Multiple inserts and data integrity

[HarryF]

Wondering if anyone has some tips on the best way to handle stuff like this.

I've frequently been in the situation where first I add a row to a "table a". Then I need to update a many to many table ( table b) with the primary key from table a.

The solutions I've come up with basically looking something like this;

"INSERT INTO TABLE A ..."
"SELECT id FROM TABLE A WHERE ..."
"INSERT INTO TABLE B aid = '$idfromtablea', ..."

For the specific cases I've had this has been no problem because I've had something like a UNIQUE column (such as a username) in table a which I can also use to identify with.

But that's not a generic solution - depends on the data I'm using. I'm wondering if there's something down the road of mysql_affected_rows but can't see it.

Has anyone got any better ideas for doing this?

[Technosailor]

perhaps all one query:

"INSERT INTO table2
SELECT table1.aid
FROM table1
.
.
.
;" 

Sketch

[HarryF]

Alright! Had no idea you could do that. That's a big step forward - reduces the last two queries to one.

Wonder if there's some trick you can pull for the first query that returns the primary key as an "error".

Or perhaps there's something that can be done using the MySQL link id? Still pondering...

Not really a problem you have in other dbs, where you can put foreign keys into another table.

[Technosailor]

Perhaps I'm hardheaded. What do you mean about returning the primary key as an error?

Sketch

[HarryF]

Not too sure right now

Was trying to think if there's a trick to generate a "non-critical" warning on the primary key column then use mysql_error to tell you what it's value is. Sounding dodgier the more I type. Probably not.

[aspen]

Is this what you're looking for:

http://www.php.net/manual/en/functio...-insert-id.php

[Technosailor]

Originally posted by HarryF
Not too sure right now

Was trying to think if there's a trick to generate a "non-critical" warning on the primary key column then use mysql_error to tell you what it's value is. Sounding dodgier the more I type. Probably not.

Intriguing nonetheless. What would you want it to warn you of? What would you accomplish by getting an error message with the value that you couldn't by just reading the value from the db?

Sketch

[DR_LaRRY_PEpPeR]

it sounds like you need to find out what the AUTO_INCREMENT number is that was just generated, for INSERTing into your other table? then aspen's function, mysql_insert_id(), is what you want:

mysql_query('INSERT INTO table_a ...');
$id_from_table_a = mysql_insert_id();
mysql_query("INSERT INTO table_b SET aid=$id_from_table_a"); 

[HarryF]

Whoa! That's a prime case for RTFM! mysql_insert_id () is exactly what I'm looking for.

I've looked and looked for a function like that - honest!