PHP/MySQL database search engine

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?  

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.

In fact this link may be better for you ..

http://www.plus2net.com/php_tutorial/php_drop_down_list.php

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.

Why not use Google Site Search?  They kind of have search all figured out over at Google!
http://www.google.com/sitesearch/#utm_campaign=en&utm_source=en-ha-na-us_ca-bk&utm_medium=ha&utm_term={keyword}

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.

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";

Select allOpen 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";

Select allOpen 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).

RF,
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):

<?php
#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
?>

Select allOpen 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.

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.

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."  

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.

Ray, we trip over dollars to save pennies all the time.   Anyhow, I am working towards something like what is on this KBB webpage:

http://www.kbb.com/car-values/

The section like by make (which is easy) or the "search cars for sale"

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.

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?  http://sphinxsearch.com/  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.

Thanks Loco, but I'm getting close to being done with the code now.

Thanks guys, between the two selected answers I was able to peice something together that runs quick and smooth!