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