...

View Full Version : Resolved auto increament trouble



Dan13071992
01-22-2012, 08:14 PM
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:



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

felgall
01-22-2012, 09:18 PM
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.

Dan13071992
01-22-2012, 09:23 PM
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:



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


?

cheers.

Dan

tangoforce
01-22-2012, 09:47 PM
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.

Dan13071992
01-22-2012, 09:53 PM
so how would i put that into the code as it is?

would it be:




$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?

felgall
01-22-2012, 09:55 PM
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).

tangoforce
01-22-2012, 09:56 PM
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.

Dan13071992
01-22-2012, 09:59 PM
thanks guys. i just used this:



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 :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum