PHP Freaks

So you have some tabular data printed out in your browser. You can even change the order of the information by clicking on the column name at the top of your list. But can you make your own custom list order?

Or maybe you're deciding to make a content management system (CMS). You're making your features modular, so it's easier to add/remove modules. How do you go about displaying them in a custom order in the browser?

Or to illustrate with simple numbers: If you have a list ordered 1 2 3 4 5 and you're wanting to somehow make it 1 3 2 5 4 or 1 2 3 5 4 or 1 5 2 4 3 or whatever else, then you've come to the right place.

Just want to say up front that yes, I am aware that there are several ajax or other "web 2.0" type methods, frameworks, etc.. that offer this sort of thing. You can drag and drop rows and it's flashy and no, that's not what you're going to get out of this tutorial. This is straight php. No bells and whistles and warm fuzzy little kitties to jump up and down and purr about what a great job you're doing.

Setup the Database

Since data is *usually* stored in a database, we are going to be storing our custom list order in a column in a SQL table. I'm using MySQL just because that's what I use, but as far as I know, all queries should work with any of the SQL database.

First things first, let's set up a table. Create a table named 'info' with a column named 'usort' type int and a column named 'name' type varchar(10). Here is a query for that:

CREATE TABLE `info` (
`usort` INT NOT NULL ,
`name` VARCHAR( 10 ) NOT NULL
)

And here is another query to populate it with some data:

INSERT INTO 
`blah` (`usort`, `name`) 
VALUES 
('1', 'Larry'),
('2', 'Curly'),
('3', 'Moe'),
('4', 'Mary'),
('5', 'Jane');

Note: The values in the usort column need to be unique in order for this code to work. However, DO NOT make this column primary key, auto_increment, or unique. If you do this, the query that swaps usort values will yell and scream at you about having duplicate entries.

But that's okay, because usort is not meant to replace the row id which you will probably have, and will probably already be unique, auto_incremented, etc...

The Plan

Okay now that we have our table setup with some data to work with, let's talk about the script. The goal of this example script is to display the data from the table. We will make the column names links so that we can sort by column name.

More importantly, we will create 'up' and 'down' arrows for each row, so that we can order the list how we want to. When you click an up or down arrow, the script will send a query to the database to swap numbers in the table with the row above or below the row.

Entire code that way ---------->

The Code

Here is the whole script. The next few pages will break it down bit by bit.

<?php
// connect to db
$conn = mysql_connect('localhost','dbusername','dbpassword') or die(mysql_error());
$db = mysql_select_db('dbname',$conn) or die(mysql_error());

// if an arrow link was clicked...
if ($_GET['dir'] && $_GET['id']) {
   // make GET vars easier to handle
   $dir = $_GET['dir'];
   // cast as int and couple with switch for sql injection prevention for $id
   $id = (int) $_GET['id'];
   // decide what row we're swapping based on $dir
   switch ($dir) {
      // if we're going up, swap is 1 less than id
      case 'up': 
         // make sure that there's a row above to swap
         $swap = ($id > 1)? $id-- : 1;
         break;
      // if we're going down, swap is 1 more than id
      case 'down':
         // find out what the highest row is
         $sql = "SELECT count(*) FROM info";
         $result = mysql_query($sql, $conn) or die(mysql_error());
         $r = mysql_fetch_row($result);
         $max = $r[0];
         // make sure that there's a row below to swap with
         $swap = ($id < $max)? $id++ : $max;
         break;
      // default value (sql injection prevention for $dir)
      default:
         $swap = $id;
   } // end switch $dir
   // swap the rows. Basic idea is to make $id=$swap and $swap=$id 
   $sql = "UPDATE info SET usort = CASE usort WHEN $id THEN $swap WHEN $swap THEN $id END WHERE usort IN ($id, $swap)";
   $result = mysql_query($sql, $conn) or die(mysql_error());
} // end if GET	 

// set a result order with a default (sql infection prevention for $sortby)
$sortby = ($_GET['sortby'] == 'name')? $_GET['sortby'] : 'usort';
// pull the info from the table
$sql = "SELECT usort, name FROM info ORDER BY $sortby";
$result = mysql_query($sql, $conn) or die(mysql_error());

// display table
echo "<table border = '1'>";
echo "<tr>";
// make column names links, passing sortby
echo "<td><a href='{$_SERVER['PHP_SELF']}?sortby=usort'>usort</a></td>";
echo "<td><a href='{$_SERVER['PHP_SELF']}?sortby=name'>name</a></td>";
echo "</tr>";
// display data 1 row at a time
while ($r = mysql_fetch_assoc($result)) {
   echo "<tr>";
   // make the links to change custom order, passing direction and the custom sort id
   echo "<td align = 'center'><a href='{$_SERVER['PHP_SELF']}?dir=up&id={$r['usort']}'>/\</a> ";
   echo "<a href='{$_SERVER['PHP_SELF']}?dir=down&id={$r['usort']}'>\/</a></td>";
   echo "<td>{$r['name']}</td>";
   echo "</tr>";
} // end while $r
echo "</table>";
// end display table
?>

Connect to db

<?php
// connect to db
$conn = mysql_connect('localhost','dbusername','dbpassword') or die(mysql_error());
$db = mysql_select_db('dbname',$conn) or die(mysql_error());

First thing to do is connect to the database. Nothing fancy here. Insert your own info where appropriate.

If an arrow was clicked...

// if an arrow link was clicked...
if ($_GET['dir'] && $_GET['id']) {
   // make GET vars easier to handle
   $dir = $_GET['dir'];
   // cast as int and couple with switch for sql injection prevention for $id
   $id = (int) $_GET['id'];

Okay next, we want to check if an arrow was clicked. We do this by checking for two GET variables. One variable ('dir') will tell us which direction to swap the number (up or down), and the other variable ('id') will tell us what the number is. We assign them to 'regular' variables for easier coding.

$id is cast as type int. The reason why we do this is to keep people from entering in half numbers or things other than numbers. We do this to prevent possible sql injection attacks from that variable (you always have to be security conscious).

Up or Down

switch ($dir) {
      // if we're going up, swap is 1 less than id
      case 'up': 
         // make sure that there's a row above to swap
         $swap = ($id > 1)? $id-- : 1;
         break;

$dir tells us which way we want to swap: up or down. For example, if we have a list of 1,2,3, swapping up will change the list to 2,1,3. Swapping down will change the list to 1,3,2.

List    Swapping up   Swapping down
1         2                 1
2         1                 3
3         3                 2

We will use a switch to decide what to do if $dir is up or down. if $dir == 'up' then we will use a ternary operator to make sure that the current row is greater than 1, so that there is something above the row to swap with. If there is, then we will assign the previous row to $swap by subtracting 1. If there is no row above the current row (it's already at the top of the list), we assign 1 to it.

How do we know that the row above it is 1 less than its current number? Because, all of the usort values are supposed to be unique. 5 rows == 5 numbers, 1-5. There will always be 1,2,3,4,5. Ordering by usort will always be 1-5 (or however many you have). You have to program it that way, or it won't work.

Well, I take that back. It won't work for this code example. You can get fancy and do a query to find out what the one above or below will be, regardless of whether it's exactly 1 or not, but we aren't gonna get all complicated with it.

Going Down on...the List

// if we're going down, swap is 1 more than id
      case 'down':
         // find out what the highest row is
         $sql = "SELECT count(*) FROM info";
         $result = mysql_query($sql, $conn) or die(mysql_error());
         $r = mysql_fetch_row($result);
         $max = $r[0];
         // make sure that there's a row below to swap with
         $swap = ($id < $max)? $id++ : $max;
         break;

Swapping down a row is the same principle as swapping up row, except we're like, going down, instead of up. First we do a select count(*) to find out how many rows we have. Yes this will be done every page load. Yes we can save it in a cookie or session var or pass it through the url (though I'd say no to the GET method for this var anyways, for security reasons), but in an effort to simplify, we're just going to select every time.

Grab the result, put it in a var $max. Use a ternary to check if there is a row to swap down to. If there is, then add 1 to the current row's id. If not, then assign it $max.

// default value (sql injection prevention for $dir)
      default:
         $swap = $id;
   } // end switch $dir

The default for $swap will be $id. This is for in case someone decides to enter into the url dir=somethingotherthanupordown.

2 Card Monte...

// swap the rows. Basic idea is to make $id=$swap and $swap=$id 
   $sql = "UPDATE info SET usort = CASE usort WHEN $id THEN $swap WHEN $swap THEN $id END WHERE usort IN ($id, $swap)";
   $result = mysql_query($sql, $conn) or die(mysql_error());
} // end if GET

Now that we know which two numbers we're going to swap, we will run our query to swap them in the database. Basically the query says this:

For every number in the IN(...) list, we are going to run a condition on it. If it equals one thing, we're going to assign it this other thing. If it's equal this other thing, we're going to assign it that one thing

Or a more 'php' way of saying it:

foreach ($list as $row) {
   if ($row == $x) { 
      $row = $y;
  } elseif ($row == $y) {
      $row = $x;
}

You: "Now wait just a minute there... how can the database do that? I understand doing update table set column = $x where column = $y, but you can't just turn around and do the same thing for the 2nd row, because the first row has already been updated! There's no number to search for! wtf??"

Me: MAGIC. You heard me: MAGIC. For real. Okay for really real, what happens is, internally the database will create a temporary variable to perform the execution. First row gets updated where it equals whatever. 2nd row is updated based on temporary variable. However, between the time the first row gets updated and the 2nd row gets updated, both rows will contain the same info, hence the duplicate error message that will ensue if you try to index that column as some flavor of unique.

Sorting the data

// set a result order with a default (sql infection prevention for $sortby)
$sortby = ($_GET['sortby'] == 'name')? $_GET['sortby'] : 'usort';
// pull the info from the table
$sql = "SELECT usort, name FROM info ORDER BY $sortby";
$result = mysql_query($sql, $conn) or die(mysql_error());

Pretty straight forward. We want to be able to click the column name to sort the data by that column, so assign a column name to $sortby to use in the query. Since we only have two columns to sort by, we use another ternary to assign one or the other. This ensures that $sortby will only be 'name' or 'usort' and not something else like another sql injection attempt. We then run the query to get the data to display.

Display the info

// display table
echo "<table border = '1'>";
echo "<tr>";
// make column names links, passing sortby
echo "<td><a href='{$_SERVER['PHP_SELF']}?sortby=usort'>usort</a></td>";
echo "<td><a href='{$_SERVER['PHP_SELF']}?sortby=name'>name</a></td>";
echo "</tr>";

Next we will start an html table, making the first row the column names. We make them links, passing the column name through the url, so the script knows which column to order the results by, should you click on one.

// display data 1 row at a time
while ($r = mysql_fetch_assoc($result)) {
   echo "<tr>";
   // make the links to change custom order, passing direction and the custom sort id
   echo "<td align = 'center'><a href='{$_SERVER['PHP_SELF']}?dir=up&id={$r['usort']}'>/\</a> ";
   echo "<a href='{$_SERVER['PHP_SELF']}?dir=down&id={$r['usort']}'>\/</a></td>";
   echo "<td>{$r['name']}</td>";
   echo "</tr>";
} // end while $r
echo "</table>";
// end display table
?>

Finally, we use a while loop to loop through and display the results from the data pulling query. We make some up arrow and down arrow links, passing which direction to swap, as well as the usort number for that row, so the script knows what rows to swap, should you click one of those links. The name is displayed as plain text, because we aren't doing anything with that. Close the table after the loop, and we're done.

The End

Well there you have it; a custom list order method made easy. May you find this of some use in your coding endeavors.

Crayon Violent