Simple way to change the records order

Simple way to change the records order

Many times we have the need to change the table records order. Joomla is an example. In joomla, many modules has the feature to change the order of the records.

Here is an example of ordering the table records. Also added the code if we want to add a record into a particular position.

Assume that there is a table 'f_users' and it has fields like users_id,user_pos . We are considering this only :).

First of all, we are listing the records based on user_pos. And add the links for 'up' and 'down' .

Source code

<?php

// data base connection codes

extract($_GET);

// create a new user to the particular position

// ************ New user creation section starts *******************

if(isset($action))
{
// assume that the position is 5
$position = 5;
// increment the position by 1 if the user position is greater than 5
mysql_query("UPDATE f_users SET user_pos=user_pos+1 WHERE user_pos >= $position");
// inserting new user
mysql_query("insert into f_users(users_name,user_pos) values('blabla',$position)");
}
// ************ New user creation section enda *******************

if(isset($move))
{
switch($move)
{
case 'up':
// this has to move to top
$base = $id;
// find the top one
$prevOne = mysql_result(mysql_query("SELECT MAX(user_pos) FROM f_users WHERE user_pos < $base "),0);
// swapping the values

mysql_query("UPDATE f_users SET user_pos = CASE user_pos WHEN $base THEN $prevOne WHEN $prevOne THEN $base END WHERE user_pos IN ($base, $prevOne)");

break;
case 'down':
// this has to move down
$base = $id;
// find the bottom one
$nextOne = mysql_result(mysql_query("SELECT MIN(user_pos) FROM f_users WHERE user_pos > $base "),0);
// swapping the values
mysql_query("UPDATE f_users SET user_pos = CASE user_pos WHEN $base THEN $nextOne WHEN $nextOne THEN $base END WHERE user_pos IN ($base, $nextOne)");
break;
default:
break;
}
}

$first = mysql_result(mysql_query("SELECT MIN(user_pos) FROM f_users "),0);
$last = mysql_result(mysql_query("SELECT MAX(user_pos) FROM f_users "),0);
$res = mysql_query("select users_id,user_pos FROM f_users ORDER BY user_pos");
?>
</p>
<table width="26%" border="1" cellspacing="0" cellpadding="0">
<tr>
<td width="18%">&nbsp;</td>
<td width="39%">&nbsp;</td>
<td width="43%">&nbsp;</td>
</tr>
<?php
while($row = mysql_fetch_array($res))
{
?>
<tr>
<td><?php echo $row['users_id']; ?></td>
<td><?php echo $row['user_pos']; ?></td>
<td>
<?php
if($row['user_pos']!=$first)
echo '<a href="?move=up&id='.$row['user_pos'].'">Up</a>';
?>
&nbsp;&nbsp;&nbsp;&nbsp;
<?php
if($row['user_pos']!=$last)
echo '<a href="?move=down&id='.$row['user_pos'].'">down</a>';
?>
</td>
</tr>
<?php
}
?>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><a href="?action=adduser">add user</a></td>
</tr>
</table>