php mysql同时写入数据库表,MYSQL，PHP插入到数据库中的多个表中

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?

Thanks

解决方案

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.