CODEWITHSUNDEEP
phpsqlpdoprepared-statement
Vit Kos
Vit Kos

Reputation: 5755

Insert or Update with prepared statements in MySQL

Trying to make insert or update sql using the prepared statements from php's pdo. First I thought of using REPLACE INTO command,but it gives me an error because I have a foreign key on my index. Read that I must use INSERT...ON DUPLICATE KEY UPDATE syntax to make it working, but it's not clear for me how to do that with prepared statements. Any solution for this? Thanks.

The sql is :

$sql="REPLACE INTO fn_currencies(id,short,name,buy,sell,date) VALUES (:id,:short,:name,:buy,:sell,:update)";

UPD: I am making this query in Yii that uses personal wrapper over the PDO. When I use unnamed parameters I get this type of error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was: INSERT INTO `fn_currencies` (id,short,name,buy,sell,date) VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE id=?,short=?,name=?,buy=?,sell=?,date=?

When I use the named parameters with differed names for Insert and Update as was mentioned..I get no errors and neither data is inserted in my DB. Here is the schema for the DB:

CREATE TABLE IF NOT EXISTS `fn_currencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `short` varchar(4) NOT NULL,
  `name` varchar(200) NOT NULL,
  `buy` decimal(10,4) NOT NULL,
  `sell` decimal(10,4) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
ALTER TABLE `fn_currencies`
  ADD CONSTRAINT `FK_fn_currencies` FOREIGN KEY (`id`) REFERENCES `fn_operations` (`currency_id`);

Upvotes: 0

Views: 3206

Answers (1)

Fluffeh
Fluffeh

Reputation: 33502

Thanks to DavaRandom, he pointed out an error in my code, but this should do the trick. Replace the named parameter with ? and use an array merge to make the SQL on the fly like this:

$sql="
    insert INTO fn_currencies(id,short,name,buy,sell,date) 
    VALUES (?,?,?,?,?,?)
    on duplicate key update currencies set 
        short=?, name=?, buy=?, sell=?, update=?";
$values=array("id"=>1, "short"=>36, "name"=>'Bazinga', "sell"=>3.67, "date"=>'2012-08-08');
$db->query($sql, array_merge(array_values($values),array_values($values)));

Apparently this will also work (See comments all over the page about yes/no/maybe) but the above will certainly work:

$sql="
    insert INTO fn_currencies(id,short,name,buy,sell,date) 
    VALUES (:id,:short,:name,:buy,:sell,:update)
    on duplicate key update currencies set 
        short=:short, name=:name, buy=:buy, sell=:Sell, update=:update";

Upvotes: 3

Related Questions