Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts

    auto increament trouble

    hi guys, basically im taking up what BluePanther mentioned a while ago to me about using the id of a crew instead of the name of the crew in my database, however im having a bit of trouble when someone makes a crew as its not putting the auto increament into the database in the users table, below is my code, however i dont know how it can work out the auto increament and insert it all in to the users table as its inserted into the crews table aswell:

    PHP Code:
    mysql_query("UPDATE users SET money='".dbSafe($newmoneyfrombuying)."', crewaccept='".dbSafe(Yes)."' WHERE username='".dbSafe($fetch->username)."'") or die("Couldnt substract cash!");
    mysql_query("INSERT INTO `crews` (`id`, `name`, `quote`, `owner`, `bank`,`size`,`color`,`datecreated`,`daily`,`nextdue`) VALUES ('', '".dbSafe($crewname)."', '".dbSafe(None)."', '".dbSafe($fetch->username)."', '".dbSafe(0)."','".dbSafe($users)."', '".dbSafe($color)."','".dbSafe($date)."','".dbSafe($daily)."','".dbSafe($hnextdue2)."');") or die (mysql_error());
    mysql_query("UPDATE users SET crew='".dbSafe($playercrew->id)."' WHERE username='".dbSafe($fetch->username)."'");
    mysql_query("UPDATE applycrew SET crew='".dbSafe(None)."' WHERE username='".dbSafe($fetch->username)."'");
    $message"Crew Created!"
    cheers guys
    Last edited by Dan13071992; 01-22-2012 at 09:09 PM.
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    To use the autoincrement you shouldn't specify that field in the insert.

    After creating the record with the autoincrement value you can then use the LAST_INSERT_ID() function in the SQL for the other table updates in order to collect the value that thye autoincrement used for the insert.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    ok thanks, i fort that by just leaving it blank in the '' that that would be fine :s, but ill take it out, thanks for the heads up,

    so in question to your last post would i insert like this:

    PHP Code:
    mysql_query("UPDATE users SET crew='".dbSafe(LAST_INSERT_ID())."' WHERE username='".dbSafe($fetch->username)."'"); 
    ?

    cheers.

    Dan
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,366
    Thanks
    61
    Thanked 530 Times in 517 Posts
    Just to add to felgalls reply, you can also use the mysql_insert_id() function without running that SQL command that he mentioned which makes it a bit less work.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #5
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    so how would i put that into the code as it is?

    would it be:

    PHP Code:

    $newid 
    mysql_insert_id();
    mysql_query("INSERT INTO `crews` (`id`, `name`, `quote`, `owner`, `bank`,`size`,`color`,`datecreated`,`daily`,`nextdue`) VALUES ('$newid', '".dbSafe($crewname)."', '".dbSafe(None)."', '".dbSafe($fetch->username)."', '".dbSafe(0)."','".dbSafe($users)."', '".dbSafe($color)."','".dbSafe($date)."','".dbSafe($daily)."','".dbSafe($hnextdue2)."');") or die (mysql_error());
    mysql_query("UPDATE users SET crew='".dbSafe($$newid)."' WHERE username='".dbSafe($fetch->username)."'"); 
    is that correct?
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    You don't need to specify

    dbSafe(LAST_INSERT_ID())

    Just LAST_INSERT_ID() by itself is enough as it always refers to the last autoincrement value inserted by the currently running script.

    Also as pointed out, if you need the value in your PHP code you can use mysql_insert_id() to retrieve it into a variable and then use that variable in subsequent database calls (useful if you have more than one autoincrement).
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    Dan13071992 (01-22-2012)

  • #7
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,366
    Thanks
    61
    Thanked 530 Times in 517 Posts
    No, mysql_insert_id() works on the last INSERT that you have run. Basically you run the insert query and THEN run the function.

    It does the same as what felgalls sql does except that its been packaged into a ready to run php function. It must always be used after the inserting SQL statement.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    Dan13071992 (01-22-2012)

  • #8
    Regular Coder
    Join Date
    Dec 2010
    Location
    Kent, UK
    Posts
    573
    Thanks
    23
    Thanked 10 Times in 10 Posts
    thanks guys. i just used this:

    PHP Code:
    mysql_query("UPDATE users SET money='".dbSafe($newmoneyfrombuying)."', crewaccept='".dbSafe(Yes)."' WHERE username='".dbSafe($fetch->username)."'") or die("Couldnt substract cash!");
    mysql_query("INSERT INTO `crews` (`id`, `name`, `quote`, `owner`, `bank`,`size`,`color`,`datecreated`,`daily`,`nextdue`) VALUES ('', '".dbSafe($crewname)."', '".dbSafe(None)."', '".dbSafe($fetch->username)."', '".dbSafe(0)."','".dbSafe($users)."', '".dbSafe($color)."','".dbSafe($date)."','".dbSafe($daily)."','".dbSafe($hnextdue2)."');") or die (mysql_error());
    mysql_query("UPDATE users SET crew='".dbSafe(mysql_insert_id())."' WHERE username='".dbSafe($fetch->username)."'");
    mysql_query("UPDATE applycrew SET crew='".dbSafe(None)."' WHERE username='".dbSafe($fetch->username)."'"); 
    and it works great thanks guys ++ rep for you both
    http://360-tactics.co.uk/forum/index.php

    Crime-Wave

    please post your code wrapped in tags
    please post your PHP wrapped in tags


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •