I get a SQL syntax error from the PHP script but when I put this query output from the page into the query browser I don't get an error. I echoed the values in the header and the INSERT INTO. I don't see any errors?
thanks,
----output--------------
hdrArray:
accountNo , zonenm , zoneid , sku , supplierName , nationalDrugCode , brandDescription , genericDescription , class , qty , unit , cost , extCost
sData:
080593355' , 'otc' , '0003' , '02454 ' , '' , '66993087985' , 'CHAPSTICK B/CD REG BALM 24X0.15OZ ' , 'LIP BALM ' , '0' , '9' , '1/EA ' , '0.99' , '8.91
Invalid query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
, file_name, location_code) VALUES ('080593355' , 'otc' , '0003' , '02' at line 1
INSERT INTO pharm_cart_order.cart_inventory_tmp (accountNo , zonenm , zoneid , sku , supplierName , nationalDrugCode , brandDescription , genericDescription , class , qty , unit , cost , extCost
, file_name, location_code) VALUES ('080593355' , 'otc' , '0003' , '02454 ' , '' , '66993087985' , 'CHAPSTICK B/CD REG BALM 24X0.15OZ ' , 'LIP BALM ' , '0' , '9' , '1/EA ' , '0.99' , '8.91
', 'JOB0059.TXT', '059')
$hdrArray=array("accountNo","zonenm","zoneid", "sku","supplierName","nationalDrugCode","brandDescription","genericDescription","class","qty","unit","cost","extCost","nof");
if ($handle = opendir ($dir)) {
while (FALSE != ($file = readdir ($handle))) {
if (!is_dir($dir . $file)) {
$location_code= substr($file, strpos($file, ".txt") -7, 3);
echo $location_code."<br />";
if (($fh = fopen($dir . $file, "r+")) != FALSE) {
$firstline = TRUE;
$headersok = TRUE;
while ($headersok && (($res = fgetcsv($fh,9999,"|")) != FALSE)) {
if ($firstline) {
$hdrArray2 = implode( ",", $res)."<br />";
if ($headersok) { // all headers listed in the file are in the hdrArray
foreach ($res as $x) {
if (!in_array($x, $hdrArray)) {
$headersok = false;
echo "$x is not in $hdrArray";
}
}//end for each
}//end if
$firstline = FALSE;
}//end if firstline
else {
$res = array_map('mysql_real_escape_string', $res);
$sData= implode( "','", $res)." <br />";
$sql="INSERT INTO `pharm_cart_order`.cart_inventory_tmp ($hdrArray2, filename,location_code) ";
$sql.="VALUES ('$sData', '$file', '$location_code') ";
$result=mysql_query($sql);
}//end else
}//end while
fclose($fh);
}//end if fopen
}//end check for
}//end while
closedir($handle);
}//close fopen
It loops through a file directory and opens & reads the csv files to do the INSERTS for each row..
It creates the header string. It compares the header string with the headerList to make sure the header columns are in that list.
It parses numbers from the filename and creates the location_code.
It adds the location_code and file_name to the values to be inserted.
thanks,
It adds the
thanks,
