The easiest way to alter a MySQL query based on user choices is to place that choice in the form a URL variable, otherwise known as a $_GET variable. For example, let’s say we wanted to give our site visitor the choice of seeing the details for products we have listed in a database table called bikes:

If we wanted just the snow bike row as a result, our MySQL query would be:

SELECT * FROM bike WHERE idno = 1;

But we want to make it such that the id value that we use is a variable. Keeping things simple, we’ll hard-code the links for the user into the PHP page:

<ul id="productlist">
	<li>
		<a href="?id=1">Snow Bike</a>
	<li>
		<a href="?id=2">Road Bike</a>
	<li>
		<a href="?id=3">Mountain Bike</a>
</ul>

We don’t need a page name before the question mark delimiting our GET variable as we’ll be looping back on the same page. At the top of our page, we want to connect to our database. We’ll use the same lines of PHP code we did previously:

<? $myDatabase = mysql_connect("mysql_server", "user", "password") or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db("database", $myDatabase); ?>

The change happens in the very next few lines:

<? if (isset($_GET['id’])) { 
		$id = mysql_real_escape_string($_GET['id’]); 
	} else { 
		$id = 1;
	} ?>

We’re using mysql_real_escape_string to ensure that what we get from the URL is just a number, and not anything that will corrupt our database when we use it in a query. Anytime you are building a query based on user input, it is vital to check and double-check the variables you are using for security. (If we were using a later version of PHP we might use FILTER_SANITIZE_NUMBER_INT instead.) If $_GET['id'] isn’t set – i.e. the user has come to the page for the very first time – $id is set to 1.

Now that we have a number, we can create our query:

<? $query = "SELECT * FROM bikes WHERE idno = $id LIMIT 1";
$mydata = mysql_query($query, $myDatabase) or die(mysql_error());
$myrows = mysql_fetch_assoc($mydata); ?>

Assured that we are getting only one row returned from the database, we can echo it on our page. First, we’ll check that we have any response, then provide output:

<? if ($myrows) { ?>
	<h1><?=$myrows['name’]?></h1>
	<img src="assets/images/<?=$myrows['pic']?>" alt="<?=$myrows['name']?>">
	<p>Material: <?=$myrows['frame_comp’]?>
	<p>Color: <?=$myrows['frame_color’]?>
<? } else { ?>
	<h1>No product found</h1>
<? } ?>

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.