0

I am trying to make a PDO MYSQL statement to insert only data that NOT exists in the database

A simple example with PHP

$sql = INSERT INTO mytable SET hash=:hash, tablename=:tablename, recuid=:recuid WHERE recuid NOT IN (SELECT recuid FROM mytable);
$insert = $this->_pdo->prepare($sql);
$insert->execute(array(
    ':hash' => $this->_getHash,
    ':tablename' => $this->_catNewTable,
    ':recuid' => $result->uid
));
  • this->_pdo connects to the database which works fine
  • this->_getHash generates a random hash Code
  • recuid is numeric
  • tablename is a string

var_dump result:

string(185) "INSERT INTO sys_refindex SET hash=:hash, tablename=:tablename, recuid=:recuid WHERE recuid NOT IN (SELECT recuid FROM sys_refindex)" 

Syntax error or access violation: 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 'WHERE recuid NOT IN (SELECT recuid FROM sys_refindex)' at line 1SQLSTATE[42000]

If think there is a problem with the WHERE statement combined with the placeholders?

Jim
  • 923
  • 4
  • 17
  • 30
  • You are mixing the insert and update statements – Rehmat Sep 03 '15 at 10:40
  • I also tried it with INSERT IGNORE INTO mytable but should not work :( – Jim Sep 03 '15 at 10:45
  • First run a query and select data by providing the recuid. If it returns 0 results, insert the data, else not. It will look something like this: $sql = "SELECT * FROM mytablet WHERE recuit = :recuid"; $sql = $pdo->prepare($sql); $sql->execute(array(':recuid'=>$result->uid)); if($sql->rowCount() == 0) { $insert = "INSERT INTO mytablet (hash, tablename, recuid) VALUES (:hash, :tablename, :recuid)"; $insert = $pdo->prepare($insert); $insert->execute(array(':hash'=>$this->_getHash, ':tablename'=> $this->_catNewTable, ':recuid'=>$result->uid)); } – Rehmat Sep 03 '15 at 10:48
  • thanks this way works fine! – Jim Sep 03 '15 at 13:50

0 Answers0