Sort Rows | MySql

[eevan79]

How to sort rows without skipping numbers when its deleted? For example I use row numbers to display post number in forum script. First message in topic have post_numb 1, second 2, etc...I use it for anchors (permanent link) - viewtopic.php?forum=id&postid=id#post_number . If I have 10 posts in topic I have following numbers: 1,2,3,4,5,6...etc. If I delete post 5, I get this: 1,2,3,4,6,7...

Script for increase post number:

$postnum = mysql_num_rows($result2)+1;

row is post_numb:

I want to sort this row in order (on picure post with numb 5 is deleted), so I need to post 6 became 5, 7 became 6 etc.

 

Here how it looks

 

and when I delete post#5 I gotthis:

 

[trq]

This lookss kinda like a forum, surely posts should be sorted by date/time.

[miancu]

maybe every time you have the delete post operation called, you can add a function called to reorder the post_numb values to avoid having missing numbers. or maybe you should just get them ordered from database after the post_num and then use an index to display #1,#2, etc in the php.

hope this helps!

[eevan79]

This lookss kinda like a forum, surely posts should be sorted by date/time.

Yes, thats minimalistic forum (about 50kb) on which I currently working.

Here is test online. Still no .css styles. I just finished template system.

 

maybe every time you have the delete post operation called, you can add a function called to reorder the post_numb values to avoid having missing numbers. or maybe you should just get them ordered from database after the post_num and then use an index to display #1,#2, etc in the php.

hope this helps!

Thanks for note. But how to reorder post_numb row ?

[miancu]

My idea will be to have a separate function where you get all results from database with (post_numb>just deleted post_numb) ordered by post_numb ASC and using an counter(in PHP) to update each  field post_numb  with that counter(that starts from just deleted post_numb to the total number of records).  You call this function every time you do a delete post and have the deleted post_numb as a param for the function.

 

If you have a lot of records in the database, this function might be a little time consuming, so you might just want with the second option I have you.

 

Hope this is helpful!

 

[Psycho]

You are making this more difficult than it needs to be. The records should have a unique ID which doesn't have to have anything to do with the sort order. Records should be ordered by the timestamp the record was created (as thorpe suggests). You can then add an index number to be "displayed" to the user within the PHP code that creates the HTML (as miancu suggests).

 

[eevan79]

Thanks.

I dont need that extra row for that. I use it only cause I have page system (I though that would be easier).

Here is how I solve it:

$counter= 1;

while ... mysql_fetch_assoc{

$temp_numb = $posts_row['post_id'];

$temp_numb = ($posts_row['post_id'] - $temp_numb+$counter + ($post_per_page*mysql_real_escape_string($_GET['page']))-$post_per_page);

...drawing tables$counter += 1;}

 

Thanks guys.