Page 1 of 1
update 2 tables in one time
Posted: Thu Mar 03, 2005 5:07 am
by pleigh
can i do this?
Code: Select all
$newpost = @mysql_query("UPDATE newpost SET postID=posts.postID, postdate=NOW()") or die(mysql_error());
my idea here is to copy the content of the postID from the posts table to the postID of the newpost table
Posted: Thu Mar 03, 2005 5:12 am
by timvw
afaik sql syntax is like
update <tablename> set [<columnname>=<newcolumnvalue>]+ [where conditions]
thus you can't change 2 tables at once.
but changing 2 columns in the table at once is possible.
if you want to perform 2 queries, and they should look like one, you will have to lookup what transactions are.... (or at least what autocommit in mysql does...)
Posted: Thu Mar 03, 2005 5:22 am
by pleigh
two tables: posts, for storing data when creating a post, new post for tracking the date when the post was created, my idea is to build separate posts so i can display image when there is a new thread.....in my two tables, they both have postID fields, when initializing, the postID of table posts is set, automatically, i want to copy that value postID from posts table to new posts table....what can be the right query for this?
Posted: Thu Mar 03, 2005 5:33 am
by timvw
two tables: posts, for storing data when creating a post, new post for tracking the date when the post was created, my idea is to build separate posts so i can display image when there is a new thread.....in my two tables, they both have postID fields, when initializing, the postID of table posts is set, automatically, i want to copy that value postID from posts table to new posts table....what can be the right query for this?
Code: Select all
$queries = array();
$queriesї] = "START TRANSACTION;"
$queriesї] = "INSERT INTO table1 VALUES (....)";
$queriesї] = "SELECT @id:=LAST_INSERT_ID() FROM table1";
$queriesї] = "INSERT INTO table2 VALUES(@id, ....)";
$queriesї] = "COMMIT";
foreach($queries as $query)
{
mysql_query($query);
}
offcourse you could also use mysql_insert_id after the first insert.. and use that value to build the second query....