Helpful Information
 
 
Category: Databases
auto increment number

I have two tables in my database. In the first table (member) an autonumber is assigned (membernum) when info is submitted through the form. I'd like to be able to include this number (as a foreign key) in the second table (car) also. Here is what I tried without success:
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>

mysql_query ("INSERT INTO member (fname, lname)
VALUES ('$fname', '$lname')");

mysql_query ("INSERT INTO car (membernum, year, model)
VALUES (mysql_insert_id(), '$year', '$model')");
[/code]
Any ideas on how to do this? Also, how can I set the auto increment number to start with a higher number.

TIA, Greg

insert_id doesn't work without placing a null in to it's place in the query (it's not like a timestamp), so you may need to specify that in the first query.

mysql_query ("insert into member (memid,fname,lname) values (null,'$fname','$lname')");

Then it should work. To start at a higher number for initial value, you'll need to force the number for the first record. What I've done is use a dummy record with a defined number. Later, after you have "real" records you can delete the dummy.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
insert_id doesn't work without placing a null in to it's place in the query (it's not like a timestamp), so you may need to specify that in the first query.

mysql_query ("insert into member (memid,fname,lname) values (null,'$fname','$lname')");

Then it should work. To start at a higher number for initial value, you'll need to force the number for the first record. What I've done is use a dummy record with a defined number. Later, after you have "real" records you can delete the dummy.

[/quote]

Thanks. I tried that and I'm still having problems. The first table gets the data, but the second table doesn't. HEre is my code.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
mysql_query ("INSERT INTO memtest (ffr, fname, lname)
VALUES (null, '$fname', '$lname')");

mysql_query ("INSERT INTO cartest (year, model)
VALUES (mysql_insert_id(), '$year', '$model')");

[/code]

I am still doing something wrong?

Yes, look at your second insert.. you've defined two columns and then assign 3 values!

Get in the habit of using

print mysql_error();

after any mysql query or connection. Will save a lot of debugging time http://www.devshed.com/Talk/Forums/wink.gif

Rod

Well, I fixed what you said and it still wasn't working. I fixed it though. I used this instead :

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
LAST_INSERT_ID()
[/code]

This seems to work better. Thanks for all your help.

BTW, that statement,
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
print mysql_error();
[/code]
is really helpful. Thanks.

DOH! Of course, can't use a php function inside the query. Using the mysql function in there works though.










privacy (GDPR)