[SOLVED] MySQL id numbers

[Andy17]

Hey again,

 

Let's say I have a MySQL table with id 0, 1, 2, 3, 4, 5 and I delete the rows with id=2, 3. This would leave me with the rows with id=0, 1, 4, 5. Then the next time I add content to my table, the new row's id will automatically be 6, even though some previous rows were deleted. Is there are way I can make it "fill out" the missing rows? I mean, so the next two rows would be with id=2, 3 instead of 5, 6; so there are no "spaces"?

 

I hope you get my point.

[waynew]

Andy. Do not worry about the gaps in your id numbers. My advice is to leave it be. Messing around with id numbers in order to make them appear one after the other, without any spaces in the count is an antipattern. i.e not recommended.

[Andy17]

Well, it just caused me a small problem in my "random joke" script. I have the total number of rows set to $maxrows and I generate a number between 1 and $maxrows. That generated number is the id of the row I want to show the content of. So, for example, I have a table with 10 rows, then it will generate a number from 1-10. If the number is 4, then the row with id=4 will be shown. However, if that row is removed, nothing will be shown.

 

Here is some of the code:

 

<?php

$number = rand(1, $maxrows);

$sql1 = "SELECT * FROM jokes WHERE id = '$number'";
$result = mysql_query($sql1);

if ($result)

{

$row = mysql_fetch_array($result);

   // Echoing the content here

}

?>

 

Any suggestions of a better way to do it to avoid the id gap problem?

 

Thank you.

[waynew]

$result = mysql_query("SELECT RAND(*) FROM jokes LIMIT 1") or die(mysql_error());

[waynew]

$result = mysql_query("SELECT * FROM jokes ORDER BY RAND() LIMIT 1") or die(mysql_error());

[Andy17]

Worked perfectly, thanks a lot!