Write a Customisable MySQL Query

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:

Example simple MySQL database table
idno	name	frame_color	frame_comp	pic
1	Snow Bike	red	aluminum	red_snow.jpg
2	Road Bike	black	titanium	black_ti.jpg
3	Mountain Bike	blue	aluminum	mountain.jpg

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>
<li><a href="?id=2">Road Bike</a></li>
<li><a href="?id=3">Mountain Bike</a></li>
</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’]?>"
title="<?=$myrows['alt’]?>" />
<p>Material: <?=$myrows['frame_comp’]?></p>
<p>Color: <?=$myrows['frame_color’]?></p>
<? } else { ?>
<h1>No product found</h1>
<? } ?>

With the amount of in that i would almost have just echoed out the entire HTML statement. =P

posted by Ian Carson 6 months ago

oh no... it formatted my text again! (had opening and closing PHP tags)

posted by Ian Carson 6 months ago

Dudley Storey Yep, I'm afraid so, Ian... automatically escaping code in comments will have to wait for the next version of the blog, I'm afraid (coming Jan 2012, with luck). But I believe I understand what you are saying: use PHP to echo out all the HTML in the IF statement, rather than using a fragmentary approach.