PHP PDO simple insert or update function

Question

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 [for using `global`](http://stackoverflow.com/a/11923384/508666) — PeeHaa, Sep 05 '12 at 14:29
You first need to `execute()` it before you can tell whether the update caused rows to get updated using `numRows()`, but if the field already exists with the same value you might get unintended results as well. — Ja͢ck, Sep 05 '12 at 14:30
The update query doesn't give an error, it's a valid query that doesn't update any records. — user254875486, Sep 05 '12 at 14:30
@Lex but he only executes his first query at the bottom, so the if-condition is useless. — Ja͢ck, Sep 05 '12 at 14:32

Fluffeh · Accepted Answer · 2012-09-05T14:42:30.833

21

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.

edited Sep 05 '12 at 14:42

answered Sep 05 '12 at 14:31

Fluffeh

33,228
16
67
80

This is what I tried: function updateorcreate($table,$name,$value){ global $sodb; $pro = $sodb->prepare("INSERT INTO $table (field, value) values (:name, :value) ON DUPLICATE KEY update value = :value2"); $pro->execute(array(':name'=>$name,':value'=>$value,':value2'=>$value)); } – Walrus Sep 05 '12 at 14:42
@RobinKnight Sorry, I should have mentioned it right off the bat, you do have a unique constraint set on the table for the column `field` yes? – Fluffeh Sep 05 '12 at 14:45
No perhaps I ought to as there should be no reason they would ever repeat – Walrus Sep 05 '12 at 14:46
@RobinKnight Sorry about that mate, yes, the syntax will only work if the column is constrained. Apologies, should have mentioned that right from the start :( – Fluffeh Sep 05 '12 at 14:48
PDO really needs to allow you to use the same parameter twice. – Jonathon Jun 19 '13 at 19:25

score 2 · Answer 2 · answered Sep 05 '12 at 14:34

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

answered Sep 05 '12 at 14:34

Louis Huppenbauer

3,719
1
18
24

score 0 · Answer 3 · answered Sep 05 '12 at 14:31

0

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.

answered Sep 05 '12 at 14:31

Sherlock

7,525
6
38
79

OK, I was trying to see if we could bypass that step but if its not safe then I won't – Walrus Sep 05 '12 at 14:31
`SELECT` is a rather dodgy way of doing it too. Better would be to have a unique key on the field, and then use `INSERT ... ON DUPLICATE KEY UPDATE ...`. – cHao Sep 05 '12 at 14:32
I never read he wants to avoid duplication altogether. He just doesn't want it in this specific situation. – Sherlock Sep 05 '12 at 14:33

score 0 · Answer 4 · answered Sep 05 '12 at 14:36

0

try,

    PDO::exec()

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

for prepared statements,

    PDOStatement::execute()

You can try,

    PDOStement::rowCount()

answered Sep 05 '12 at 14:36

FirmView

3,130
8
34
50

score 0 · Answer 5 · edited Aug 13 '21 at 10:40

The following are PHP PDO helper functions for INSERT and UPDATE

INSERT function:

function basicInsertQuery($tableName,$values = array()){

/*
//
USAGE INSERT FUNCTİON
    $values = [
        "column" => $value,               
    ];
    $result =  basicInsertQuery("bulk_operations",$values);
*/

try {
    global $pdo;

    foreach ($values as $field => $v)
        $vals[] = ':' . $field;

    $ins = implode(',', $vals);
    $fields = implode(',', array_keys($values));
    $sql = "INSERT INTO $tableName ($fields) VALUES ($vals)";

    $rows = $pdo->prepare($sql);
    foreach ($values as $k => $vl)
    {
        $rows->bindValue(':' . $k, $l);
    }
    $result = $rows->execute();
    return $result;
} catch (\Throwable $th) {
   return $th;
}    
}

UPDATE function:

function basicUpdateQuery($tableName,  $values = array(), $where = array()) {

/*
*USAGE UPDATE FUNCTİON

    $valueArr = [ column => "value",  ];
    $whereArr = [ column => "value",  ];
    $result = basicUpdateQuery("bulk_operations",$valueArr, $whereArr);
*/
try {        
    global $pdo;

    //set value
    foreach ($values as $field => $v)
        $ins[] = $field. '= :' . $field;
    $ins = implode(',', $ins);

    //where value
    foreach ($where as $fieldw => $vw)
        $inswhere[] = $fieldw. '= :' . $fieldw;
    $inswhere = implode(' && ', $inswhere);


    $sql = "UPDATE  $tableName SET $ins WHERE $inswhere";    
    $rows = $pdo->prepare($sql);
    foreach ($values as $f => $v){
        $rows->bindValue(':' . $f, $v);
    }
    foreach ($where as $k => $l){
        $rows->bindValue(':' . $k, $l);
    }
    $result = $rows->execute();

    return $result;
} catch (\Throwable $th) {
    return $th;
}

}

PHP PDO simple insert or update function

5 Answers5

Linked

Related