PHP PDO simple insert or update function

问题:

In trying to create a simple PHP PDO update function that if the field is not found would insert it, I created this little snippet.

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 though if the update function is going to work with if(!$pro); How would we make this one work.

回答1:

You are assigning $pro to the prepare, not the execute statement.

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 can't use the same bound param twice, but you can set two bound params to the same value.

Edit: This mysql syntax will only work where a key (primary or another unique) is present and would cause an insert to fail.

回答2:

If it's mysql-only you could try INSERT INTO ... ON DUPLICATE KEY UPDATE

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

回答3:

You will first need to execute it.

Apart from that, this is a dodgy way of doing this. It would be better to start a transaction, do a SELECT and then determine what to do (INSERT or UPDATE). Just checking whether the UPDATE query succeeded doesn't suffice, it succeeds when no row is found too.

回答4:

try,

    PDO::exec() 

returns 1 if inserted. 2 if the row has been updated.

for prepared statements,

    PDOStatement::execute() 

You can try,

    PDOStement::rowCount()