_25_11_2024_15_40_33.jpg)
|
Socoder -> Web Development -> SQL Forum Tables Problem |
|
| Fri, 31 Aug 2007, 07:13 | |
shroom_monk
|
OK, I have a little problem with some forum code I'm writing in PHP, with MySQL. I have two tables I'm dealing with: one for topics and one for posts. The topics table stores the topic's name and id (primary key), and the posts table stores the post, the post id (primary key) and the id of the topic it goes in. Now, if a user wants to post a topic, they go to the form, type in the topic's title and the start post, and press Submit. That works fine. The problem is with inserting the data into the tables. First, I insert the title of the topic into the topics table. It will automatically be given the next id for its primary key (I'm using auto_increment). However, when I want to insert the first post into the table, I need to know what that topic id was. So my question is: how do I insert a new row into a table, and then find out an automatically generated value from it? -=-=- A mushroom a day keeps the doctor away... Keep It Simple, Shroom! |
| Fri, 31 Aug 2007, 07:22 | |
Jayenkai
|
Yeah, annoyingly SQL won't actually return the ID given, so you have to put something specific in, then find that! ie.. 1. Insert - Topic Name, Starter, Time Created But instead of putting the REAL time created, put in something like 1234567890 2. Do a quick search to find 1234567890 to get the topic's ID 3. Update the time to the right time. 4. Use the ID in the Post. -=-=- ''Load, Next List!'' 
|
| Fri, 31 Aug 2007, 07:29 | |
|
shroom_monk
|
Yeah, I could probably use something along the lines of a random ID number - and just hope that someone else posting at the same time doesn't generate the same number... although that's unlikely. Yeah, I'll use that. Thanks Jay! 
-=-=- A mushroom a day keeps the doctor away... Keep It Simple, Shroom! |
| Sat, 01 Sep 2007, 02:01 | |
|
oscar |
there is actually a way to return the last query id to a variable.. I did it at work yesterday... but alas i can't remember what it was so it might have to wait until monday when I can check out the code again... it was something like last_query_id() or something i think and it returns the primary key or false if the query failed i shall get back to you on monday (it's saturday night here) 
|
| Sat, 01 Sep 2007, 09:20 | |
|
shroom_monk
|
Well, I got Jay's method working quite nicely... still, it would be quite nice to know for future reference! Thanks oscar! 
-=-=- A mushroom a day keeps the doctor away... Keep It Simple, Shroom! |
| Mon, 03 Sep 2007, 22:23 | |
|
oscar |
mysql_insert_id is the php command you want... it returns the last id generated...or should i say... the id of the last insert
|
| Tue, 04 Sep 2007, 00:36 | |
|
shroom_monk
|
Cool, thanks oscar! That probably would have made my code a lot simpler at the time, but it's all finished now. Still that would be good for future reference! What's the exact syntax for it? I assume it's: -=-=- A mushroom a day keeps the doctor away... Keep It Simple, Shroom! |
| Tue, 04 Sep 2007, 01:57 | |
|
oscar |
you can leave it blank and it'll return the last performed query eg. https://au.php.net/manual/en/function.mysql-insert-id.php
|
| Tue, 04 Sep 2007, 01:58 | |
shroom_monk
|
OK, thanks.
|
|
|
| |||
|
| ||||