UPDATE
The second after I posted this question, thanks to the syntax highlighting of the resulting queries I saw what went wrong: the $pl string didn't open with a closing back tick. Now I changed it to:
$pk = ',`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';//WRONG
$pk = '`,`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';//OK
However, this poses a new question: why didn't the PDO object spew errors back at me for this? executing the query manually surely would return an error saying there is no field called fld2_AGE,, with a comma at the end. Why didn't I get any errors? any Ideas?
PS: any idea's how to upvote SO's syntax highlighting for solving my problem? :-)
I'm leaving the original question as a reference/example, though it doesn't need solving anymore
Ok, I've been using a script I wrote a while back to import generate several csv files from a huge file, to quickly import the data into several mysql tables. This script has worked failry well in the past (I think) up until I wrote a second script, in which I accidentally truncated my tables (stupid, I know). 'No biggie' I thought, as I had the the script, it would be a matter of seconds to restore my data. Unfortunately, I found that now, only one file is being imported, and no errors are showing. Below I have pasted the entire db section of the script. When I execute this code, all I get is the output files imported successfully, which is the very last line of code...
I know it's a failry large block of code, with a lot of string format printing, which doesn't improve readability, so I've also provided the resulting query strings below. As far as I can tell, they look well formatted, as do the files (I checked). Can anyone tell where else I'm supposed to look for errors? It would be a great help... Thanks!
<?php
$files = array_fill_keys(array('filename1','filename2','filename3','filename4'),'');
//$files === array of handles fputcsv($files['filename1'],array('values','from','other','files'),';');
$tbls = array_combine($files,array('tblname1','tblname2','tblname3','tblname4'));
$path = dirname(__FILE__)'/';
$qf = 'LOAD DATA LOCAL INFILE \'%s%s.csv\' INTO TABLE my_db.tbl_prefix_%s FIELDS TERMINATED BY \';\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\'';
$pref = array_combine($files,array('fld1_','fld2_','fld3_','fld3_'));
$pkA = ' (`%1$sNAME`,`%1$sAGE';
$pk = '`,`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';
try
{
$db = new PDO('mysql:host=mysqlhostn','user','pass');
foreach($files as $f)
{
$db->beginTransaction();
$db->exec(sprintf('TRUNCATE TABLE my_db.tbl_prefix_%s',$tbls[$f]));
$db->commit();
}
}
catch(PDOException $e)
{
if ($db)
{
$db->rollBack();
$db = null;
}
die('DB connection/truncate failed: '.$e->getMessage()."\n");
}
try
{
while($f = array_shift($files))
{
$db->beginTransaction();
$q = sprintf($qf,$path,$f,$tbls[$f]).sprintf($pkA.($f !== 'agent' ? $pk : ''),$pref[$f]);
switch($f)
{
case 'filename3':
$q .= 'tbl3_specific_field';
break;
case 'filename2':
$q .= sprintf('tbl2_specific_field`,`%1$tbl2_specific_field2',$pref[$f]);
break;
case 'filename4':
$q .= sprintf('tbl4_specific_field`,`%1$tbl4_specific_field2`,`%1$tbl4_specific_field3`,`%1$tbl4_specific_field4',$pref[$f]);
break;
}
$stmt = $db->prepare($q.'`)');
$stmt->execute();
$db->commit();
}
}
catch(PDOException $e)
{
$db->rollBack();
$e = 'CSV import Failed: '.$e->getMessage();
$db=null;
die($e."\n");
}
$db = null;
exit('files imported successfully'."\n");
?>
generated Queries - execution output:
TRUNCATE TABLE my_db.tbl_prefix_tblname1
TRUNCATE TABLE my_db.tbl_prefix_tblname2
TRUNCATE TABLE my_db.tbl_prefix_tblname3
TRUNCATE TABLE my_db.tbl_prefix_tblname4
LOAD DATA LOCAL INFILE '/local/path/to/files/filename1.csv' INTO TABLE my_db.tbl_prefix_tblname1 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld1_NAME`,`fld1_AGE`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename2.csv' INTO TABLE my_db.tbl_prefix_tblname2 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld2_NAME`,`fld2_AGE,`fld2_USER`,`fld2_TYPE`,`fld2_CODE`,`fld2_VALUE`,`fld2_tbl2_specific_field`,`fld2_tbl2_specific_field2`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename3.csv' INTO TABLE my_db.tbl_prefix_tblname3 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld3_NAME`,`fld3_AGE,`fld3_USER`,`fld3_TYPE`,`fld3_CODE`,`fld3_VALUE`,`fld3_tbl3_specific_field`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename4.csv' INTO TABLE my_db.tbl_prefix_tblname4 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld4_NAME`,`fld4_AGE,`fld4_USER`,`fld4_TYPE`,`fld4_CODE`,`fld4_VALUE`,`fld4_tbl4_specific_field`,`fld4_tbl4_specific_field2`,`fld4_tbl4_specific_field3`,`fld4_tbl4_specific_field4`)
csv imported successfully
File1 is getting imported as I need it to be, an example of the first file:
11;9
While the actual content of file2 (which isn't getting imported) looks like this:
11;9;25;5;FOOBAR;Z;333;321;123
Both first fields contain the same data, as they should, both tables have the same field definitions, same storage engine (InnoDB), collation (UTF-8)... I have no idea what's causing the problem, so any advice would be greatly appreciated.
