How can I display last ID in MySQL DB + 1?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Curtis782
Forum Commoner
Posts: 31
Joined: Tue Oct 25, 2005 3:34 pm

How can I display last ID in MySQL DB + 1?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT LAST_INSERT_ID()+1 FROM `table`
Curtis782
Forum Commoner
Posts: 31
Joined: Tue Oct 25, 2005 3:34 pm

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you don't use num_rows().. the data is inside the results of the query..
Curtis782
Forum Commoner
Posts: 31
Joined: Tue Oct 25, 2005 3:34 pm

Post 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";
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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
Curtis782
Forum Commoner
Posts: 31
Joined: Tue Oct 25, 2005 3:34 pm

Post by Curtis782 »

I tried this code. This prints:

Code: Select all

Array
(
    [LAST_INSERT_ID()+1] => 1
)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look up the documentation on the function.. :roll:
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Alternatively..

Code: Select all

<?php

$id_plus_one = mysql_insert_id() + 1;

?>
mysql_insert_id()
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post 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;
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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. :)
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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() .
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
Post Reply