PDO request working 1/4 time

Question

To explain the context I have a CSV file through which I'm looping.

I already checked with the following code , I'm actually ready each and every line of my CSV file.

if (file_exists($file)){

    $fic = fopen($file, 'rb');
    $linecount = 0;

    for ($ligne = fgetcsv($fic, 1024,";"); !feof($fic); $ligne = fgetcsv($fic, 1024,";")) {
        $linecount++;
     }
}
else { 
/*do smth*/
}
echo $linecount;

But when I replace $linecount++ with what I realy want my code to do :

$codeArticle = $ligne[0]; //Code Article
$nomArticle = $ligne[1]; //Nom Article
$refGamme = $ligne[2]; //Référence Gamme 
$nomGamme = $ligne[3]; //Nom Gamme
$codeSSRef = $ligne[4]; //Code Sous Référence
$libelleSSRef = $ligne[5]; //Libellé Sous Référence

$queryGammes = "INSERT INTO Gamme VALUES('$refGamme','$nomGamme');";
$querySSRef = "INSERT INTO SS_Ref VALUES('$codeSSRef','$libelleSSRef');";
$queryProduits = "INSERT INTO Produit VALUES('$codeArticle','$nomArticle',NULL,'$refGamme','$codeSSRef');";
$result = $pdo->query($queryGammes);
$result = $pdo->query($querySSRef);
$result = $pdo->query($queryProduits);

I got no error with this code running but when I look into my db (mysql) I only have 339 total rows in the table named Produit where i should have 1 line per CSV line. Theese rows are actualy great , the requests runned great but I can't figure out why this code doesn't insert every line of my file into my DB.

Any advice please ? I'm realy stuck here and it's important so I'll take any piece of advice.

Building SQL query strings from input is never a good idea. It can lead to everything from invalid SQL (if somebody has the name `O'Leary` it will break the generated SQL) to SQL injection attacks. I'm betting a lot of your problems will go away if you replace your SQL string building code with proper prepared statements — GordonM, Mar 27 '17 at 10:45
"I go no error with this code" - Then please have a look at [How to squeeze error message out of PDO](http://stackoverflow.com/questions/3726505/how-to-squeeze-error-message-out-of-pdo#3726526). — Álvaro González, Mar 27 '17 at 10:45
@ÁlvaroGonzález after getting enough knowledge with PDO I managed to write a much better answer, http://stackoverflow.com/a/32648423/285587 which I prefer to be linked instead, if you don't mind — Your Common Sense, Mar 27 '17 at 11:29
@YourCommonSense thx to your answer I found out why my code wasn't running well , but do you know how I could deal with special chars ? It seems like the � replacing chars like é or è is making the request crash — Rolexel, Mar 27 '17 at 12:20
@AlexandreAudin use prepared queries for values and only english letters for columns and tables names — maxpovver, Mar 27 '17 at 12:28
@maxpovver I have the problem using prepared queries , and i have only english letters for columns and table names.... It's with the data i want to insert that I have the problem. I'm having : Incorrect string value: '\xE9ricar...' for column 'nomArticle' but I have not xE9ricar , it's éricar — Rolexel, Mar 27 '17 at 12:35
@AlexandreAudin set charset in DSN like it shown [here](https://phpdelusions.net/pdo#dsn): charset=utf8 — Your Common Sense, Mar 27 '17 at 12:38
I finally found a solution , I'll post it as a general answer and validate it ! Thanks a lot everyone — Rolexel, Mar 27 '17 at 14:04

maxpovver · Answer 1 · 2017-03-27T10:58:32.193

I am almost sure that you have issues because of inserting values directly into sql. Here is what you should do instead:

if (file_exists($file)){

    $fic = fopen($file, 'rb');
    $linecount = 0;
    // generate prepared statements only once and reuse them in loop
    $queryGammes = $pdo->prepare("INSERT INTO Gamme VALUES(:refGamme,:nomGamme);");
    $querySSRef = $pdo->prepare("INSERT INTO SS_Ref VALUES(:codeSSRef,:libelleSSRef);");
    $queryProduits = $pdo->prepare"INSERT INTO Produit     
              VALUES(:codeArticle,:nomArticle,NULL,:refGamme,:codeSSRef);");
    for ($ligne = fgetcsv($fic, 1024,";"); !feof($fic); $ligne = fgetcsv($fic, 1024,";")) {
        $codeArticle = $ligne[0]; //Code Article
      $nomArticle = $ligne[1]; //Nom Article
      $refGamme = $ligne[2]; //Référence Gamme 
      $nomGamme = $ligne[3]; //Nom Gamme
      $codeSSRef = $ligne[4]; //Code Sous Référence
      $libelleSSRef = $ligne[5]; //Libellé Sous Réfé
      // execute queries using generated statements, not $pdo directly
      $result = $queryGammes->execute(array("refGamme"=> $refGamme, "nomGamme"=> $nomGamme));
      $result = $querySSRef->execute(array("codeSSRef"=>$codeSSRef, "libelleSSRef"=>$libelleSSRef));
      $result = $queryProduits->execute(array("codeArticle"=>$codeArticle, "nomArticle"=>$nomArticle, "refGamme"=> $refGamme,"codeSSRef"=>$codeSSRef));
     }
}

You can also make it without using named parameters, but I don't recommend to do that because such code is less readable(and it will be harder to remember what it does later):

if (file_exists($file)){

    $fic = fopen($file, 'rb');
    $linecount = 0;
    // generate prepared statements only once and reuse them in loop
    $queryGammes = $pdo->prepare("INSERT INTO Gamme VALUES(?,?);");
    $querySSRef = $pdo->prepare("INSERT INTO SS_Ref VALUES(?,?);");
    $queryProduits = $pdo->prepare"INSERT INTO Produit     
              VALUES(?,?,NULL,?,?);");
    for ($ligne = fgetcsv($fic, 1024,";"); !feof($fic); $ligne = fgetcsv($fic, 1024,";")) {
        $codeArticle = $ligne[0]; //Code Article
      $nomArticle = $ligne[1]; //Nom Article
      $refGamme = $ligne[2]; //Référence Gamme 
      $nomGamme = $ligne[3]; //Nom Gamme
      $codeSSRef = $ligne[4]; //Code Sous Référence
      $libelleSSRef = $ligne[5]; //Libellé Sous Réfé
      // execute queries using generated statements, not $pdo directly
      $result = $queryGammes->execute(array($refGamme, $nomGamme));
      $result = $querySSRef->execute(array($codeSSRef, $libelleSSRef));
      $result = $queryProduits->execute(array($codeArticle,$nomArticle,$refGamme,$codeSSRef));
     }
}

Thanks a lot for this answer ! It improved a lot my code but didn't change the result , but thx to other answers I might have found a way ! — Rolexel, Mar 27 '17 at 12:10
This still has some issues, namely you're not doing any kind of error handling. The prepare() and execute() methods can fail and either return false or throw an exception (depending on how PDO is configured). Either way, there needs to be some error correction code added (either checking for successful return values from prepare() and execute(), or using try/catch for handling exceptions) — GordonM, Mar 27 '17 at 18:44

score 0 · Answer 2 · answered Mar 27 '17 at 10:43

Checklist:

You are not sanitizing your input so that's the first place I would look. Most likely and robust fix is to switch to the parameterized query syntax of PDO.
Now, assuming that doesn't fix the problem, the next step is to look carefully at the lines which insert vs the lines which don't. I would take the first few rows that insert and compare them against ones that do not.
I would look at unique constraints. Violations here may throw messages in the MySQL log. So check that first.

score 0 · Accepted Answer · answered Mar 27 '17 at 14:10

Thx to every answers I got I found a solution , first I used parameterized queries as suggested by @maxpovver (I pasted the got he gave) and @Chris Travers.

Thx to @Álvaro González and @Your Common Sense I managed to print PDO Errors which told me there were some problems with specials chars. Setting PDO charset to utf8 as suggested by @Your Common Sense wasn't enough to change the problem on the entrence , but it solved on the exit : special chars print well when I retrive them from my DB.

To solve the problem I had to use the utf8_encode php function :

$result = $queryGammes->execute(array(
    "refGamme"=> $refGamme, 
    "nomGamme"=> utf8_encode($nomGamme))
);

No, please. A solution to encoding problems that involves `utf8_encode()` is almost always wrong. Do you have any evidence that you are using ISO-8859-1? — Álvaro González, Mar 27 '17 at 14:12
My DB uses utf8mb4_unicode_ci but for the website I don't know , i'll try to figure it out. — Rolexel, Mar 27 '17 at 14:19

PDO request working 1/4 time

3 Answers3