2. PHPRunner General questions
  3. [SOLVED]  $strSQLInsert1 it only imports 1 record not all
This topic is locked
[SOLVED]

 $strSQLInsert1 it only imports 1 record not all

 		9/5/2019 10:35:24 AM
PHPRunner General questions
C
 chrispa author

hello - i need your help i have following script in before record added which it select some records and insert to other table / actually i would like to get all records from tblordersupplydetails where enqury link = description and insert all values to table proformachilditems.
My script below it only insert 1 record from tblordersupplydetails to proforormachilditems altough they can be 3-4 or more records - can someone help me ? thanks
quote
$strSQLSELECT = "SELECT * FROM tblordersupplydetails WHERE EnquryLink='".$values['description']."'";

$rs = db_query($strSQLSELECT,$conn);

$data = db_fetch_array($rs);
$strSQLInsert1 = "insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr)

values ('".$data['Description']."','".$data['ShortCode']."','".$data['UnitLink']."','".$data['Quantity']."'

,'".$data['Curr']."') " ;
CustomQuery($strSQLInsert1);

// Place event code here.

// Use "Add Action" button to add code snippets.
return true;
unquote
N
 		Nir Frumer 9/5/2019

HI, i BELIEVE THAT THIS IS WHAT YOU ARE LOOKING FOR

YOU MAY ALSO WANT TO CREATE 1 INSERT * UPDATE MyTABLE (MyFILDES,,,) VALUES (MyVALUES,,,),(MyVALUES,,,) ....
$strSQLSELECT = "SELECT FROM tblordersupplydetails WHERE EnquryLink='".$values['description']."'";

$rs = db_query($strSQLSELECT,$conn);

WHILE ($data = db_fetch_array($rs)) {
$strSQLInsert1 = "insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr)

values ('".$data['Description']."','".$data['ShortCode']."','".$data['UnitLink']."','".$data['Quantity']."'

,'".$data['Curr']."') " ;
CustomQuery($strSQLInsert1);

}
good luck
C
 		chrispa author 9/5/2019

great thanks a lot !!!
now works much better - however when in description there is a single quote ' like CLEANSER VIM 500GRM X 12'S then it returns following error and stops importing remaining items :
error
Technical information

Error type 256

Error description You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'S','171196','267','' ,'24')' at line 2

URL 192.168.1.8/purchasing/reports/proformachild_add.php?page=add&submit=1&fly=1&

Error file C:\xampp\htdocs\purchasing\reports\connections\Connection.php

Error line 611

SQL query insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr) values ('COFFEE FILTER PAPER CRINKLED, 250MM DIAM 1800'S','171196','267','' ,'24')
More info
Call stack

File: line Function Arguments

0. connections\Connection.php:611 trigger_error 1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'S','171196','267','' ,'24')' at line 2;

  1. 256;

    1. connections\Connection.php:611 Connection->triggerError 1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'S','171196','267','' ,'24')' at line 2;

    2. connections\MySQLiConnection.php:142 MySQLiConnection->query 1. insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr) values ('COFFEE FILTER PAPER CRINKLED, 250MM DIAM 1800'S','171196','267','' ,'24') ;

    3. include\dal.php:24 CustomQuery 1. insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr) values ('COFFEE FILTER PAPER CRINKLED, 250MM DIAM 1800'S','171196','267','' ,'24') ;

    4. include\proformachild_events.php:47 eventclass_proformachild->BeforeAdd 1. Array ( [description] => 27774 [connect] => 1 ) ;


  2. ;
  3. ;
  4. AddPage;

    5. classes\addpage.php:572 AddPage->callBeforeAddEvent N/A

    6. classes\addpage.php:404 AddPage->processDataInput N/A

    7. classes\addpage.php:319 AddPage->process N/A

    8. proformachild_add.php:100 Global scope N/A




    HI, i BELIEVE THAT THIS IS WHAT YOU ARE LOOKING FOR

    YOU MAY ALSO WANT TO CREATE 1 INSERT * UPDATE MyTABLE (MyFILDES,,,) VALUES (MyVALUES,,,),(MyVALUES,,,) ....
    $strSQLSELECT = "SELECT     FROM tblordersupplydetails WHERE EnquryLink='".$values['description']."'";

    $rs = db_query($strSQLSELECT,$conn);

    WHILE ($data = db_fetch_array($rs)) {
    $strSQLInsert1 = "insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr)

    values ('".$data['Description']."','".$data['ShortCode']."','".$data['UnitLink']."','".$data['Quantity']."'

    ,'".$data['Curr']."') " ;
    CustomQuery($strSQLInsert1);

    }
    good luck
C
 		chrispa author 9/5/2019

ok found it - the single quote problem following is working fine now thank you all for your help
quote

$strSQLSELECT = "SELECT * FROM tblordersupplydetails WHERE EnquryLink='".$values['description']."'";

$rs = db_query($strSQLSELECT,$conn);

WHILE ($data = db_fetch_array($rs)) {
$strSQLInsert1 = "insert into proformachilditems (Description, ShortCode, UnitLink, Quantity, Curr)

values ('".str_replace("'","\'",$data['Description'])."','".$data['ShortCode']."','".$data['UnitLink']."','".$data['Quantity']."'

,'".$data['Curr']."') " ;
CustomQuery($strSQLInsert1);

}

return true;
unquote