I’ve been working with PHP for so long I’ve forgotten what it was like when you’re just starting out. Things to me like appending strings to form complex sql queries were things that I struggled with when I first started out. The idea seemed to be very simple, but without understanding exactly what was happening, it quickly spiraled out into something that made absolutely no sense.
Here is the the problem. We have a database consisting of the following fields:
- id – a unique identifier of an entry
- name – a persons’ name
- address – their address
- city – the city they live in
- state – the state they live in
The database may contain anywhere from 5 to 5000 entries, it doesn’t matter. We still have to figure out a way to display only what the user requires. For example, if the user only wants to see people who live in a certain city, they should be able to filter by city. Perhaps they want to see only the people in a certain state, then they should be able to filter by state.
The Pseudo-code
I find this is an important part to coding a solution to our problem. Understanding exactly how you will do it is essential. We are going to only have one page and let the user select which state/city they wish to use from a simple form and then let them filter the results like that. Instead of having multiple pages we are also going to have all our code in one file. The code will look like the following
- Create a default SQL statement
- Get any filters
- Update SQL statement with filters
- Display the results
Before we get to the PHP part, we’re going to first design the HTML part of our page.
<html> <head> <title>Selecting Certain Information</title> </head> <body> <form action="<?=$_SERVER['PHP_SELF']?>" method="post"> Sort By: <select name="city"> <option value="" selected>-----</option> <option value="city1">City 1</option> <option value="city2">City 2</option> <option value="city3">City 3</option> </select> <select name="state"> <option value="" selected>-----</option> <option value="s1">State 1</option> <option value="s2">State 2</option> <option value="s3">State 3</option> </select> <button type="submit">Filter</button> </form> </body> </html>
Notice that our states and cities are present in a dropdown element. The value of each element corresponds to how it is stored in the database. For example if the state of Nebraska is stored as NE in the database, the value for Nebraska in the form should be NE.
We are assuming that you have already connected to your database somewhere near the start of your script. Step one is to create the default sql statement. After our form, we are going to add this snippet of code
<?php $sql = 'select * from table'; ?>
Now we are going to get the filters. Notice that we are using the $_POST array which is built into PHP and can access forms that are submitted using the $_POST method. Since we are trying to access the form elements named “city” and “state” those are the keys in our $_POST array. $_POST[‘city’] and $_POST[‘state’]. We are also going to go one step further and pass the values of that to two different functions. These functions will essentially make any user-input safe to use. You can read up more on addslashes() and strip_tags() in the PHP manual.
<?php $city = addslashes(strip_tags($_POST['city'])); $state = addslashes(strip_tags($_POST['state'])); ?>
Now it’s time to update our SQL statement based on the filters. First we are going to check to make sure that City and State are not empty. If they are, we don’t need to update our sql statement and we can just run it. If they are not, then we will need to deal with each one separately.
<?php if($city != '' && $state != ''){ } else if($city != ''){ } else if($state != ''){ } ?>
The reason we are doing it this way is because of how the logic behind IF statements works. It will first check both $city and $state and if they are both NOT empty, it will execute the first block of code. If either one is empty, it will skip that block and move on to the next. If $city is empty, then it will skip that block and move on to the last. If $state is empty, it won’t do anything. The reason we want to check to make sure that $state is empty instead of just assuming it is because you should never assume when it comes to your users. They will always do things that you never assumed they did. This is a failsafe to make sure that only the values you are allowing will get through.
If the user selected both a city and a state our new sql statement will look like this:
<?php $sql .= ' where city = "'.$city.'" and state = "'.$state.'"'; ?>
The .= in PHP is concatenation. Basically it is just appending the WHERE clause to our default SQL statement. This way our sql statement is now
‘select * from table where city = “’.$city.’” and state = ‘”.$state.’”’
That will select only the values from the database that match both the city and state filters.
In the second block, we are dealing with if only a city was specified.
<?php $sql .= ' where city = "'.$city.'"'; ?>
As you can see, it’s almost the same thing, just with the bit about state removed.
The last block is almost exactly the same as the one before, just instead of city, we are dealing with state
<?php $sql .= ' where state = "'.$state.'"'; ?>
And finally we run our query.
Now you can proceed to handle the results however you wish. If you already have code that was handling the results, you can keep using that as that will remain the same. The only thing that changes is the SQL statement itself, which will dictate what results SQL will take from the database and give back to us.
Here’s the complete page code
<html> <head> <title>Selecting Certain Information</title> </head> <body> <form action="<?=$_SERVER['PHP_SELF']?>" method="post"> Sort By: <select name="city"> <option value="" selected>-----</option> <option value="city1">City 1</option> <option value="city2">City 2</option> <option value="city3">City 3</option> </select> <select name="state"> <option value="" selected>-----</option> <option value="s1">State 1</option> <option value="s2">State 2</option> <option value="s3">State 3</option> </select> <button type="submit">Filter</button> </form> <?php $sql = 'select * from table'; $city = addslashes(strip_tags($_POST['city'])); $state = addslashes(strip_tags($_POST['state'])); if($city != '' && $state != ''){ $sql .= ' where city = "'.$city.'" and state = "'.$state.'"'; } else if($city != ''){ $sql .= ' where city = "'.$city.'"'; } else if($state != ''){ $sql .= ' where state = "'.$state.'"'; } $q = mysql_query($sql) or die(mysql_error()); ?> </body> </html>
If you have any questions, feel free to leave a comment or even email me.
