In this tutorial we'll show how to change the order of database records on the list page by clicking up or down arrow button in selected row.
- As a first step you need to add a numeric field named Rank to database table in questions. It makes sense to populate this field with some initial values. Besides that ranking makes he most sense when data is sorted by Rank field.
SELECT
....
Rank
FROM Cars
ORDER BY Rank
2. Proceed to the Visual Editor and set 'View as' type of Rank field to 'Custom'. Paste the following code there:
$keycolumn="ID";
global $strTableName;
$value = '<a class="tablelinks" href="'.$strTableName.'_list.php?a=return&custom=up&recid=' .$data["$keycolumn"] . '">
<img style="border: 0px;float: right; clear: right;margin: 2 2 2 8;" src="images/up1.png"/></a>
<a class="tablelinks" href="'.$strTableName.'_list.php?a=return&custom=down&recid=' .$data["$keycolumn"] . '">
<img style="border: 0px;float: right; clear: right;margin: 2 2 2 8;" src="images/down1.png"/></a>
<p>' . $value . '</p>';
This code displays the value of Rank field plus two hyperlinked images. Hyperlinks looks like this:
Cars_list.php?a=return&custom=up&recid=3
Cars_list.php?a=return&custom=down&recid=3
custom points either 'up' or 'down', recid contains the ID of record to be moved.
3. Proceed to Events editor and post the following code to BeforeSQLQuery event. That's where we see if parameters were passed via URL and change ranks of two records sitting next to each other.
global $strTableName;
$previd="";
if ($_REQUEST["custom"])
{
$keycolumn="ID";
$rankcolumn="Rank";
$rs = CustomQuery($strSQL);
$data = db_fetch_array($rs);
while($data)
{
if ($data[$keycolumn]==$_REQUEST["recid"])
{
if ($_REQUEST["custom"]=="up" && $previd!="")
{
CustomQuery("update ".AddTableWrappers($strTableName)." set $rankcolumn=".$data[$rankcolumn]." where $keycolumn=".$previd);
CustomQuery("update ".AddTableWrappers($strTableName)." set $rankcolumn=$prevrank where $keycolumn=".$data[$keycolumn]);
}
else if ($_REQUEST["custom"]=="down")
{
$previd=$data[$keycolumn];
$prevrank=$data[$rankcolumn];
$data = db_fetch_array($rs);
if ($data)
{
CustomQuery("update ".AddTableWrappers($strTableName)." set $rankcolumn=".$data[$rankcolumn]." where $keycolumn=".$previd);
CustomQuery("update ".AddTableWrappers($strTableName)." set $rankcolumn=$prevrank where $keycolumn=".$data[$keycolumn]);
}
}
break;
}
$previd=$data[$keycolumn];
$prevrank=$data[$rankcolumn];
$data = db_fetch_array($rs);
}
}
4. Download
and
images and save them in 'images' directory under output directory (right click on image and choose 'Save image as').
This is it. Just make sure you use correct rank and key column names in the beginning of each of above events.
Tags: move rows up and down, change records order, re-arrange rows
