Page 1 of 1
How can I display last ID in MySQL DB + 1?
Posted: Tue Nov 01, 2005 2:51 pm
by Curtis782
I'm creating a work order system and was curious how I could display an accurate work order ID # on an add form (when no new records having been submitted to DB)?
I tried something like this...
Code: Select all
$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1");
mysql_select_db("devsite_workorder", $link);
$result = mysql_query("SELECT * FROM workorder", $link);
$num_rows = mysql_num_rows($result) +1;
echo "$num_rows";
But when I delete a work order/DB record I notice this does not produce correct output.
Posted: Tue Nov 01, 2005 2:53 pm
by feyd
Code: Select all
SELECT LAST_INSERT_ID()+1 FROM `table`
Posted: Tue Nov 01, 2005 3:22 pm
by Curtis782
Is this the correct implementation?
Code: Select all
$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1");
mysql_select_db("devsite_workorder", $link);
$result = mysql_query("SELECT LAST_INSERT_ID()+1 FROM workorder", $link);
$num_rows = mysql_num_rows($result);
echo "$num_rows";
//echo "$result";
I tried the above but the echo statement is still printing 36
MySQL DB records 1-34, no id breaks
...34,41,37,39
(record 41 is not showing up consecutively for some reason when I browse the DB via phpMyAdmin)
Posted: Tue Nov 01, 2005 3:29 pm
by feyd
you don't use num_rows().. the data is inside the results of the query..
Posted: Tue Nov 01, 2005 3:35 pm
by Curtis782
I'm getting the following output: "Resource id #5"
Code: Select all
$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1");
mysql_select_db("devsite_workorder", $link);
$result = mysql_query("SELECT LAST_INSERT_ID()+1 FROM workorder", $link);
echo "$result";
Posted: Tue Nov 01, 2005 3:39 pm
by shiznatix
Code: Select all
$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1");
mysql_select_db("devsite_workorder", $link);
$result = mysql_query("SELECT LAST_INSERT_ID()+1 FROM workorder", $link);
$info = mysql_fetch_assoc($result);
echo '<pre>';
print_r($info);
echo '</pre>';
you gotta pull the information out of the query with mysql_fetch_row or simmilar function which turns it into a array
Posted: Tue Nov 01, 2005 3:43 pm
by Curtis782
I tried this code. This prints:
Code: Select all
Array
(
[LAST_INSERT_ID()+1] => 1
)
Posted: Tue Nov 01, 2005 4:04 pm
by feyd
look up the documentation on the function..

Posted: Tue Nov 01, 2005 4:20 pm
by Jenk
Alternatively..
Code: Select all
<?php
$id_plus_one = mysql_insert_id() + 1;
?>
mysql_insert_id()
Posted: Tue Nov 01, 2005 4:22 pm
by ryanlwh
doesn't mysql_insert_id() or LAST_INSERT_ID work only when an INSERT statement is called? wouldn't it be MAX(id) in this case??
Code: Select all
$link = mysql_connect("$DB_HOSTNAME1", "$DB_USERNAME1", "$DB_PASSWORD1");
mysql_select_db("devsite_workorder", $link);
$result = mysql_query("SELECT MAX(id)+1 FROM workorder", $link);
$info = mysql_result($result,0,0);
echo $info;
Posted: Tue Nov 01, 2005 4:26 pm
by Jenk
no.
last/mysql_insert_id() both return the AUTO_INCREMENT ID field of the last successfully inserted/replaced row on that connection.
Where as using select max(ID) is prone to error due to concurrent updates.

Posted: Tue Nov 01, 2005 4:28 pm
by ryanlwh
Jenk wrote:no.
last/mysql_insert_id() both return the AUTO_INCREMENT ID field of the last successfully inserted/replaced row on that connection.
Where as using select max(ID) is prone to error due to concurrent updates.

right, but look at his post:
Code: Select all
Array
(
[LAST_INSERT_ID()+1] => 1
)
if it was a fresh new connection then last_insert_id doesn't work either
Posted: Tue Nov 01, 2005 6:07 pm
by Chris Corbyn
ryanlwh wrote:
right, but look at his post:
Code: Select all
Array
(
[LAST_INSERT_ID()+1] => 1
)
if it was a fresh new connection then last_insert_id doesn't work either
It's the way the query was written that caused that. It's probably better to use PHP's function to do it. mysql_insert_id() .
Posted: Wed Nov 02, 2005 2:00 am
by Jenk
ryanlwh wrote:Jenk wrote:no.
last/mysql_insert_id() both return the AUTO_INCREMENT ID field of the last successfully inserted/replaced row on that connection.
Where as using select max(ID) is prone to error due to concurrent updates.

right, but look at his post:
Code: Select all
Array
(
[LAST_INSERT_ID()+1] => 1
)
if it was a fresh new connection then last_insert_id doesn't work either
You are quite right, sorry, I some how thought he was wanting to gain the next ID after an insert.