PHP PDO simple insert or update function

Question

When I tried to create a simple PHP PDO update function, which, if the field is not found, inserts it, I created this small fragment.

function updateorcreate($table,$name,$value){ global $sodb; $pro = $sodb->prepare("UPDATE `$table` SET value = :value WHERE field = :name"); if(!$pro){ $pro = $sodb->prepare("INSERT INTO `$table` (field,value) VALUES (:name,:value)"); } $pro->execute(array(':name'=>$name,':value'=>$value)); } 

It does not detect if the update function will work with if(!$pro); . How do we do this job.

Answer 1

You assign $pro to the prepare command, not execute.

Having said that, if you are using mysql, you can use the insert... on duplicate key update syntax.

 insert into $table (field, value) values (:name, :value) on duplicate key update value=:value2 

You cannot use the same related parameter twice, but you can set two related parameters to the same value.

Edit: this mysql syntax will only work where the key is present (primary or other unique) and will cause the insert to fail.

Answer 2

If it is mysql-only, you can try INSERT INTO ... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 3

First you need to execute it.

It is also a tricky way to do this. It would be better to start the transaction, do SELECT, and then determine what to do (INSERT or UPDATE). Just checking if the UPDATE query failed, fails, it succeeds when no rows are found.

Answer 4

to try

  PDO::exec() 

returns 1 if inserted. 2 if the row is updated.

for trained operators,

  PDOStatement::execute() 

You may try,

  PDOStement::rowCount()