SitePoint Sponsor |
|
User Tag List
Results 1 to 9 of 9
-
Mar 27, 2002, 08:05 #1
- Join Date
- Nov 2000
- Location
- Switzerland
- Posts
- 2,479
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Multiple inserts and data integrity
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?
-
Mar 27, 2002, 08:35 #2
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
perhaps all one query:
PHP Code:"INSERT INTO table2
SELECT table1.aid
FROM table1
.
.
.
;"
-
Mar 27, 2002, 08:49 #3
- Join Date
- Nov 2000
- Location
- Switzerland
- Posts
- 2,479
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Mar 27, 2002, 08:52 #4
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Perhaps I'm hardheaded. What do you mean about returning the primary key as an error?
Sketch
-
Mar 27, 2002, 08:58 #5
- Join Date
- Nov 2000
- Location
- Switzerland
- Posts
- 2,479
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Mar 27, 2002, 15:39 #6
- Join Date
- Aug 1999
- Location
- East Lansing, MI USA
- Posts
- 12,937
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Is this what you're looking for:
http://www.php.net/manual/en/functio...-insert-id.phpChris Beasley - I publish content and ecommerce sites.
Featured Article: Free Comprehensive SEO Guide
My Guide to Building a Successful Website
My Blog|My Webmaster Forums
-
Mar 27, 2002, 15:44 #7
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.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
-
Mar 27, 2002, 18:24 #8
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
PHP Code: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");
- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
Mar 27, 2002, 18:36 #9
- Join Date
- Nov 2000
- Location
- Switzerland
- Posts
- 2,479
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
Bookmarks