Using multiple PHP drop downs to filter MySQL table data

Question

I am currently building a page that uses five separate drop down menus (static content) that when selected the combination of what is selected will filter my mysql query and display the correct results using those parameters.

I am a newbie in php and i don't know how to go about it

Here is the html code for index.php

<form action="search.php" method="post">
<p>Body Type</p>
                <select class="form-control" name="bodytype">
                    <option value="" selected="selected">Any body type</option>
                     <option value="saloon">Saloons</option>
                    <option value="hatchback">Hatchbacks</option>
                    <option value="4 wheel drive">4 wheel drives</option>
                <option value="station wagon">Station wagon</option>
                <option value="pickup">Pickups</option>
                    <option value="motor bike">Motor bikes</option>
                <option value="convertible">Convertibles</option>
                <option value="bus">Buses, Danfos & Vans</option>
                <option value="truck">Trucks</option>
                </select>


<p>Condition</p>
                <select class="form-control" name="condition">
                    <option value="" selected="selected">Any condition</option>
                     <option value="brand new">Brand new</option>
                    <option value="foreign used">Foreign used (Tokunbo)</option>
                    <option value="nigerian used">Nigerian used (Registered)</option>
                 </select>



<p>Fuel type</p>
                <select class="form-control" name="fueltype">
                    <option value="" selected="selected">Any fuel type</option>
                     <option value="petrol">Petrol</option>
                    <option value="diesel">Diesel</option>
                    <option value="hybrid">Hybrid</option>   
                 </select>


<p>Transmission</p>
                <select class="form-control" name="transmission">
                    <option value="" selected="selected">Any transmission</option>
                     <option value="automatic">Automatic</option>
                    <option value="manual">Manual</option>
                 </select>


<p>Drive type</p>
                <select class="form-control" name="drivetype">
                    <option value="" selected="selected">Any drive type</option>
                     <option value="2 wheel drive">2 wheel drive</option>
                    <option value="4 wheel drive">4 wheel drive</option>
                 </select>

<input name="search" type="submit" value="Search">
</form>`

This is the search.php

<body>

<?php
$selected_btype = $_POST['bodytype'];  // Storing Selected Value In Variable
$selected_condition = $_POST['condition'];
$selected_fueltype = $_POST['fueltype'];
$selected_makemodel = $_POST['makemodel'];
$selected_transmission = $_POST['transmission'];
$selected_location = $_POST['location'];
$selected_dtype = $_POST['drivetype'];
$selected_year= $_POST['year'];
$selected_dsetup= $_POST['drivesetup'];


$query="SELECT * FROM vehicle WHERE(body_type=$selected_btype) OR
(condition='$selected_condition') OR (fuel_type='$selected_fueltype') OR
(make_model='$selected_makemodel') OR (transmission='$selected_transmission') OR
(location='$selected_location') OR (drive_type='$selected_dtype') OR
(year='$selected_year') OR (drive_setup='$selected_dsetup')";


$result=mysql_query($query);
if($result)
{
while ($row=mysql_fetch_array($result)){
echo "<tr>";
echo "<td>".$row['sn']."</td>"; 
echo "<td>".$row['body_type']."</td>";  
echo "<td>".$row['make_model']."</td>"; 
echo "<td>".$row['location']."</td></tr>"; 
echo "<td>".$row['year']."</td>"; 
echo "<td>".$row['condition']."</td>"; 
echo "<td>".$row['transmission']."</td>"; 
echo "<td>".$row['description']."</td></tr>"; 
echo "<td>".$row['images']."</td></tr>"; 
}
}else{
die(mysql_error());
}
?>

The output of search.php:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition='nigerian used') OR (fuel_type='') OR (make_model='peugeot'' at line 1

Try to echo your sql to see what the whole query being executed. Add this after your $query statement. echo '
'.$query; — user1149244, Oct 15 '14 at 00:42

score 0 · Answer 1 · edited Oct 15 '14 at 03:41

0

In your query, the body type selection is not encapsulated in quotes like others, but it is a string so it needs to be like this

$query="SELECT * FROM vehicle WHERE(body_type=<b>'</b>$selected_btype<b>'</b>) OR......

edited Oct 15 '14 at 03:41

Infinite Recursion

6,511
28
39
51

answered Oct 15 '14 at 00:55

HitMeWithYourBestShot

351
2
7

It worked but what if i want to filter my mysql query to display results based on the combination of all what is selected in the drop down boxes. How will i go about it? – Tope Ayowole Oct 15 '14 at 15:22
@TopeAyowole To filter based on ALL parameters you would want to use AND statements.http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_and – HitMeWithYourBestShot Oct 16 '14 at 02:47

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

There are a few issues with your code:

Firstly, condition is a MySQL reserved word and needs to be wrapped in backticks.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Either wrap it in backticks or rename it to another word.

$query="SELECT * FROM vehicle WHERE(body_type=$selected_btype) OR
(`condition`='$selected_condition')

Notice where the error is pointing to:

for the right syntax to use near 'condition='nigerian used')  
                                 ^

Then there's WHERE(body_type=$selected_btype)

The $selected_btype variable needs to be wrapped in quotes, since string values are involved.

WHERE(body_type='$selected_btype')

Footnotes:

You should consider using mysqli with prepared statements, or PDO with prepared statements, they're much safer, because your present code is open to SQL injection.

edited May 23 '17 at 12:11

Community

1
1

answered Oct 15 '14 at 01:13

Funk Forty Niner

74,450
15
68
141

It worked but what if i want to filter my mysql query to display results based on the combination of all what is selected in the drop down boxes. How will i go about it? – Tope Ayowole Oct 15 '14 at 15:21
@TopeAyowole To use all the filters, you may need to play around a bit with the `OR`. – Funk Forty Niner Oct 15 '14 at 16:08
Please can you throw more light? – Tope Ayowole Oct 15 '14 at 16:17
You accepted the wrong answer by the way. @TopeAyowole you accepted the other guy's and not mine. – Funk Forty Niner Oct 15 '14 at 16:20
@TopeAyowole why did you not accept my answer? It is clear that my answer is the reason why your code did not work. Good luck with your code, I am done here. – Funk Forty Niner Oct 15 '14 at 16:29
I am very sorry about that. As a newbie in stack overflow i mistakenly ticked his answer instead of yours. Once again i am very sorry about that. – Tope Ayowole Oct 15 '14 at 16:58

Using multiple PHP drop downs to filter MySQL table data

2 Answers2