Multiple Field Search

[Jimi_l]

PHP5

MySQL 5.0.26

 

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 would do this using HTML to build the form and PHP to connect to the DB, run the 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 what appears to be standard 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 a word and get a reply" type search code snippets, but none for multiple fields. The goal is to have three or more drop down boxes (make, model and year for example), 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 MySQL query code from scratch is above my head right now. 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. I can post the form HTML and the connect code but I don't think it would help much. If I could just see a similar query I am confident i could decypher it for my use but I just cannot find one.

 

Jim

 

[fenway]

Mysql is very limited in this capacity - LIKE %$yourString%, FULLTEXT indexing, your own custom index table or a third-party plugin are pretty much the only way to go.

[Jimi_l]

Hi Fenway,

 

Would there be a better solution? What plugins should I be looking at?

 

Thanks,

 

Jim

[fphp]

It seems to me that this has little to do with MySQL and more with how you write your server side program.  You mentioned PHP which is, IMHO, a good choice. 

 

Personally, I would use AJAX to populate drop down boxes according to chosen options.  In other words, start with one box (brand/make), generate 2nd box with model after make is identified, generate 3rd box with year after model is chosen ... and so on ...  You see this kind of filtering used throughout (specially when downloading software upgrades/drivers where you have to identify a series of things prior to getting the applicable upgrades/drivers).

 

A couple of simple onchange() events (JavaScript) and some JS routines combined with your PHP should be the ticket.  Probably, using JSON but not necessarily a must.

 

I do not think you need plug-ins for this ... unless I am missing the entire point.

 

Hope this 'points' you in the right direction  (no punt intended).

 

Regards,

 

JL

 

[Jimi_l]

You are correct,

 

That is exactly the issue and what I had in mind. If I could see such an application I am sure I could modify it for my use but I have been unable to find any multi option search in any script.

 

Odd but there was another reply and my response to it but they seem to have disappeared ??? It regarded the DB structure itself which may be a problem. All the years are in two digit hyphenated form (88-05). I don't think there is any language that is going to "know" that means ALL the years 1988 thru 2005 but would only hit on 88 and 05 instead.

 

Thanks again,

 

Jim

[Jimi_l]

Hi All,

 

Still on the hunt for a three option, all tables and rows in a single MySQL DB search and came across this code (attached). I was hoping someone could clear a few things up for me.

 

This line for example-

// EDIT HERE and specify your table and field names for the SQL query

    $query = "SELECT * FROM tablename WHERE field1 LIKE \"%$trimm%\" OR field2 LIKE  \"%$trimm%\" OR field3 LIKE \"%$trimm%\" ORDER BY field1  DESC" ;

 

When he refers to editing the table I assumed it means change the section "FROM tablename" to read "FROM one table in my db" but what does he mean by field?

 

Perhaps a better question is how would this be re-written to include ALL tables and rows in a single DB?

 

Also of note is there seems to be no HTML for search options. Most of the code samples I have found so far start with an HTML form to input the search parameters. I can write my own easy enough but how to connect it to this script?

 

Thanks,

 

Jim

 

[attachment deleted by admin]