MYSQL, PHP Insert into Multiple Tables in Database

Question

I'm trying to insert information into multiple tables within a database, i have managed to get it to work using this:

  $query = "INSERT INTO users (grp, email, college_id, tutor, year, password, register_date) VALUES ('$g', '$e', '$ci', '$tu', '$y', PASSWORD('$p'), NOW() )";
  $query2 = "INSERT INTO unit_26 (college_id) VALUES ('$ci')";
  $result = mysql_query ($query); // Run the Query Now woooo. 
  $result2 = mysql_query ($query2); // Run the Query Now woooo. 
  if ($result) { // If it Ran OK.

Although it works and the information is added to both tables, I was just wondering if anyone has a better way of doing this, or of this way is wrong?

@Col. - I thought that that was the canonical link we used for questions featuring SQL injection. — aaz
– aaz, Commented Feb 16, 2011 at 20:34
@Col. Shrapnel: Aren't you the one always railing about solutions that are vulnerable to SQL injection? — Robert Harvey
– Robert Harvey, Commented Feb 16, 2011 at 22:27
@aaz Canonical? How come it become canonical? This site contains empty rant only and no real help. It's only purpose is to just raise some money from popular character. There are no good explanations nor real life examples nor common pitfalls. That smarta55 just copy-pasted some texts just like he copypasted the character. It's disgusting and shameful site, not canonical. — Your Common Sense
– Your Common Sense, Commented Feb 18, 2011 at 6:06

aaz · Accepted Answer · 2011-02-16 21:38:42Z

Since these two inserts are executed independently, another program running concurrently might see the database in a state where the first insert is done but the second isn't.

Whether this is a problem or not depends on the application logic. In your case it's hard to tell without additional information. Probably not. A financial transactions involving two accounts is an example where this is a problem: you don't want the sum of all account balances to be wrong at any time.

If you think you need this, you can make the operation atomic at the cost of performance: another program will either see the database before the first insert, or after the second insert. It works like this:

$result = FALSE;
if (mysql_query('BEGIN')) {
    if (mysql_query($query1) &&
        mysql_query($query2))
        $result = mysql_query('COMMIT'); // both queries looked OK, save
    else
        mysql_query('ROLLBACK'); // problems with queries, no changes
}

The storage engine has to support transactions, i.e., it has to be InnoDB. Otherwise this will silently not work.

Marc B · Accepted Answer · 2011-02-16 20:20:08Z

0

SQL inserts are for a single table only. You can't insert data into two or more tables at the same time with a single INSERT query. You can force the separate insertions to be atomic by using a transaction, but you still have to do an INSERT for each table.

Might be nice if you could so something like

INSERT INTO t1.id, t2.val, t3.other VALUES ($t1id, $t2val, $t3other);

but alas...

answered Feb 16, 2011 at 20:20

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

MYSQL, PHP Insert into Multiple Tables in Database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related