[RESOLVED] Complex SQL question

deltaforce229

I have a list of urls all with an auto increment id

is there a way so that if i remove id 3, id 4 and 5 will drop down and become 3 and 4?

This is some example code of what i'm doing:


$viewresult = mysql_query('SELECT * FROM `xfire_ads`') or exit(mysql_error());
$num_rows = mysql_num_rows($viewresult);
$autoreset = "ALTER TABLE `xfire_ads` AUTO_INCREMENT = $num_rows";
mysql_query($autoreset);
echo("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\">");   

while ($row = mysql_fetch_row($viewresult)) {
      echo "<tr><td>id [".$row[0]."]</td><td>".$row[1]."</td></tr>";
}
echo("</table>");

bradgrafelman

You could run a query like this:

SET @new_id=0;
UPDATE `myTable` SET `ID`=(@new_id:=@new_id+1) ORDER BY `ID`;

though I'm not sure why you'd want to re-number the ID's....

Piranha

There is absolutely no reason to do what you want. By doing it you might even get into trouble since the integrity of the database may suffer.

deltaforce229

@ Thanks i'll try that.

@ There is a reason to do what i want! The URLS are picked at random based on their id and how man urls are in the database.

Edit: thanks bradgrafelman thats worked 🙂

dagon

SELECT * FROM foo order by rand() Limit 1

problem solved, no need to reorder

bradgrafelman

As Piranha points out in his questioning, you shouldn't ever need to re-number an AUTO_INCREMENT column. I haven't found a situtation yet where that would need to be done.