Reorder the database record rows through front-end

Question

I am using PHP/MYSQL. I want in my frontend, for the users to sort and rearrange database records entered by the user himself in an earlier stage. The records uploaded by the user may sometimes inserted into the database randomly, so in the user profile he/she might have a facility to rearrange the the rows in the database according to there wise.

Can any one provide me a script that would help me to do so.

id  title     date     desc
1   s1        s1_date  s1_desc
2   s2        s2_date  s2_desc
3   s3        s3_date  s3_desc
4   s4        s4_date  s4_desc
5   s5        s5_date  s5_desc

In the user profile he want to rearrange the rows so that s2 will go one up and will save as s2->s1 and s1->s2 for every field.. may be swapping of records in between

I am looking it in another way.. user page will show the field in rows wise and in the side there will be an input box of each the current row id say (id) is set as value of that input box so that the user can change in between and submit the whole reordered list to the back-end. In that case what can we do... there will be many swapings to to concurrently..

Answer 1

Best solution is to add another field "ordering" where you can store by default int ID, and while rearranging rows, you could swap values of ordering between these rows. In query you would do ORDER BY ordering ASC/DESC

Answer 2

Get both rows and update back the values:

Assuming the tablename is: t

$result = mysql_query("SELECT title, `date`, `desc` FROM t WHERE id = 1");
$row1 = mysql_fetch_assoc($result);

$result = mysql_query("SELECT title, `date`, `desc` FROM t WHERE id = 2");
$row2 = mysql_fetch_assoc($result);

mysql_query("UPDATE t SET title = '{$row2['title']}', `date` = '{$row2['date']}', `desc` = '{$row2['desc']}' WHERE id = 1");
mysql_query("UPDATE t SET title = '{$row1['title']}', `date` = '{$row1['date']}', `desc` = '{$row1['desc']}' WHERE id = 2");