Giving mysql rows new numbers everytime a page loads.

[Madatan]

I'm gonna try to explain what i'm after here.

 

I have lets say 11 rows in a mysql table with ID Name and Counter columns. ID is on auto_increment. Each time I add one row in this table with a php script the row gets for example 118 - Doofus.jpg - 12. By this I mean that every row gets an unique Counter number, but not random, instead they have to be from 1,2,3,4,5,6,7 etc etc. So that the first row always gets 1 and the last row always gets the last number in the order, in this case 12.

When a row in this table is deleted, also via a PHP script, the range of number gets messed up. Maybe I delete a row with the Counter number 5. Now I have a gap. So what I want to do is that everytime I load a page I want these numbers rearranged so that it always is 1,2,3,4,5,6,7 etc etc.

 

I hope I made myself clear and I hope someone can help me, I've tried loops with mysql Update but to no avail.

 

Thanks for all your help!

 

 

[Rohan Shenoy]

$sql="SELECT * FROM table_name ORDER BY id ASC";

 

 

[rhodesa]

I would guess that you could alter your PHP code to handle gaps, but if not, closing the gap can be done pretty easily.

 

I am going to recommend that the counter column be thrown away all together, and use something like this instead:

SELECT items.*,count(siblings.id) as counter FROM table_name as items,table_name as siblings where siblings.id <= items.id group by items.id

The query joins itself and automatically calculates the counter value.

[Madatan]

Thanks for that rhodesa but I do infact need the Counter column and I need the code to update the numbers in the database.

 

Counter Column

 

1

2

3

4

10

12

 

If the Counter column looks like this I want it to update into looking like(in the database) this once I load the page:

 

Counter Column

 

1

2

3

4

5

6

 

I hope I explained a little better now. Its not the output i'm really worried about its rather the numbers that are stored in the Counter column in the database.

[rhodesa]

Well, you can always do this:

 

<?php
  //Do your DB connection

  $result = mysql_query("SELECT `id` FROM `tablename` ORDER BY `counter`");
  for($n = 1;$row = mysql_fetch_assoc($result);$n++)
    mysql_query("UPDATE `tablename` SET `counter` = '{$n}' WHERE `id` = '{$row['id']}'");
?>