mysql_num_rows question

Ma7bob

Hi!, i have table with auto_increment primary key .. some times the record at the middle get deleted from the users ... what i need is .. how to get for example the next ID (or num) for the next reord .. for example

if i have records with $num=1,5,6

and i sent record num 1 for my code .. i want to get num 5

here is what i am using ? can you see any problems?

$upper=$recNum+1; 
$result =mysql_query("select *from $tableName WHERE num=$upper")or
die (mysql_error());

while (mysql_num_rows($result) < 1) { 
$upper++;
}

for some reason this won't work .. Thanks in advance 🙂 appreciate any help 🙂

phat-tramp

Ok, I'll have a crack at this one since I'm new...

mysql_num_rows returns a number...the number of records returned by your query.

In your snippet, the return value from mysql_num_rows would only ever by a 0 or 1 which makes the logic in your while loop useless, especially if you go back up and set it again to $recNum+1 before doing another query against your database...you'll be doing the exact same thing over and over.

Think about what goes inside your while loop and I think you'll have more luck.

laserlight

Well, a possible solution would be to use LIMIT
For example, if you wanted to select the 3rd record only, which now has num=6, you could try:

SELECT * FROM tablename LIMIT 2, 1

This would skip the first 2 rows, then return 1 row, i.e. the 3rd row.

Another way of doing it, if you plan you go through all (or more than 1) row in your DB table would be to extract the rows required, then loop through them, picking out the ID number along with the other data needed.

For example, if you only needed all the id data in a particular table in a mysql db:

$query = mysql_query("SELECT id FROM tablename");
while ($row = mysql_fetch_assoc($query)) {
	//access $row['id'];
}

Ma7bob

thanks phat-tramp and laserlight 🙂
actually what laserlight suggested worked great 🙂 .. here is what i am using now 🙂 ..

$result =mysql_query("select * from $tableName WHERE num > $upNum LIMIT 1")or
die (mysql_error());


while ($row =mysql_fetch_array($result)){

echo $row["num"];
  }
}