Subscribe to PHP Freaks RSS

PHP Custom List Order

Print
by Crayon Violent on Dec 3, 2008 6:40:28 PM - 154,810 views

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

Comments

Thomas Johnson Dec 4, 2008 9:46:21 AM

Very nice tutorial CV!.

Gareth Evans Dec 7, 2008 8:09:46 PM

Nice Tutorial, I'd like to see how this could be adapted to work with a system where rows can also be deleted. So;
1, 2, 3, 4, 5 could change to 1, 2, 3, 5, 4

then something could be removed 1, 3, 5, 4

then it could re-order without first doing this 1, 2, 5, 4

Crayon Violent Dec 7, 2008 10:48:05 PM

Okay, here you go. Add this code somewhere before the codeblock that selects the data to be displayed:

// delete from table
if ($_GET['del'] == 'true') {
   // cast id as int for security
   $id = (int) $_GET['id'];
   // delete row from table
   $sql = "DELETE FROM info WHERE usort = '$id'";
   $result = mysql_query($sql, $conn) or die(mysql_error());
   // select the info, ordering by usort
   $sql = "SELECT usort, name FROM info ORDER BY usort";
   $result = mysql_query($sql, $conn) or die(mysql_error());
   // initialize a counter for rewriting usort
   $usort = 1;
   // while there is info to be fetched...
   while ($r = mysql_fetch_assoc($result)) {
      $name = $r['name'];
      // update the usort number to the one in the next number
      $sql = "UPDATE info SET usort = '$usort' WHERE name = '$name'";
      $update = mysql_query($sql, $conn) or die(mysql_error());
      // inc to next avail number
      $usort++;
   } // end while
} // end if del

and then inside your while loop that lists the data, add another table cell next to the name or wherever:

echo    "<td><a href='{$_SERVER['PHP_SELF']}?del=true&id={$r['usort']}'>delete</a></td>";

Basically what that does is first check to see if the delete link was clicked. If so, it takes the id and deletes the row that has the id.

Then, all the rows and columns are selected. Okay here's the thing about that: You need another unique column in order to do this. In that code ^ I use the name column because it's the only other column there is in this code, and the example data happens to be unique. Ideally, you would use a real unique column like the primary key or account_id type column or whatever.

Basically the idea is to then loop through each row and use a regular + 1 inc'ed var and update the usort column where name = current name.... and that's why you really need to use your primary key column instead of some column that can have duplicate data.

Again, this code example only has 2 columns to work with, and they both happen to be unique. Ideally, when you're getting into adding and deleting rows, you're going to have a separate column containing a unique id, as primary key, auto_ince'd or whatever. So in your real code, you would be passing that id and basing your delete off that, not the usort column. And ideally, you would be looping through the data and basing the update off that same unique id column in the where clause.

Gareth Evans Dec 8, 2008 7:34:55 PM

That's fantastic thank you. Will be implementing something similar in my CMS

alapimba Jan 7, 2009 6:23:16 AM

Hi, great tutorial that you wrote.
I have a few questions.
How can i add another line to the table? Can you explain how to count the number of rows so the new one will be like total + 1?
In case of use a unique id as you mentioned how should it look on your code? What the variable $r['name'] means?
Thanks a lot for your work.

Crayon Violent Jan 7, 2009 9:12:57 AM

alapimba,

Since the numbers are supposed to be unique increments of 1 to x, there are several ways you can find out the highest number. For instance,

You can...

- SELECT usort FROM info ORDER BY usort DESC LIMIT 1
- SELECT MAX(usort) FROM info
- SELECT count(*) FROM info

Add 1 to the result and you're good to go.

re: $r['name']

$r is an associative array that holds the current row fetched from the result source. 'name' is the name of the element for $r. You can read my basic ]database handling tutorial for more info, particularly page 11

alapimba Jan 9, 2009 1:59:32 PM

Crayon just finished my backend thanks for your tutorial!

Thanks a lot to make this tutorial.

MadTechie Jan 26, 2009 9:02:28 PM

Great Script CV, I have added it to one of my projects (had to make a few tweaks, but thats to be expected..) But it was better than the solution i had

Great Job *5xThumbs Up*

mozyatdisco Jul 14, 2009 12:02:08 PM

When you delete an entry, the script goes almost haywire. Any idea on how to handle that?

As it is, it was of great help. Thx.

cheekylele Aug 26, 2009 1:02:52 AM

hey there great script but i keep getting a MySQL error saying :
Undefined index: dir
Undefined index: sortby

i think its in relation to the GET method because its trying to get a value that doesnt actually exist in the db

// make GET vars easier to handle
   $dir = $_GET['dir'];

.... any advice on how to eliminate the error would be awesome - thanks! :-)

Crayon Violent Aug 26, 2009 7:38:12 PM

If I had to take a guess you forgot to add $ to the front of your variable names in your query string. Or else, you forgot to change the url values to your actual column names. But those are just guesses seeing as how you didn't post your actual code.

If you require further assistance, please post your problem on the forums. You'll get a lot more people looking at it and therefore a faster answer.

krystof78 Oct 26, 2009 6:49:42 AM

Hi CV,
Great tutorial that you have here.
I am in a situation where it works perfectly till the point where I sort my data by a variable. Let's say that I have in my table these fields:
- id
- name
- usort
- category
I have to display the information by category. But then, when I use your script, it does not work properly as it changes the position in the table by +/-1, not with the row I want it to be changed with. Ultimately it will work when it goes up/down the appropriate row.
I guess there should be a way of fixing that by swapping the usort number, but I have to admit that my skills do not allow me to do that.
Am I clear on my problem? Do you have any idea about how to fix that?

Add Comment

Login or register to post a comment.