PHP/MySQL database search engine

Good Afternoon Experts!
I am looking to put in a search engine to our website to filter by specific vehicles.

A bit of details about the site:

The website currently filters by vehicle type – cars, trucks, sport utilities, vans, and toys
Toys are things like motorcycles, boats, etc.
We have anywhere from 400-700 vehicles active on the website at any given time
The site uses PHP/MySQL, with the update to the site it will be the most current version of both.

The problem: There are a couple of different ways I am looking at approaching this and I’m looking for advice on which I should go and how  I would go about doing it.

I need to be able to search the following fields:   1) year 2) make 3) model
In addition I plan on putting in a filter by stock number, but this one will be easy as it will only display the stock number they are looking for.  The following code will easily take care of that one.
$query = “select * from vehicle_table where stocknumber = $stocknumber”

Open in new window

1. Option 1

I have thought about using AJAX to be able to select a make, and then display model, and then display only the available years for that make and model combination.  I’m not quite certain how I would do that.

2. Option 2

The other option I have thought of is to do a select box and list all of the available makes in one box and models in another box.  Followed by a box that shows start year and a box that shows end year.

Variables / Tables / Data storage

The information I am looking to search is currently in a single table.
(similar table name) – vehicle_info
Variable 1 – make
Variable 2 – model
Variable 3 – year

I am certain I am forgetting information, but if you need more let me know and I will do my best to add any information I have forgot to list above.
Who is Participating?
maeltarConnect With a Mentor Commented:
You could use something like what is on this page as a starting block, looks to meet the requrements for Option 2.
I think this sounds more like a user experience question than a coding question.  You can do this many different ways and it is probably best to allow your end user to select in more than one way.

I like option one.  Yes, AJAX is a good way to deal with this and you would have to find a good AJAX library to do it.  It's not too bad, though.

Option 2, I'm not completely clear on. Would the select boxes be populated dynamically as the user selects?  Would the user select the year?  What is the flow?  

shdwmageAuthor Commented:
I had a working set of code before, but it spiked processor usage and our webhost wasn't happy with it.  It's been a long time and I doubt I still have the code.

The form would be like the following I suppose:
After selecting all of the vehicle makes (ford, chevy, chrsyler, etc) it would transition them to a second page where just the models for the selected makes shows up, and then transition again unto a page to select a range of years and then show a result.  

You are correct that it is an experience question, I have writen search engines in different languages, but never in php/mysql. I'm a real stickler for trying to do things as efficiently as possible. I understand the concepts behind it, but since I've never done it I'm a bit tepid about it.

We have a lot of traffic that comes into our website, and I cannot afford to make a mistake that would cause issues with it.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

In fact this link may be better for you ..
AJAX is what I would do.  It is much nicer.  If you were having a processor spike then there had to be bad code.  Ajax shouldn't do that.

Back to option 2, the only thing I don't like about it is that when I do a car search, I like to be able to search in many different ways, by year, by model, by make, etc.  The challenge is finding a good way to present all of these options the user.  I like how newegg works.  You have several different options on a single page and each option limits search further.  

Good luck with this.  It sounds like a fun project.
Ray PaseurCommented:
Why not use Google Site Search?  They kind of have search all figured out over at Google!{keyword}
shdwmageAuthor Commented:
Ray, in my case google site search won't work for what I need.  I need to be able to custom format the output.  Additionally it doesn't handle my site structure well because there are multiple stores running off the same website.  So when I perform a search it returns the search for all the stores showing multiple options for the exact same vehicle.
shdwmageAuthor Commented:
When I assembled my search page before I had a couple of queries on it to build the list.  They were like what follows

$query = "select DISTINCT make from vehicle_info where active ='yes' order by make";
and then doing another query with
$query = "select DISTINCT model from vehicle_info where active = 'yes' order by model";

Open in new window

With the AJAX code, I am assuming when I first build the page I will need to do a query like the first one and build the list with a while loop.

And then have a second file that is called that performs a query like the following:
$query = "select DISTINCT from vehicle_info where active = 'yes' and make = $make order by model";

Open in new window

The other thought that just occured to me is that I probably want to be able to filter by body style as well.  So would I want to select body style first then make then model and drill down that way showing only results for that?  And allowing for "all" to be selected in the body style to remove that filter.
I hope you don't mean to have any part of the query accessible to the user.  

I would create a list of options and maybe even store them in cookies for ease and accessibility.  With each ajax request, you should send the entire list of selected options and values to the server.  This should then be checked on the server side to make sure the data is valid and safe.  Then a query is constructed and the data returned to the page.  This should happen each time there is an update.  

Updates can be automatic or user inittiated (with like a button or something).
shdwmageAuthor Commented:
The queries won't be accessable to the end user. In addition any input that is brought in from the website is thoroughly scrubbed and clean before being allowed to even be processed.

I intend to make a drop down list so they can only select one type of vehicle at a time.  So the following will show up to begin with (in psuedo code):

#display available body styles, ran from database to prevent any non-represented body styles from being shown, sorted by body style to alphabatize.
$query = "select DISTINCT body_style from VEHICLES where ACTIVE = 'yes' order by body_style";
$result = mysql($query);
#start building the list
echo"          <select name="body_style" id="bstyle">
            <option selected="selected" value="all">ALL</option>"
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
#loop through the options to make the list.
echo <option value = '$row[body_style]'>$row[body_style]</option>;
# close the select
echo </select>

#Repeat the above for vehicle makes

Open in new window

Now that is all psuedo code, but you get the jist of what I am thinking. With the turn over rate of vehicles at our company it's difficult to keep up with whats being added and removed at any given time. It's not that I'm opposed to generating a file, xml, txt or whatever, but the data is in a constant state of flux.  I would have to add the update script to the add and remove vehicle selections and even the modification screens.  As I said not opposed, just explain to me how you would handle it please.
Ray PaseurCommented:
I would have a DBA look at this problem, with the data base tables laid out on the wall so you can see all the data at once.  Most automotive sites have some kind of filtering mechanism, so you can look at a few and get some design ideas for the client side of things.
shdwmageAuthor Commented:
Ray, while I would love to do something like that, the company I work for is way to "froogle" to spend the money on that kind of thing. They are even to froogle to pay for antivirus software so I get told a lot "that's what we pay you for."  
Ray PaseurCommented:
It's not "frugal" to save money by not buying anti-virus software.  It is something different from frugal.  Maybe more like foolish.  That's like saying, I want you to take the seat belts out because I want to save money, so I am not going to pay for seat belts.

Here is what the company needs to know:  The cost of design decisions is about 1/1000 the cost of error remediation after the code has been written.  So the time to invest is now, before the code is written.  This isn't something I am just making up - it's taught in the engineering programs of every major college and can be demonstrated in real-life examples, where one company made good design choices and another made poor design choices.  Fast forward to the day of application deployment.  One company is celebrating a successful launch and the other company is making an emergency call to IBM.
shdwmageAuthor Commented:
Ray, we trip over dollars to save pennies all the time.   Anyhow, I am working towards something like what is on this KBB webpage:

The section like by make (which is easy) or the "search cars for sale"
Ray PaseurCommented:
Yeah, KBB have a good interface.  It is a JavaScript thing using AJAX to talk to the back end and populate the pull-down select boxes.  But there is no real "search" going on.  They just have junction tables to associate the model with the make, etc.
rfportillaConnect With a Mentor Commented:
I agree with Ray_Paseur about the AJAX comment.  When you select one dropdown, a request is being made in the background by an AJAX library to populate the next dropdown.  This is certainly a good way to do this.

I'm a little confused, though, as to what is holding you back at this point.  You seem to have the parts that you need.  I think this is the procedure I would follow. Please let me know if there is anything I am missing or doesn't make sense.

1. create a database view with the relevant information
2. Structure code to build the sql query based on filter options.  
    For ex,
       $sql = "select color,price,mileage from .... "
       if(isset ($carmake)) { array_push($filter, "make = $carmake" )}
       if(isset ($carmodel)) {array_push($filter, "model = $carmodel") }
       if(isset ($caryear)) { array_push($filter, "model = $caryear") }
       if(count($filter) > 0) {$sql .= "where " . join (" and " , $filter)}
3. Use AJAX to submit requests and update page after each time user selects from dropdowns

shdwmageAuthor Commented:
Yes, I believe I have all the peices of the puzzle together now. I am in the process of writing the code right now to test with.  I'll update if I run across any snags and/or when I finish with a successful product.
I'm a little late in the game, but have you looked at Sphinx?  We use it a lot for our Rails apps and it does a lot for indexing the data you want to search through and speeds up the search a great deal. It's runs quite well on MySQL and can be used with PHP.

As far as the user experience, the way most of the auto sites require you to pick a year, then pick a make, then pick a model, then pick an engine type, then pick a.... drives me nuts.  It should be WAY easier than that.  I should have one search box that I can start typing in Corvette and it auto completes a selection that I can choose from.  Anyway, that's my professional and personal 2 cents.
shdwmageAuthor Commented:
Thanks Loco, but I'm getting close to being done with the code now.
shdwmageAuthor Commented:
Thanks guys, between the two selected answers I was able to peice something together that runs quick and smooth!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.