Query and '

[Philippe-M]

I've this code

          $value_query[] = "('".$data[0]."', '".$data[1]."', ".$data[2].", ".$data[3].")";
          $database->query('
            INSERT INTO stock
            (itmref, status, qtystu, cumallqty)
            VALUES :values
            ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)', [
              ':values' => implode(',', $value_query)
            ]
          );

The sql query it's

INSERT INTO stock(itmref, status, qtystu, cumallqty) VALUES '('1-79218', 'A', 3, 0)' ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)

And i've this error

1064 - 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 ''('1-79218', 'A', 3, 0)' ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)' at line 1

The correct sql query it's

INSERT INTO stock(itmref, status, qtystu, cumallqty) VALUES ('1-79218', 'A', 3, 0) ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)

The different it's " ' " before and after ('1-79218'...

[Andrews54757]

@Philippe-M Thats not the right usage of PDO prepared statements. Of course it would be quoted, it escaped the string. It should be this:

          $database->query('
            INSERT INTO stock
            (itmref, status, qtystu, cumallqty)
            VALUES (:first, :second, :third, :fourth)
            ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)', [
              ':first' => $data[0],
              ':second' => $data[1],
              ':third' => $data[2],
              ':fourth' => $data[3]
            ]
          );

[Philippe-M]

I'm use this query for import ~6000 register and i'm create a array before to import and load the array value_query. If i'm use your code i'm import one bye one.

      echo '['.date('d-m-Y H:i:s').'][info] --- Import du fichier stock.csv'."\n";
      $filename = __DIR__."/csv/stock.csv";
      if(file_exists($filename)) {
        echo '['.date('d-m-Y H:i:s').'][info] Le fichier a été trouvé'."\n";
        if(($handle = fopen($filename, 'r')) !== FALSE) {
          echo '['.date('d-m-Y H:i:s').'][info] Vidage de la table'."\n";
          $database->query('TRUNCATE stock');
          echo '['.date('d-m-Y H:i:s').'][info] Insertion des données'."\n";
          while(($data = fgetcsv($handle, 100000, '|')) !== FALSE) {
            $champs = count($data);
            for($i = 0; $i < $champs; $i++) {
              $data[$i] = preg_replace('/^\"*/', '', $data[$i]);
            }
            $value_query[] = "('".$data[0]."', '".$data[1]."', ".$data[2].", ".$data[3].")";
          }

          $database->query("
            INSERT INTO stock
            (itmref, status, qtystu, cumallqty)
            VALUES :values
            ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)", [
              ":values" => implode(',', $value_query)
            ]
          );

          fclose($handle);
          echo '['.date('d-m-Y H:i:s').'][info] Importation terminée'."\n";
        } else {
          echo '['.date('d-m-Y H:i:s').'][error] erreur pendant l\'import : '.var_dump($handle).''."\n";
        }
      } else {
        echo '['.date('d-m-Y H:i:s').'][error] Le fichier '.$filename.' n\'a pas été trouvé'."\n";
      }

[Andrews54757]

@Philippe-M Oh I see, so you are trying to insert multiple rows at once. This is tricky. There is one solution though, you could edit the sql as a string,. However, this is not secure.

[Andrews54757]

@Philippe-M I have a quick and dirty solution:

In https://github.com/catfan/Medoo/blob/master/src/Medoo.php#L1080, Change to

public function insert($table, $datas, $append = null)

Then change https://github.com/catfan/Medoo/blob/master/src/Medoo.php#L1173 to

return $this->exec('INSERT INTO ' . $this->tableQuote($table) . ' (' . implode(', ', $fields) . ') VALUES ' . implode(', ', $stack) . ($append ? (' ' . $append) : ''), $map);

Now, you can do:

$Medoo->insert('stock',[
    [
        'itmref' => 'value', 
        'status' => 'anothervalue',
        'qtystu' => 'val3', 
        'cumallqty' => 'val4'
    ],
    [ // multi-insertion
        'itmref' => 'value', 
        'status' => 'anothervalue',
        'qtystu' => 'val3', 
        'cumallqty' => 'val4'
    ] 
    /* etc...*/
],'ON DUPLICATE KEY UPDATE itmref=VALUES(itmref)'); // append

[catfan]

This is the wrong way to use PDO prepared statement. You may check out how prepared statement works for SQL and build the query you wanted.

In fact, there are also a lot of other ways to handle this case. Like using insert() for multiple insert if no duplicate update needed, or check out the duplicated key before the insertion. Additionally, you may also consider to use REPLACE for this case.