PHP MySQL Database Filters

[Darkrellin]

Hi,

I am making a website for a car database. I made a table called Cars with fields Name,Make,Model,Color,Doors. I have also made a search function:

SELECT * FROM Cars WHERE Name LIKE '%$search%'

This is a general search that will search the whole table, but what if I wanted a filter on it like a scroll down list for car colors or a check box for 4 door that will give me a search result. e.g I am looking for a car named Civic but I only want it to be black and 2 door.

And one extra added thing how do I then take the output, make it into a unique link for each different car and send it to a different site.

Help appreciated,
Thanks.

[NinjaMediaD]

To add a link or a path to the unique car you would also need to add another column for storing a path in the car table. If I were you I would make an auto increment id tag as well so you could make the your PK. Looks like you also forgot the year..

Fortunately the way the sql query would work you can add your filters to the end of your statement. For instance if you had a form that went with it with extra items to search for like doors/ color etc.. All you would need is for your form to also have either drop down boxes, or multi select boxes.

then in your php file that processes the form you can use if/then statements to find if something was selected and add that to the search query.

$myQuery = "SELECT * FROM Cars WHERE name like ".$search;
//then you pick out the items from the form
//if doors were the name of your doors select/option box
if(isset($_POST['doors'])){
$doors = $_POST['doors'];
$myQuery .= " AND Doors = '$doors'";
}
//add as many checks under here.  Alternatively you can have the not set value to equal '' then your test would be if($_POST['doors'] != '')

[Darkrellin]

Exactly what I was looking for. I do have another question though, I have made another column called car_id and made it auto increment as you said, now lets say I have six records so 1-6. How do I take that data with the query I am doing above to then redirect you to the proper site. Thanks.

[NinjaMediaD]

car_id can be used to create a backend page where you can edit the car instead of having to go to the php admin and do it through sql, you can set it up so you can select a car_id and alter just one part of it, or all of it or delete it (this is kind of a future planning) To add a path to it you would also need a column for the path, and make it text. Then you can do something like this:

//after your selection query
//assuming a select * from car 
//and the columns are in this order
//car_id,Name,Make,Model,Color,Doors,path

//make a display table for out put
echo "<table><tr><td>Car name</td><td>Make</td><td>Model</td><td>Color</td><td>Doors</td></tr>";


//assumes you named your query $myQuery
$result = mysqli_query($mysqli,$myQuery) or die("MySQL error: " . mysqli_error($mysqli) . "<hr>\nQuery: $myQuery"); 
	
//fetch the results and post them to an array
while($row = mysqli_fetch_array($result, MYSQLI_NUM)){

//$row becomes an array with 0 being the first column to 6 being the last

//output row with a link using the data in car name ($row[1]) to point to the path ($row[6])
 
	      echo "<tr><td><a href=\"".$row[6]."\">".$row[1]."</a></td><td>".$row[2]."</td><td>".$row[3]."</td><td>".$row[4]."</td><td>".$row[5]."</td></tr>";	

//$row[0] is not used here because we aren't editing here we are displaying results so we ignore it
	}//close while loop

//close display table
echo "</table>";

[Darkrellin]

After I finish testing other features I will try this thank you so much for your help, I just started learning to code this stuff last week. Very helpful!