🔒 Locked 🤖 Godot

[web] Auto increment problem on PHP

Started by bargasteh Jun 28, 2006 at 6:43 PM 8 replies 2.7k views

Original Post

Jun 28, 2006 at 6:43 PM

Here's my extremely simple code. My table currently has 10 items in it and since the primary key is set to autoincrement, you'd expect that it would return the integer 10 , but Noooooooooo........... ----------------------------------------- ----------------------------------------- This should return the primary key of the last entry into the table but it doesn't. The output from this is actually ---------------------------------------- Key is Resource id #4 ---------------------------------------- This means that the query is returning "Resource id #4" instead of an integer. I have no idea why this is happening. When I try running the query against mysql via Navicat, SQLyog, or EMS MySQL Manager, I get a "0" for each entry in the table. I don't know for sure, but that leads me to think that something may be wrong mySQL itself. Since I'm running Windows XP for a dev environment, I installed the MySQL binary. I'm unable to get the Windows version of MySQL and PHP 5 to return anything like a logical response. Is anyone else seeing this? Thanks,

Palidine

200

Palidine

Jun 28, 2006 at 6:46 PM

mysql_query does not return what you think it returns. It returns a resource, not the value of the specific item you are querying. This is because typically mysql_query is used to search for more than just a single column value.

read the MAN page:
http://us2.php.net/mysql_query

also, go through the tutorial on that site about using the SQL functions of PHP. They have really good ones that will walk you through the stuff.

-me

bargasteh

Author

bargasteh

Jun 28, 2006 at 6:51 PM

Ok, I am going to read them all.

but now how can I find the next auto_increment number that I have to use during Insertion ?

Thank you.

Palidine

200

Palidine

Jun 28, 2006 at 6:59 PM

AFAIK you do it with the method you used. Except that you then have to extract the data from the resource that's returned from the mysql_query method. i.e. read the tutorials and it will explain how to extract that data from th resource object.

if you read that MAN page i linked it actually has an example of how to extract data from the resource result of mysql_query.

-me

Shiny

Jun 28, 2006 at 7:36 PM

This may or may not be what you're interested in -- but you should know that as long as your id/auto_increment number is listed as a primary key -- then when you insert -- if you omit the key, it will auto-increment for you...hence the whole idea of the auto-increment: it works without your input :)

~Shiny.

RobotechII

Jun 28, 2006 at 7:39 PM

Quote:
Original post by Palidine
AFAIK you do it with the method you used. Except that you then have to extract the data from the resource that's returned from the mysql_query method. i.e. read the tutorials and it will explain how to extract that data from th resource object.

if you read that MAN page i linked it actually has an example of how to extract data from the resource result of mysql_query.

-me

Actually you need to use this function

int mysql_insert_id ( [resource link_identifier] )

Cygnus_X

Jun 28, 2006 at 8:27 PM

Yeah... mysql_insert_id() doesn't go in the mysql query either. The form would look like this:

$Result = mysql_query("Insert into MyTable values ('')");
$ID = mysql_insert_id();

I need to warn you though, this seems to be very unstable. I have had numerous problems with $ID turning out to be 0 for some yet-to-be-explained reason.

RobotechII

Jun 28, 2006 at 10:01 PM

Quote:
Original post by Cygnus_X
Yeah... mysql_insert_id() doesn't go in the mysql query either. The form would look like this:

$Result = mysql_query("Insert into MyTable values ('')");
$ID = mysql_insert_id();

I need to warn you though, this seems to be very unstable. I have had numerous problems with $ID turning out to be 0 for some yet-to-be-explained reason.

I've never had a problem with it and I've used it hundreds of times *shrugs*

ligh

Jul 1, 2006 at 10:56 AM

i think the to get the next auto_increment value you would have to do something like

$result=mysql_query("SELECT MAX(id) AS max_id FROM table GROUP BY id",$link);
$row=fetch_mysql_assoc($result);
$next_id=$row['max_id'] + 1

Cygnus_X

Jul 1, 2006 at 2:06 PM

Quote:
Original post by ligh
i think the to get the next auto_increment value you would have to do something like

$result=mysql_query("SELECT MAX(id) AS max_id FROM table GROUP BY id",$link);
$row=fetch_mysql_assoc($result);
$next_id=$row['max_id'] + 1

The problem with the code above is that you'd have to run a second query on the database. With 1M entries and 1M users, that can be devistating to your server. After mysql_insert_id() quit being reliable for me, I decided to have a general system variable table (1 row) that kept track of the next incriment value. Thus, I have the following:

$Result1 = mysql_query("Select * from SiteVars");
$Row1 = mysql_fetch_array($Result1)
$NextID = $Row1['NextID'];

$Result2 = mysql_query("Update SiteVars set NextID = NextID + 1");
$Result3 = mysql_query("Insert into Table values ('$NextID', ....)");

It doesn't use auto_increment... but if you need the new ID, I find it to be much more stable.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.