Page 1 of 1

how to get next row id

Posted: Thu May 05, 2005 7:44 am
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....

Posted: Thu May 05, 2005 7:54 am
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

Posted: Thu May 05, 2005 8:06 am
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

Posted: Thu May 05, 2005 8:09 am
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"

Posted: Thu May 05, 2005 8:23 am
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....)

Posted: Thu May 05, 2005 8:49 am
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

Posted: Thu May 05, 2005 9:09 am
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

Posted: Thu May 05, 2005 9:11 am
by itsmani1
oki