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
Curtis782
Forum Commoner
Posts: 31 Joined: Tue Oct 25, 2005 3:34 pm
Post
by Curtis782 » Tue Nov 01, 2005 2:51 pm
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Nov 01, 2005 2:53 pm
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 » Tue Nov 01, 2005 3:22 pm
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)
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Nov 01, 2005 3:29 pm
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 » Tue Nov 01, 2005 3:35 pm
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";
shiznatix
DevNet Master
Posts: 2745 Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:
Post
by shiznatix » Tue Nov 01, 2005 3:39 pm
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 » Tue Nov 01, 2005 3:43 pm
I tried this code. This prints:
Code: Select all
Array
(
[LAST_INSERT_ID()+1] => 1
)
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Tue Nov 01, 2005 4:04 pm
look up the documentation on the function..
Jenk
DevNet Master
Posts: 3587 Joined: Mon Sep 19, 2005 6:24 am
Location: London
Post
by Jenk » Tue Nov 01, 2005 4:20 pm
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 » Tue Nov 01, 2005 4:22 pm
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;
Jenk
DevNet Master
Posts: 3587 Joined: Mon Sep 19, 2005 6:24 am
Location: London
Post
by Jenk » Tue Nov 01, 2005 4:26 pm
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 » Tue Nov 01, 2005 4:28 pm
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
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Tue Nov 01, 2005 6:07 pm
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() .
Jenk
DevNet Master
Posts: 3587 Joined: Mon Sep 19, 2005 6:24 am
Location: London
Post
by Jenk » Wed Nov 02, 2005 2:00 am
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.