how to get next row id

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
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

how to get next row id

Post by itsmani1 »

Code: Select all

$Result = mysql_db_query($db,"select * from adds where AddID='1'",$cid);
	$Number = mysql_num_rows($Result);
	if($Number > 0)
	{
		while($Rows = mysql_fetch_object())
		{
			$AddID = $Rows -> AddID;
			$Image = $Row -> Image;
			$Name = $Rows -> Name;
		}
		mysql_db_query($db,"update adds set Current='1' where AddID='1+1'",$cid);
in the above peace of code i am trying to update the next row but let assume tht if there is no AddID 2 (if id 2 and 3 are been deleted by some one and the next id present in db is 4) what will hapen. is there any method that can tell about the next row's ID....
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

if its a ID field with autoincrementing values starting from 1 (the usual)

Code: Select all

$query = "SELECT something FROM table";
$result = mysql_query($query) or die (mysql_error());
$num_rows = mysql_num_rows($result);
$nextID = $num_rows++;
or

Code: Select all

$query = "SELECT something FROM table order BY id DESC LIMIT 1";
$result = mysql_query($query) or die (mysql_error());
// then add one onto returned result
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

malcolmboston wrote:if its a ID field with autoincrementing values starting from 1 (the usual)

Code: Select all

$query = "SELECT something FROM table";
$result = mysql_query($query) or die (mysql_error());
$num_rows = mysql_num_rows($result);
$nextID = $num_rows++;
Now here is a point :) if last id was 23 and by $id ++ it will move to 24th if 24th is deleted or not present what will hapen does it has tendency to move to 25th????

or

Code: Select all

$query = "SELECT something FROM table order BY id DESC LIMIT 1";
$result = mysql_query($query) or die (mysql_error());
// then add one onto returned result
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

you can reset the autoincrementing number back to there if you wish but by default it will skip it, i have employed a function many times that always checks ID and deletes any "blanks"
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

malcolmboston wrote:you can reset the autoincrementing number back to there if you wish but by default it will skip it, i have employed a function many times that always checks ID and deletes any "blanks"
You mean it will skip the the deleted and will move to next number (for example if 24th is deleted it will automatically move to 25 or if 25 is not there it will move to 26th wot so ever....)
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

what i mean is this:

say you have a table where your upto ID 7
you use ID 8 then delete it
it will now use ID 9 even thought ID 8 is avalailable

you can stop this as i mentioned earlier but i dont hjave my function available to me right now and cannot remember the name of it, its a MySQL function i know that much
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

just read http://dev.mysql.com/doc/mysql/en/update.html

and pay attention to what the order by and limit clauses can do for you ;)


probably query would look like

Code: Select all

update tablename
set column='new value'
where id > 1
order by id
limit 1
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

oki
Post Reply