2 MySQL DB's Updated at once.

Second needs value of first.

[Knowles]

OK I have 2 databases. When I insert into them the best I can figure is I will have to run 2 different inserts one for the first database and one for the second. The issue I cant seem to put my finger on is I need the second database to enter an auto increment value from the first database. So would it end up being:

INSERT first DB

SELECT first DB

INSERT second DB

Am I right in thinking this or would there be an easier way?

[vincevincevince]

look into something like:

INSERT INTO `database2.table` (`data1`,`data2`,`autoinc`) SELECT '$data1','$data2',`database1.table.autoincrement` FROM `database1.table` WHERE 1 ORDER BY `database1.table.autoincrement` DESC LIMIT 1;

ie, insert into the fields (data1, data2, autoinc) or database2.table
use the php generated values $data1, $data2, and use a lookup value database1.table.autoincrement for your other data
limit it to one, with descending autoincrement so the last autoincrement value is used.

does that make sense?

[note the '' around values you put into the SELECT to be returned exactly as they went in, and `` put around values you want to be filled in by the lookup - this trick avoids doing two queries by making the SELECT statment forward on whatever values you want for your php generated fields]

[Knowles]

vince thanks for the reply.... I dont quite understand it though.

I ended up doing it like this:

$sql = mysql_query("INSERT INTO users (puserid)
VALUES('$username')")
or die (mysql_error());

$results = mysql_query("SELECT id FROM users WHERE puserid = '$username'");
while($row = mysql_fetch_row($results)){
$sql = mysql_query("INSERT INTO triviatour1 (user_id) VALUES ( '$row[0]')");
}

It seemed to work when I did the update too it. Now the real test will be how well it works when people start using it.

[charlier]

or you could use

int mysql_insert_id ( [resource link_identifier])

mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.

mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.

[hakre]

hi Knowles,

another hint at the end: provide a check that the same username isn't entered twice!

the solution by charlier is failsafe anyway, because that function will return the new id (key) created. you can use the return value for the second table/db later on.