SQL Forum Tables Problem

-=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- (c) WidthPadding Industries 1987 0\|209\|0 -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=-
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: $lastid = mysql_insert_id($result) --v -=-=- 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. $lastid=mysql_insert_id(); --v https://au.php.net/manual/en/function.mysql-insert-id.php
Tue, 04 Sep 2007, 01:58
shroom_monk	OK, thanks.