Php Get next auto-increacment number

[randydg]

Hi, Im trying to use this code to get the last number used under "id" say 5300
and use that number +1 everytime. This code should pull the last used number and increase it by 1, but prety much I need to get $id to use the corrrect new number

$query="SELECT `id` FROM `customers`";
		$result=mysql_query($query);
		$num=mysql_numrows($result);
		$id=$num;
		while ($id < $num) {
		$id++;
		};
//Rest of the code, the code above is the problem
$sql4 = "INSERT INTO customers VALUES('$id','$name','$phone','$alt','$address','$city','$state','$zip')";
	$result4 = mysql_query($sql4); 
    if (!$result4) { 
            die('Invalid query: '.mysql_error()); 
    } 

[ixalmida]

Try this:

$query = "SELECT MAX(id) AS last_id FROM Customers";
$result = $mysql_query($query);
$row = mysql_fetch_assoc($result);
$last_id = $row['last_id'];

[bradgrafelman]

Note that using such code means you'll be vulnerable to race conditions.

Why do you need to manually increment the value? Why not just let the DB take care of that for you (it's called an auto increment column for a reason, y'know 🙂)?

[randydg]

Good Question, the answer is simply, its not working, i've proven this by manualy adding an unused number and my script works, but if i set the db to auto-increment I end up with duplicates.

[bradgrafelman]

randydg wrote:

if i set the db to auto-increment I end up with duplicates.

Erm... that's not possible, unless you a) didn't correctly define an AUTO_INCREMENT column, or b) have some weirdly-configured MySQL instance that doesn't check key constraints.

[ixalmida]

As always, Brad makes a good point. I tend to answer questions without considering the implications of the methods. It would be unusual to insert this way.

[randydg]

AUTO_INCREMENT is set on the field ID where the numbers are, and I dont have anything wierd setup. normal mysql database.

Ixalmida: From what i've read online max() is a bad way to do it.

ixalmida;10959146 wrote:

Try this:

$query = "SELECT MAX(id) AS last_id FROM Customers";
$result = $mysql_query($query);
$row = mysql_fetch_assoc($result);
$last_id = $row['last_id'];

[dagon]

this is the best way to do it:

SELECT LAST_INSERT_ID();

[bradgrafelman]

dagon;10959158 wrote:

this is the best way to do it:

SELECT LAST_INSERT_ID();

But that's only per-session, and if called before any INSERT statements in a session then you'll just get 0.

Plus, it leads to the same (or perhaps slightly worse) race condition that a "SELECT MAX(id)" would.

randydg wrote:

AUTO_INCREMENT is set on the field ID where the numbers are

Show us a CREATE TABLE statement after you've added the AUTO_INCREMENT as well as what your SQL query(ies) look like after allowing MySQL to handle the column.

randydg wrote:

Ixalmida: From what i've read online max() is a bad way to do it.

Indeed; the "best" (or perhaps "only good") way to do it is to use an AUTO_INCREMENT column and let the database handle it. :p

[randydg]

Pherhaps this needs better explaination, maybe that will help you.
This is for creating a new worksheet that submits info such as name,phone,address,etc... With my current code it works fine untill you enter the same info in again it ties it to the first submited info, and I have narrowed down this problem to the id, some how its giving it the same id because its the same name, etc..
so in the code if i say $id=199; and 199 is not in use it uses that id to put the info in and never has duplicated.

bradgrafelman;10959160 wrote:

But that's only per-session, and if called before any INSERT statements in a session then you'll just get 0.

Show us a CREATE TABLE statement after you've added the AUTO_INCREMENT as well as what your SQL query(ies) look like after allowing MySQL to handle the column.

Indeed; the "best" (or perhaps "only good") way to do it is to use an AUTO_INCREMENT column and let the database handle it. :p

[ixalmida]

If you've been manually adding records and your auto-increment is behind your manual IDs, then of course you'd have duplicates. It's easy to fix that:

ALTER TABLE mytable AUTO_INCREMENT=999999

(Fill in your own number, of course. You could also use an expression.)

[randydg]

I dont think thats the problem, because its been happening before i did it.

ixalmida;10959242 wrote:

If you've been manually adding records and your auto-increment is behind your manual IDs, then of course you'd have duplicates. It's easy to fix that:

ALTER TABLE mytable AUTO_INCREMENT=999999

(Fill in your own number, of course. You could also use an expression.)

[randydg]

Sence I cant edit my last post, sorry for the double post but this has been resolved
by the following code:

$query="SELECT `id` FROM `customers`";
		$result=mysql_query($query);
	    $id=$result;
		$id++;

[bradgrafelman]

Just to note again, using that code will leave you subject to race conditions which can cause queries to fail due to duplicate id's.

[randydg]

Ok, but I did some modifications to make sure that wouldnt happen to the rest of the code. Now there is no way it can get out of order unless someone does it on purpose, by changing id# in the database to a used one.

bradgrafelman;10959277 wrote:

Just to note again, using that code will leave you subject to race conditions which can cause queries to fail due to duplicate id's.