CODEWITHSUNDEEP
phpmysqlsqlinsert-update
RyanJ
RyanJ

Reputation: 131

Multiple INSERT/UPDATE on MySQL

I have to perform a multiple insert/update using the ON DUPLICATE KEY instruction with one dynamic field and the others static. I've got the table "notifications" with fields: 

ID (BIGINT AUTOINCREMENT PRIMARY KEY), 
user (BIGINT UNIQUE), 
creator (BIGINT UNIQUE), 
type (TINYINT UNIQUE), 
value (BIGINT UNIQUE), 
date (INT), 
readed (TINYINT), 
erased (TINYINT).

I have to insert/update a row for each user (ID will be extracted by a query), so the value of the field "user" will be dynamic, the others are static. How I can perform this action with only one query on MySQL?

Upvotes: 1

Views: 255

Answers (1)

DannyTheDev
DannyTheDev

Reputation: 4173

If you can make use of PDO, you can build your query up and submit it once you've added all records.

Here's an example of using beginTransaction with commit.

This prepares your query, and then loops through a userlist and allocates each record.

After looping it commits/actions the query.

$dbc->beginTransaction();
$sql =  'INSERT INTO notifications (id,user,creator,type,value,date,readed,erased) '
    .   'VALUES (:id,:user,:creator,:type,:value,:date,:readed,:erased) '
    .   'ON DUPLICATE KEY UPDATE user=:user ';

$sth= $dbc->prepare( $sql );


foreach( $userlist as $user )
{
    $sth->execute( array(
        ':id'=>$user['id'],
        ':user'=>$user['user'],
        ':creator'=>$user['creator'],
        ':type'=>$user['type'],
        ':value'=>$user['value'],
        ':date'=>$user['date'],
        ':readed'=>$user['readed'],
        ':erased'=>$user['erased']
     ));
}

$dbc->commit();

Some reference points:

Upvotes: 1

Related Questions