update 2 tables in one time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

update 2 tables in one time

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...)
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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&#1111;] = "START TRANSACTION;"
$queries&#1111;] = "INSERT INTO table1 VALUES (....)";
$queries&#1111;] = "SELECT @id:=LAST_INSERT_ID() FROM table1";
$queries&#1111;] = "INSERT INTO table2 VALUES(@id, ....)";
$queries&#1111;] = "COMMIT";

foreach($queries as $query)
&#123;
  mysql_query($query);
&#125;
offcourse you could also use mysql_insert_id after the first insert.. and use that value to build the second query....
Post Reply