PHP Freaks Tutorials

Simple SQL Search

Wed, 06 May 2009 21:50:36 +0000

Introduction
Often I see many posts about how to do a simple SQL search. Well I finally caved in and decided to write a tutorial just for that. I will try and explain everything in as much detail as I can without being over wordy. This is a basic search, nothing more. If you want a more advanced search I would suggest using Google or hiring someone to code it for you.

The aim of this tutorial is to provide users with a basic layout and the logic behind creating a multiple field search in MySQL. As often time users tend to over do the search and add a lot of unnecessary code. By following and understanding this tutorial you should be able to implement this search into your own site and provide a nice and simple SQL Search of your own database. So roll up your sleeves and be prepared to get dirty.

Setting Up
First I will assume that you have a valid installation of PHP, Apache (or IIS) and MySQL installed and already working on your system. I will also expect you to know how to create the table structure provided and add the data to it in MySQL. I will not go through the trouble of how to do that, you can find yourself a basic MySQL/PHP Tutorial and follow that.

And now for the SQL test data:
Table Structure:

Create table simple_search (
sid INT NOT NULL auto_increment,
stitle VARCHAR(50) NOT NULL,
sdescription VARCHAR(255) NOT NULL,
sbody text NOT NULL,
primary key (`sid`),
INDEX (`stitle`),
INDEX(`sdescription`)
);

Test Data:

INSERT INTO simple_search (`stitle`, `sdescription`, `sbody`) VALUES ('Test Simple Search 1', 'This is a test result for our MySQL simple search tutorial.', 'MySQL searching is a valueable item to be used in coordination with PHP. The data provided here will be easily searchable to users. Granted it will only provide the users with the basic, hence simple, search.'),
('Searching Made Easy 101', 'Searching through MySQL sets is made easy in PHP', 'PHP provides us with a means to access Relational databases with ease. Through the functions that are provided a user is able to create a form and allow their clients to browse through postings that other\'s have posted and find relevant articles to their searches. Because this is a search made easy it will not be exact or concise instead pull up a catch all to the terms searched.'),
('Gateway to Information', 'The web provides us with many tools to access loads of information that are stored in a database on our server.', 'Storing information on your server is a great way to provide clients with different information. A client can now find information they were looking for all In one place. Utilizing a scripting language often makes access to this data easier and provides the server owner with a means to backup extreme amounts of data with ease. Not to mention access that data.'),
('The Gaming World as we Know it', 'The gaming world as we know it has undergone many changes in the past. But what changes lay in the future?', 'Some say the future of gaming on computers relies in a virtual reality, but not like the 80\'s virtual reality. This new virtual reality allows the user to use their mind to control elements of a video game, vs using a controller. This new aspect of gaming will put the user right in to the game by providing a more efficient way of controlling their character. How long before this technology is readily available, no body knows. But the gaming world as we know it is changing rapidly and only time will tell when this great feature will be added to the next gaming console.'),
('Hundreds of Ants Attacking', 'Hundreds, if not thousands of Ants are starting to rage war against the human race. Can we stop it?', 'In recent news, Ants have been found to attack random people. A mom in Brazil reported that her child was just playing nicely outside when an Ant decided to go and bite her. The mom reported this incident to the police immediately, but as was told, the police were busy with nearly two dozen other Ant related reports. "I was scared for my childs life" report Janet Marget from Pawtucket, IL. "The ant just came running out of the bushes and charged me, I nearly escaped by stamping hard on the ground and crushing the ant." While some are over come by fear, others are embracing the new "regime" as it is being called. Jack Oldswell said, "I welcome the ants to take over. I mean come on, with ants out numbering humans, millions if not billions to one, they have the numbers.". This pandemic has caused quite a stir in the government agencies who have set up a task force to go and do recon on Ant colonies. Unfortunately, in this line of work, every employee has been bitten by multiple ants and this line of work is highly dangerous. As such the government raised the employees salary to three times the amount of a normal persons salary. As for this lowly reporter, I welcome the ants and offer them this statement so that they might spare me and save my life when they rule the planet.');

Database Connection
Now that we have our data entered into MySQL and made sure our PHP Server works it is time to start writing code. I would recommend a decent PHP Editor, Notepad++ for a simple project like this, or for a major project I would choose NetBeans IDE. Those are just the editors I recommend, feel free to choose whichever editor you are comfortable with. Now onto the code.

First up, we have to make a connection to the database. Since MySQLi is not on servers with a PHP version < 5 I will use MySQL to write out the database connections.

<?php
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'searchuser';
$dbPass = 'searchpass';
$dbDatabase = 'searchdb'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
?>

Remember to change the $dbUser, $dbPass, $dbDatabase and $dbHost to the values of your database. If you do not the script will error out and tell you what is wrong.

Search Form
Now we have the database connection setup we need to setup our actual form code:

<html>
<title>My Simple Search Form</title>
<style type="text/css">
#error {
color: red;
}
</style>
<body>
<?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
Search In:<br />
Body: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> /> |
Title: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['title'])?"checked":''; ?> /> |
Description: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc'])?"checked":''; ?> /><br />
Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
</body>
</html>

You will notice that this is a simple form, no formatting. The PHP is mixed in with it. If you do not know, the ? and : are ternary operators which act like a short If/else statement.

Example: (condition is true/false) (then ?) do this (else : ) do that.

If you want to read more on HTML Forms, google it. What we are doing with the form code is checking different variables. First off, we see if there was an error by counting the error array. (This part of the code comes later). If there was an error, show the error to the users so they can fix it. Next we have different variables being echo'd. The first simply points the form to the search page for submission. The next are items that were previously on the form if they contain values. Should be pretty straight forward.

Finally we check to see if the $results array has any items. If the results array does, then we will display those results to the user along with the search terms that were used to grab these results. So now you hopefully understand the form and why the code is how it is.

Search Code
Next step is the actual PHP code for the search:

<?php
// Set up our error check and result check array
$error = array();
$results = array();
// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
if (strlen($searchTerms) < 3) {
$error[] = "Search terms must be longer than 3 characters.";
}else {
$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
}
// If there are no errors, lets get the search going.
if (count($error) < 1) {
$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
// grab the search types.
$types = array();
$types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
if (count($types) < 1)
$types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.
$searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
if (mysql_num_rows($searchResult) < 1) {
$error[] = "The search term provided {$searchTerms} yielded no results.";
}else {
$results = array(); // the result array
$i = 1;
while ($row = mysql_fetch_assoc($searchResult)) {
$results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
$i++;
}
}
}
}
function removeEmpty($var) {
return (!empty($var));
}
?>

Although the code may look "complex" it really is not. First we start off by defining our arrays for errors and results. This will prevent any errors from coming of error or results not being an array, because we defined them as an array already.

Next we test to see if the form was submitted by checking to see if "search" was populated. This will prevent issues from occurring in case this is the first page load. Trimming the value of the form search will gurantee that it will not search for white spaces at the beginning or end of the terms. Stripping the tags will remove any HTML code present (you may want to omit this, it depends on what your need is). Finally we do some validation checks to make sure that some terms were actually passed through and that they contained enough characters to not return everything.

Once we are passed the validation it is time to take a precautionary measure by sanitizing our data with mysql_real_escape_string for database input to prevent SQL Injection from occurring. Next we will setup our SQL statement. We start out by defining the statement. Next we setup an array for types, this is because the search can be done on multiple fields. By setting up the search this way you just need the user to select a checkbox and those fields will be included in the search. Once we have populated the types array we want to remove any of the types that were not populated. This is being done by the function removeEmpty() which is custom defined. The removeEmpty function in use with array_filter will go through each item in the array and test if that item has a value using empty. If not it removes it from the array, which makes less code and work for us. The filtering of the array is to prevent a whitespace being used as a condition and returning everything.

Now if the types contained more than 1 item in the array, we can do our search. Imploding the array types will allow us to use our checkbox to see what the user wants matched. To do this we define a dynamic variable $andOr which changes depending on what the user selects. If that want it any one of the fields we use " OR " if they want it in all of the fields we use " AND " which ensures that we get the results the users want. Finally we run the query and grab our results, populate the results array and viola. We have our search results!

The End Result
Put the script together and it will look something like this:

<?php
/*****************************
* Simple SQL Search Tutorial by premiso
* All rights reserved to PHPFreaks.
******************************/
$dbHost = 'localhost'; // localhost will be used in most cases
// set these to your mysql database username and password.
$dbUser = 'searchuser';
$dbPass = 'searchpass';
$dbDatabase = 'searchdb'; // the database you put the table into.
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
// Set up our error check and result check array
$error = array();
$results = array();
// First check if a form was submitted.
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
if (strlen($searchTerms) < 3) {
$error[] = "Search terms must be longer than 3 characters.";
}else {
$searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
}
// If there are no errors, lets get the search going.
if (count($error) < 1) {
$searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
// grab the search types.
$types = array();
$types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
$types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
$types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
if (count($types) < 1)
$types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
$andOr = isset($_GET['matchall'])?'AND':'OR';
$searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.
$searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
if (mysql_num_rows($searchResult) < 1) {
$error[] = "The search term provided {$searchTerms} yielded no results.";
}else {
$results = array(); // the result array
$i = 1;
while ($row = mysql_fetch_assoc($searchResult)) {
$results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
$i++;
}
}
}
}
function removeEmpty($var) {
return (!empty($var));
}
?>
<html>
<title>My Simple Search Form</title>
<style type="text/css">
#error {
color: red;
}
</style>
<body>
<?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
<form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" /><br />
Search In:<br />
Body: <input type="checkbox" name="body" value="on" <?php echo isset($_GET['body'])?"checked":''; ?> /> |
Title: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['title'])?"checked":''; ?> /> |
Description: <input type="checkbox" name="desc" value="on" <?php echo isset($_GET['desc'])?"checked":''; ?> /><br />
Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />
<input type="submit" name="submit" value="Search!" />
</form>
<?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
</body>
</html>

And now we run the script and see the search results work their magic! Enjoy.

PHP Add Text To Image

Mon, 26 Jan 2009 05:32:55 +0000

Dynamic Image Generation

Long and boring back-story....

One of the standard features of a message board is allowing members to have a signature, which is appended to the bottom of each post they make. Posters can put whatever they want into the signature (within forum settings). Putting quotes in one's signature is one of the more popular things to do.

A lot of people put in some motto they live by, some funny quote by some famous person, whatever. Some people like to quote things other posters on the board have said. I am one of those people. I think it's fun and promotes a sense of belonging to the community.

I've been a member of the phpfreaks forum for a number of years now, and over the years, I have posted and come across quotes that were (to me, anyways) worth collecting. But having a bunch of quotes in a sig isn't very convenient.

So one day I got the bright idea to pick a random quote from my list and just display that. Well most message boards (or websites in general, for that matter) will not let you just post script and parse it willy nilly for you. Something to do with security or whatever blahblahblah translation: sites are uptight.

But what most sites will let you do, is post images. One of the great things about php is that it comes with something called the GD library that lets you manipulate images in all sorts of ways. And it will even let you output it as an image to a browser, so you can load a php file as if it were an actual image.

Problem solved! I decided to put the quotes I've collected into a database and write a script that picks a random one and creates an image out of it, tell the browser the content is an image and output the image, and blam! I get to post a random quote in my sig!

I weep for the future....

Soon afterwards, I started receiving messages on a fairly regular basis asking me "What kind of sorcery is this?? How do I do that??" You know, I think it's kind of funny people should ask, because this is really nothing new. Remember back in the day when people made those stupid sig images that show "personal" info?

************************************
*     Your IP is xxx.xxx.xx.xx     *
*       Your Host is blahblah      *
*        Your OS is windoze        *
* Your "Shoe Size" is 4 1/2 inches *
*      You "Bat" left handed       *
************************************

On top of that, this is pretty much the same principle as captcha verification on forms, which has been around for years now, and surely you all know what that is, right? And yet, here I am, writing a tutorial about it, because people are asking, so whatever.

This tutorial will not detail how to put quotes (or text in general) into a database or retrieve a random one. You can learn all about basic database interaction in my basic database interaction tutorial. Instead, I'm just going to hard-code a couple of random quotes into an array and pick a random one.

Also, this tutorial is not going to show you how to do anything fancy, like wavy text, frilly borders, or get into distorting methods used in captcha images, etc...; we're going to make a gray background so you can see the image boundaries, and plain old run of the mill white text (to contrast the gray background). I'm here to show you how to start. Where you go from there is up to you.

Code is that way ---------------->

Payment, Up Front

Here is the complete script. Copy and paste it. Run it. Be amazed. My next trick will be to pull a quarter out of your ear.

<?php
// Path to our font file
$font = 'arial.ttf';
$fontsize = 10;
// array of random quotes
$quotes = array(
"Did you hear about the guy whose whole left side was cut off? He's all right now.",
"There was a sign on the lawn at a drug re-hab center that said 'Keep off the Grass'.",
"Police were called to a daycare where a three-year-old was resisting a rest.",
"A hole has been found in the nudist camp wall. The police are looking into it.",
"When a clock is hungry it goes back four seconds.",
"Time flies like an arrow. Fruit flies like a banana.",
"Local Area Network in Australia: the LAN down under.",
"Alcohol and calculus don't mix so don't drink and derive.");
// generate a random number with range of # of array elements
$pos = rand(0,count($quotes)-1);
// get the quote and word wrap it
$quote = wordwrap($quotes[$pos],20);
// create a bounding box for the text
$dims = imagettfbbox($fontsize, 0, $font, $quote);
// make some easy to handle dimension vars from the results of imagettfbbox
// since positions aren't measures in 1 to whatever, we need to
// do some math to find out the actual width and height
$width = $dims[4] - $dims[6]; // upper-right x minus upper-left x
$height = $dims[3] - $dims[5]; // lower-right y minus upper-right y
// Create image
$image = imagecreatetruecolor($width,$height);
// pick color for the background
$bgcolor = imagecolorallocate($image, 100, 100, 100);
// pick color for the text
$fontcolor = imagecolorallocate($image, 255, 255, 255);
// fill in the background with the background color
imagefilledrectangle($image, 0, 0, $width, $height, $bgcolor);
// x,y coords for imagettftext defines the baseline of the text: the lower-left corner
// so the x coord can stay as 0 but you have to add the font size to the y to simulate
// top left boundary so we can write the text within the boundary of the image
$x = 0;
$y = $fontsize;
imagettftext($image, $fontsize, 0, $x, $y, $fontcolor, $font, $quote);
// tell the browser that the content is an image
header('Content-type: image/png');
// output image to the browser
imagepng($image);
// delete the image resource
imagedestroy($image);
?>

And to apply it, you would use a standard img tag (or img bbcode on a forum), pointing to the file as the source, like so:

<img src='http://www.yoursite.com/yourscript.php' />

Hey...you said size doesn't matter!

// Path to our font file
$font = 'arial.ttf';
$fontsize = 10;

First we will decide what font to use and the font size. How you do this will vary from setup to setup. You may have to include a path/to/fontfile for fonts your server may or may not have installed (most hosts have a bunch of fonts pre-installed in some path or other). It's up to you to figure out where that file is, and if the font you use is installed.

If you want to skip all that for now, the easiest hassle free thing to do is to upload your font into the same directory as your script, and the script will start by looking for it there. You will have to do that for any font your host doesn't have, anyways, so may as well cut to the chase.

I chose plain old arial font, and a font size of 10, measured in inches..er...pixels.

..And the next randumb number is...

// array of random quotes
$quotes = array(
"Did you hear about the guy whose whole left side was cut off? He's all right now.",
"There was a sign on the lawn at a drug re-hab center that said 'Keep off the Grass'.",
"Police were called to a daycare where a three-year-old was resisting a rest.",
"A hole has been found in the nudist camp wall. The police are looking into it.",
"When a clock is hungry it goes back four seconds.",
"Time flies like an arrow. Fruit flies like a banana.",
"Local Area Network in Australia: the LAN down under.",
"Alcohol and calculus don't mix so don't drink and derive.");

$quotes is our array of random quotes. Each position of the array is a string value containing a quote that has personal significance to me. Okay not really. But I love puns. Who doesn't?

// generate a random number with range of # of array elements
$pos = rand(0,count($quotes)-1);

We need to pick a random quote from our list of quotes. We can do this by finding out how many elements there are in the $quotes array, and pick a random number between 0 and that number - 1. By default, array positions start at 0, so if there are (in this case) eight quotes, the array keys will be numbered from 0 to 7.

Since count($quotes) returns 8, since there are 8 quotes, we need to subtract 1 from it, so that we pick a random number between 0 and 7.

// get the quote and word wrap it
$quote = wordwrap($quotes[$pos],20);

We then take that number and assign $quotes[$pos] to $quote. So for instance, if $pos was assigned 5, $quote would be assigned:

"Time flies like an arrow. Fruit flies like a banana."

So what's that wordwrap thing? wordwrap() is not strictly necessary for this script. You can remove it and the text will all be on one line. The reason I included it here is because I know that someone will eventually ask how to break up the text so it doesn't make one long image, so I'm making a preemptive strike. I learned it by watching my president.

wordwrap() accepts several arguments. Only the first two are required, and conveniently, that's all we need in this situation, so that's all that's used. The first argument is the string we want to break up, and the 2nd argument is how many characters we want to have on the line before we make a new line.

wordwrap() by default will not make a new line in the middle of a word. If the character length reaches the cutoff and we're in the middle of the word, the entire word is put to the next line. You can force wordwrap() to cut it off at exactly the cutoff point no matter what, by setting the optional 4th argument to true.

Also, by default, wordwrap() "breaks up" the string into new lines by inserting a '\n' after the cutoff point. You can specify what is actually inserted at those points in the optional 3rd argument. There are all kinds of crazy things you can use wordwrap() for that involve taking advantage of being able to specify what is inserted at the cut-off points, but that's a whole other topic of discussion.

One size fits all...?

// create our bounding box for the first text
$dims = imagettfbbox($fontsize, 0, $font, $quote);

In order to create an image, we need to create an image resource. In order to do that, we need to tell php what size we want the image to be. For the purposes of this tutorial, we want the image to be the same size as the text block we just created. The GD Library provides a convenient way to figure out what the "borders" of our text block is: imagettfbbox().

imagettfbbox() calculates the boundaries of the text by making a bunch of weird and archaic calculations based off of things like the text's font size, the angle of the text (like, if we were to want the text to slant at some slope, instead of horizontal, as text is normally read), the font itself, and the text in question. I think it just kind of eyeballs it, but it seems to work, so I'm not complaining.

// make some easy to handle dimension vars from the results of imagettfbbox
// since positions aren't measures in 1 to whatever, we need to
// do some math to find out the actual width and height
$width = $dims[4] - $dims[6]; // upper-right x minus upper-left x
$height = $dims[3] - $dims[5]; // lower-right y minus upper-right y

Unfortunately, imagettfbbox() doesn't just tell you "Hey, this is the width and this is the height," so we need to figure that out on our own. imagettfbbox() returns an array of different numbers. 8 numbers, to be exact. Each one represents an x,y coordinate for the corners of the text 'boundary'. You can refer to the manual for which array elements refer to which coords. I commented the ones I used, here.

Ideally, all we should need is the bottom left corner, but for some reason the numbers don't quite work out that way. Lower boundaries come back as 0's or -1's, upper boundaries come back a few pixels short; who knows why. Who cares (I'm sure someone does, and I'm sure they'll eventually post a comment about it, and to that person, I say thanks for clearing that up! <coughnerdcoughcough>).

Fortunately, we can nonetheless work with those numbers. We just need to subtract a left x from a right x, and a top y from a bottom y. We'll assign the results in easy to use variables $width and $height, and we're good to go.

A picture is worth a thousand words...

// Create image
$image = imagecreatetruecolor($width,$height);

Okay now we need to create an image resource. It's basically like a canvas: something to paint the picture on, so to speak. Think of it as opening up your favorite paint program, telling it that you want to create a new picture of x,y size. You start it up, paint little stick figures, and eventually save it to a file. Well, the image resource is basically what your masterpiece is, before you save it; the thing in memory the computer messes around with every time you make a change to the picture.

imagecreatetruecolor basically tells php that we want to work with a canvas x width and y height big. We use the width and height of our text boundary we figured out earlier.

// pick color for the background
$bgcolor = imagecolorallocate($image, 100, 100, 100);
// pick color for the text
$fontcolor = imagecolorallocate($image, 255, 255, 255);

The next thing we want to do is select what colors we want to use. It's like when you are in your paint program, and you choose the color you want to use. You aren't actually using it yet, you're just selecting it. That's what we are doing here, except we are storing our choices in variables. We want one color for our background, and one for our font color.

The first argument for imagecolorallocate is the image resource we just created. The next 3 arguments is the red, green, and blue (rgb) values of the color, specified as a 0 to 255 integer (for each one). 0,0,0 means no color intensity, which is a fancy way of saying black. 255,255,255 is full color intensity, or white. 100,100,100 is some random intensity level I chose; an even mix of all 3 colors (or gray).

Hold that pose!

// fill in the background with the background color
imagefilledrectangle($image, 0, 0, $width, $height, $bgcolor);

Now we get down to business and start painting the picture, so to speak. First, we set the background color of the image. We do this by making a filled rectangle, because our image is a rectangle, and making a filled rectangle the same size of our rectangle is pretty convenient for our purposes.

imagefilledrectangle() takes 6 arguments: the image resource, the top left x,y coordinates, the bottom right x,y coordinates, and the fill color. We fill in the blanks with the appropriate variables, and move along.

// x,y coords for imagettftext defines the baseline of the text: the lower-left corner
// so the x coord can stay as 0 but you have to add the font size to the y to simulate
// top left boundary so we can write the text within the boundary of the image
$x = 0;
$y = $fontsize;
imagettftext($image, $fontsize, 0, $x, $y, $fontcolor, $font, $quote);

The next thing we do is write the text to the canvas. We do this with imagettftext(). This GD function needs 8 arguments:

1 -The image resource
2 - The font size of the text
3 - The angle we want the text to be written (like, if we want it to slant some way, instead of the normal horizontal. Same as with the imagettfbbox() earlier)
4,5 - The x,y coordinate for the base point of the text. This coordinate specifies the lower left corner of the first character in the text.
6 - the color of the font
7 - the font type
8 - the text to 'paint'

Since the 4th and 5th arguments of imagettftext() specify the lower left point of the text, we need to offset the starting point, so that the text is written within the boundary of our canvas. The x coordinate doesn't need to be offset, so that stays at 0, but the y coordinate needs to be offset by the size of the font.

Signed, sealed and delivered

// tell the browser that the content is an image
header('Content-type: image/png');
// output image to the browser
imagepng($image);
// delete the image resource
imagedestroy($image);

Okay, our image is now created! Now all we have to do is output the results. First, we send a header to the browser, telling it that the content is going to be an image. I chose png image type for the hell of it. You can specify it as a gif or bmp or jpg or whatever; the GD library supports outputting to different filetypes. You'll have to change the content-type in the header call, as well as the appropriate GD function (like imagegif, imagejpeg, etc..).

Then we actually output the image with imagepng(). The only required argument is the image resource. If that's the only argument we specify, it outputs it as a raw image to our browser (which is why we sent the header to the browser). The other arguments can be used for saving the file, the quality, etc.. You can refer to the manual entry for what the other arguments are for and how to use them.

Last thing we do is delete the image resource with imagedestroy(), so that it's not hanging around in the computer's memory anymore. This isn't strictly necessary, as everything is destroyed, memory freed, etc.. blahblahblah when the script is done executing, but it's good programming practice to clean up after yourself. After all, you do wipe your arse when you're done going #2, don't you? Don't you???

The End!

All you have to do from there is treat the file as if it were an image, in an img tag or in an img bbcode on your forum; whatever.

Hopefully this tutorial was easy enough to understand, and you can use it as a stepping stone to do whatever with it. If you are interested in how to make a captcha image, there are a million and one tutorials out there for that, so you should have no problem finding something. I doubt I will be writing one, especially since, as mentioned, the bulk of it is pretty much the same as what was shown here.

On the other hand, there is a lot to explore and discuss about the specifics of captcha, so who knows, maybe I will. Until then,

Happy Coding!

Crayon Violent

Working with checkboxes and a database

Sat, 10 Jan 2009 17:47:52 +0000

Introduction

Over in the php help forum, the question of how to deal with checkboxes arises pretty often. Usually, people want to be able to select which rows of a database to update/delete/throw out the window.

The concept is actually pretty simple. It involves naming your checkbox as an array and the use of the IN mysql clause. This tutorial aims to give a simple example in the hope that, next time someone asks, I can point them to a tutorial rather than explaining all over again.

Right, on with the show.

The Database

Seeing as we're going to be updating a database, we'll be needing a table and some data. Here's a table I made earlier:

CREATE TABLE `tutorial_users` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 50 ) NOT NULL ,
`admin` TINYINT( 1 ) NOT NULL DEFAULT '0',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

And some data to work with. No prizes for guessing what I was just watching on TV:

INSERT INTO `tutorial_users` (
`id` ,
`username` ,
`admin`
)
VALUES (
NULL , 'Stewie', '0'
), (
NULL , 'Peter', '0'
), (
NULL , 'Brian', '0'
), (
NULL , 'Meg', '0'
), (
NULL , 'Lois', '0'
), (
NULL , 'Chris', '0'
), (
NULL , 'Greased Up Deaf Guy', '0'
), (
NULL , 'Quagmire', '0'
);

It's a simple setup. A user's table with a field called admin, which is the field we'll be updating.

If you're the kind of guy (or gal; we're all for equal opportunities here) who likes a quick fix – the full code is on the next page. Otherwise, we'll be breaking it down on pages 4 and 5.

The Code

<?php
include("connect.php");
$updated = FALSE;
if(count($_POST) > 0){
$admin = $_POST['admin'];
array_map('intval',$admin);
$admin = implode(',',$admin);
mysql_query("UPDATE tutorial_users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
mysql_query("UPDATE tutorial_users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
$updated=TRUE;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>phpfreaks checkbox tutorial</title>
</head>
<body>
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<?php
if($updated===TRUE){
echo '<div>Privileges Updated!</div>';
}
?>
<table>
<tr>
<th>Username</th>
<th>Admin Privileges</th>
</tr>
<?php
$sql = "SELECT id,username,admin FROM tutorial_users ORDER by id ASC";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
while(list($id,$username,$admin)=mysql_fetch_row($result)){
$checked = ($admin==1) ? 'checked="checked"' : '';
echo '<tr><td>'.$username.'</td><td><input type="checkbox" name="admin[]" value="'.$id.'" '.$checked.'/></td></tr>'."\n";
}
?>
<tr><td colspan="2"><input type="submit" name="submit" value="Update Privileges" /></td></tr>
</table>
</form>
</body>
</html>

Interlude

Thanks for sticking around. Before we take a closer look at that code, I just want to mention a little about checkboxes and how they work.

You're probably aware that with a lot of other form elements, such as text fields, if you leave them blank they'll still appear in the $_POST/$_GET array – just with a blank value.

On the other hand, checkboxes behave a bit more like buttons. If you don't check them, they wont appear in the array. Assuming you do tick them, they'll take the value you give them or 'on' by default. Bear that in mind, it'll be important later.

Breaking it down

I'm going to start the breakdown a little way through the code from the opening form tag, seeing as you'll have to fill in that form before you can process it.

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<?php
if($updated===TRUE){
echo '<div>Privileges Updated!</div>';
}

We have a standard form with a post method, and an action of the same page. We're also going to display a message if the database was updated – because we're nice like that.

<table>
<tr>
<th>Username</th>
<th>Admin Privileges</th>
</tr>

We're going to setup a table with the username and privilege listed in it. If you don't like tables, tough luck.

$sql = "SELECT id,username,admin FROM tutorial_users ORDER by id ASC";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

We select the fields we need from the database (not forgetting the id, as we'll use this to identify each row) and execute the query with a little error checking.

while(list($id,$username,$admin)=mysql_fetch_row($result)){

If you're not familiar with the list() construct, it allows us to assign lots of variables from a numerically indexed array in one go. I get bored of using $row all the time, so it makes a change. It's important to use mysql_fetch_row() so that you get a numerically indexed array (as opposed to an associative one) and that you create the variables with the same order you selected them in your query. See the manual page for a bit more.

$checked = ($admin==1) ? 'checked="checked"' : '';
echo '<tr><td>'.$username.'</td><td><input type="checkbox" name="admin[]" value="'.$id.'" '.$checked.'/></td></tr>'."\n";

Inside that loop, we first work out if the checkbox should be ticked to start with. That is, if the user currently has admin privileges. Use of the ternary operator keeps this short and sweet. We then echo out a row for each user.

Notice that we name the checkbox admin[]. This means that $_POST['admin'] is going to be an array. An array of all the ticked boxes in fact. Also notice that we set the value to the id of the user.

<tr><td colspan="2"><input type="submit" name="submit" value="Update Privileges" /></td></tr>
</table>
</form>
</body>
</html>

Finally, we make a submit button and finish off our html. I don't know about you, but I find forms without a submit button a little unsatisfactory.

On the next page, we'll take a look at the processing.

Processing the form

include("connect.php");
$updated = FALSE;

First, we include a file that's going to connect us to our database. I would show you that file, but personally I prefer to keep my username and password to myself. We also create a variable that says we haven't updated the database...yet.

if(count($_POST) > 0){
$admin = $_POST['admin'];
array_map('intval',$admin);

Our if statement ensures that this code only runs if the form has been submitted. We create a local variable from the $_POST array. We then do a little bit of validation. We don't want any nasty users modifying any of those values in our form to do some SQL injection, so we make sure every value in that array is an integer with the array_map function. Again, see the manual page if you're not too sure how this function works.

"What if I don't have integers as my values?" Good question. You might like to use mysql_real_escape_string(), like so:

array_map('mysql_real_escape_string',$admin);

Or you might write your own function to make sure the values are the sort of thing you expect.

$admin = implode(',',$admin);

Next, we use the implode() function to turn our array into a string. We separate each value with a comma. This will allow us to use the IN clause in a moment. If your values aren't integers, you'll have to do something different. I'll cover that in a minute.

mysql_query("UPDATE users SET admin=0") or trigger_error(mysql_error(),E_USER_ERROR);
mysql_query("UPDATE users SET admin=1 WHERE id IN ($admin)") or trigger_error(mysql_error(),E_USER_ERROR);
$updated=TRUE;

Before we update the new users who have admin privileges, we first update the database so no-one does. Why? Well, our comma-delimited list contains just those people who we do want to have admin rights. So we better make sure no-one else does. Our second query makes use of that IN clause – it allows us to provide a comma-separate list of values that id can be.

Note: An alternative to updating every row to 0 would be to use the NOT IN clause.

Using strings

If your values are strings, you'll need to be a little more careful with your implode and query. Don't forget that all strings in your SQL must be inside quotes. It's a pretty simple adjustment, but it'll cause you an error if you forget. Make your implode look like this:

$admin = implode("','",$admin);

And your query like this:

mysql_query("UPDATE users SET admin=1 WHERE id IN ('$admin')") or trigger_error(mysql_error(),E_USER_ERROR);

Note the extra quotes – after we've imploded our values, they might look a little like this:

one','two','three','four

So we need to add the quote to the front and back.

We'll sum up on the last page

Summary

So there we have it. The form gets processed and reloaded with the users having the new privileges. I hope that was easy to follow and worth the effort. Next time you work with checkboxes, it should be a little easier – there's really not much more to it.

Remember to name your checkbox as an array and set its value to something that uniquely identifies the row. If that's a string, take care when you implode the data. And don't forget to validate those values too. Most users are evil. Alright, maybe not. But if you think like that, you'll save yourself more than a few headaches.

Lastly, don't forget, you can name other pieces of your form so that they are arrays. Again, this is useful for updating an unknown, large amount of data simultaneously.

Protecting php applications with PHPIDS

Sun, 21 Dec 2008 00:33:12 +0000

Introduction

PHPIDS (PHP-Intrusion Detection System) is a simple to use,
well structured, fast and state-of-the-art security layer
for your PHP based web application. The IDS neither strips,
sanitizes nor filters any malicious input, it simply
recognizes when an attacker tries to break your site and
reacts in exactly the way you want it to. Based on a set of
approved and heavily tested filter rules any attack is given
a numerical impact rating which makes it easy to decide what
kind of action should follow the hacking attempt. This could
range from simple logging to sending out an emergency mail
to the development team, displaying a warning message for
the attacker or even ending the user's session

In a nutshell PHPIDS is an advanced intrusion detection system written with performance on a large scale in mind. The basic installation and configuration is pretty straight forward.

Requirements

PHP 5.1.2 or better
Apache
mod_rewrite

Installation

First we need to download the latest stable release at http://php-ids.org/downloads/ and decompress it. Please note that you do not want public access to the phpids directory. I recommend that you place it above your document root.

If you are on a shared host and cannot place it above the document root the following rewrite will prevent unwanted access.

RewriteEngine On
RewriteCond %{REQUEST_URI} ^/phpids(.*)
RewriteRule ^(.+)$ - [F]

Configuration

The basic configuration is extremely simple. By default it comes with several examples. I recommend that you take the time to look at the original Config.ini and browse through the included documentation. It will work "out of the box" with very few edits to the Config.ini. My Config.ini looks like:

[General]
    filter_type = xml
    use_base_path = false
    filter_path = default_filter.xml
    tmp_path  = tmp
    scan_keys  = false
    HTML_Purifier_Path = IDS/vendors/htmlpurifier/HTMLPurifier.auto.php
    HTML_Purifier_Cache = IDS/vendors/htmlpurifier/HTMLPurifier/DefinitionCache/Serializer
    html[] = __wysiwyg
    json[]  = __jsondata
    exceptions[]  = __utmz
    exceptions[] = __utmc
    min_php_version = 5.1.2
[Logging]
    path = tmp/phpids_log.txt
    recipients[] = me@domain.com
    subject = "PHPIDS detected an intrusion attempt!"
    header = "From: <PHPIDS> noreply@domain.com"
    envelope = ""
    safemode = true
    allowed_rate = 15

[Caching]
    caching = file
    expiration_time = 600
    path = tmp/default_filter.cache

Now, we need to write a simple php script to enable PHPIDS. I used one of the included examples with minor modifications

ids.php

<?php
// set the include path properly for PHPIDS
set_include_path(
get_include_path()
. PATH_SEPARATOR
. 'phpids/lib/'
);
if (!session_id()) {
session_start();
}
require_once 'IDS/Init.php';
try {
$request = array(
'REQUEST' => $_REQUEST,
'GET' => $_GET,
'POST' => $_POST,
'COOKIE' => $_COOKIE
);
$init = IDS_Init::init(dirname(__FILE__) . '/phpids/lib/IDS/Config/Config.ini');
$init->config['General']['base_path'] = dirname(__FILE__) . '/phpids/lib/IDS/';
$init->config['General']['use_base_path'] = true;
$init->config['Caching']['caching'] = 'file';
$ids = new IDS_Monitor($request, $init);
$result = $ids->run();
if (!$result->isEmpty()) {
require_once 'IDS/Log/File.php';
require_once 'IDS/Log/Email.php';
require_once 'IDS/Log/Composite.php';
$compositeLog = new IDS_Log_Composite();
$compositeLog->addLogger(IDS_Log_Email::getInstance($init),IDS_Log_File::getInstance($init));
$compositeLog->execute($result);
}
} catch (Exception $e) {
//this shouldn't happen and if it does you don't want the notification public.
}
?>

Now we will use PHP's auto_prepend_file to prepend our ids.php script from above to all other php scripts. You can do this by adding the following to your php.ini

auto_prepend_file /full/path/to/ids.php

Or with a .htaccess we can do something like:

php_value auto_prepend_file /full/path/to/ids.php

PHP Custom List Order

Wed, 03 Dec 2008 23:40:28 +0000

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