Multi Option Search

Jimi_l

Hi All,

I am trying to help our IT guy at work with a custom solution for our staffers. I have the general idea down (at least I think anyway) but I need a few nudges in the right direction. This is an in house use only application for employee reference as opposed to a public use kind of thing.

What we are trying to do is convert a hand made Excel spreadsheet with auto part information to a MySQL DB and then build a multi option search for that DB. I assume from what I have been reading online that I would do this using HTML to build the form and JS & PHP to connect, search and post the results.

The spreadsheet has multiple tables, one for each model (Audi thru VW). On each table there are multiple rows/colums for each model I.E.-

Make
Model
Year
Part #'s
Comments

The import from .xls to MySQL I think I can handle. The form I can build in HTML and I have found the code to connect to the DB using user, pass, DB name, etc.

What I can't quite pin down is the code for the search itself, more specificaly how to search the entire DB at once using multiple, hopefully drop down choice type options from the form although we could just type them in if it's easier. I have found plenty of "type in the word and get a reply" type search code snippets, but none for multiple fields. The goal is to have three drop down boxes (make, model and year), hit search and get all the info in the DB for that vehicle.

It seems like this must already exist somewhere and should not be that hard. I can edit PHP, PERL have some MySQL hands on and can do HTML but code from scratch is more than I can handle. I have a Web server running on a Windows box with all the required extensions and MySQL for a Web site I created and manage for a local non profit. I assume there must be some editable snippets already out there that I can modify to do this for me?

Any help is more than welcome and thanks in advance,

Jim

Sxooter

First thing you need to do is normalize your data some. Instead of a table for each manufacturer, put all the cars in one table, with a column for manufacturer.

Then, look at some code that does what you're doing, i.e. creates select boxes etc... There's plenty of apps that do what you want to do, just not necessarily with cars.

While there's a lot of room for normalization past what I've said, like putting all the models in one table, manufacturers in another and so on, and fking your main table to those tables, you can worry about that later.

to build a drop down for something like all the manufacturers, you could use something like this: (this is in pgsql, but most of it would just change to mysql_xxx and be done)

$query = "select distinct manufacturer from bigtable";
$r = pg_query($query);
print "<select name=\"manufacturer\">";
while ($row = pg_fetch_assoc($r){
    print "<option value=\"".$row['manufacturer']."\">".$row['manufacturer']."</option>";
}
print "</select>";

And so on.  You can then get the value when the form is submitted with 
$mfct=$_GET['manufacturer'];

Jimi_l

Thanks for the reply,

It looks as if data normalization is going to be an issue. Many of the year values are too vague to be used as is like years for example. They are currently listed in two digit form with a hyphen in between I.E.
88-05

I don't think anything is going to be able to natively "know" that means 1988 thru 2005. I suspect it would hit on "88" or "05" but nothing in between.

The drop down I can do it's just the actual syntax to get the form input to the DB to search and then back to be displayed.

I appreciate the help and realize I am probably over my head but I like to learn and will keep plodding.

Jim