#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Lancaster, PA, USA
    Posts
    8
    Rep Power
    0
    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
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Lancaster, PA, USA
    Posts
    8
    Rep Power
    0
    <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?

  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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

    Rod
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Lancaster, PA, USA
    Posts
    8
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    DOH! Of course, can't use a php function inside the query. Using the mysql function in there works though.

Similar Threads

  1. Using auto increment
    By Lennier in forum MySQL Help
    Replies: 4
    Last Post: December 10th, 2003, 02:08 AM
  2. Replies: 1
    Last Post: November 22nd, 2003, 04:02 PM
  3. Auto increment
    By arbex in forum MySQL Help
    Replies: 3
    Last Post: July 28th, 2003, 11:33 AM
  4. auto number
    By superman in forum PHP Development
    Replies: 7
    Last Post: July 1st, 2003, 03:55 AM
  5. PHP/mySQL insert into statement with auto increment
    By witte_nl in forum PHP Development
    Replies: 3
    Last Post: August 20th, 2001, 08:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo