1,105,229 Community Members

PHP MySQL Database Filters

Member Avatar
Darkrellin
Newbie Poster
4 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
NinjaMediaD
Light Poster
44 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
1
 

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'] != '')
Member Avatar
Darkrellin
Newbie Poster
4 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
NinjaMediaD
Light Poster
44 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
1
 

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>";
Member Avatar
Darkrellin
Newbie Poster
4 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: