CODEWITHSUNDEEP
phppdoinsert-update
Walrus
Walrus

Reputation: 20464

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.

Upvotes: 10

Views: 19289

Answers (5)

Mehmet Emin Sayım
Mehmet Emin Sayım

Reputation: 57

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;
}

}

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33522

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.

Upvotes: 22

FirmView
FirmView

Reputation: 3150

try,

    PDO::exec()

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

for prepared statements,

    PDOStatement::execute()

You can try,

    PDOStement::rowCount()

Upvotes: 0

Louis Huppenbauer
Louis Huppenbauer

Reputation: 3714

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

Upvotes: 2

Sherlock
Sherlock

Reputation: 7597

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.

Upvotes: 0

Related Questions