Php/Mysql - need help to insert and update multiple rows with a single query

Question

is there any way how in this situation insert and update DB with single queries?

$message = 'Hello to all group members';

$userdata = mysql_query("SELECT memberid, membernick FROM members WHERE groupid='$cid'") or die('Error');

while(list($memberid, $membernick) = mysql_fetch_row($userdata)) {    

$result1 = mysql_query("INSERT INTO messages VALUES (NULL,'$membernick', '$memberid', '$message')") or die('Error');
$result2 = mysql_query("UPDATE users SET new_messages=new_messages+1, total_messages=total_messages+1 WHERE id='$memberid'") or die('Error');

}

Jerome WAGNER · Accepted Answer · 2010-04-03 14:56:46Z

2

The update and insert can be one-ified with mysql >= 5.1. Try

$message = 'Hello to all group members';

$userdata = mysql_query("SELECT memberid, membernick FROM members WHERE groupid='$cid'") or die('Error');

$memberids = array();
$values = array();

while(list($memberid, $membernick) = mysql_fetch_row($userdata)) {    

    array_push($values, "(NULL,'$membernick', '$memberid', '$message')");
    array_push($memberids, "'$memberid'");

}

    // ==> replace colX with the names of your columns
    // check http://dev.mysql.com/doc/refman/5.1/en/insert.html for further information
    $result1 = mysql_query("INSERT INTO messages (col1,col2,col3,col4) VALUES ".implode(",", $values)) or die('Error');

    $result2 = mysql_query("UPDATE users SET new_messages=new_messages+1, total_messages=total_messages+1 WHERE id IN (".implode(",", $memberids).")") or die('Error');

I Hope this will help Jerome Wagner

edited Apr 3, 2010 at 14:56

answered Apr 3, 2010 at 14:50

Jerome WAGNER

22.6k10 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ondrej Slinták Over a year ago

He wanted to update / insert multiple rows with a single query, which isn't possible with MySQL.

Jerome WAGNER Over a year ago

No I think he wants to avoid having a number of request that grows with the number of members. Shrinking the code to 2 requests seems ok to me.

user Over a year ago

Jerome Wagner, thank you very much. That's what I needed. It works!

Ondrej Slinták · Accepted Answer · 2010-04-03 15:07:02Z

0

Nope, MySQL doesn't have a support for updating or inserting multiple rows in multiple tables with a single query.

edited Apr 3, 2010 at 15:07

answered Apr 3, 2010 at 14:47

Ondrej Slinták

32.1k21 gold badges97 silver badges127 bronze badges

3 Comments

Marek Karbarz Over a year ago

well, that certainly is not true; a simple UPDATE Users SET UserName='NewUser' would update all rows in a single query.

Ondrej Slinták Over a year ago

Yes, but original poster is asking if he can insert and update DB with a single query. I'm going to update my answer, so it's not confusing.

user Over a year ago

Ondrej, my question was not accurate. Actually, I wanted to ask if it's possible to insert multiple rows with a single query and to update multiple rows with a single query. I don't need such a query which inserts and updates data at the same time. Thanks to Jerome Wagner - he answered my question.

Collectives™ on Stack Overflow

Php/Mysql - need help to insert and update multiple rows with a single query

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related