Search script

chrislead

I am wanting to search one of my databases, which I have sucessfully achieved using the code:

SELECT DISTINCT Plus.Desc, Cstr(Price) AS string_price FROM Plus WHERE Plus.Desc LIKE '%".$criteria."%'"

The problem is, I need to make this script more advanced to account for multiple criteria strings. I.e. If the criteria is "Gloss Paint" I want the results to bring up all the products described with either of the two words in the criteria, so it may bring up the products "gloss 5L white" and also "matt paint 5L", whereas, at the moment with the script, only the products with both the two words will appear, i.e. "gloss paint 5L". which obviously limits the results to very few and is very ineficient.

Any help with this is much appreciated. Cheers

MikeSnead

You'll probably need to break apart the search string by some sort of method and then change the query to something that achieves this effect:

"SELECT DISTINCT Plus.Desc, Cstr(Price) AS string_price FROM Plus WHERE Plus.Desc LIKE '%".$criteria1."%' OR '%".$criteria2."%'"

I'm not sure if that would be the correct SQL syntax though :p But I think that is the idea you would have to follow... at least without having to run a separate query for each word or set of words.

chrislead

Rite, thanks for your reply,

Ill try that and post back if I am unsuccessful, many thanks.

kburger

Actually, I'd suggest using a MySQL Full-Text search. This is more along the lines of what most search engines use.

chrislead

Nice one, i'll take a look, however I notice this is specifically suited to MySQL, and I am using an ODBC to connect to the database.

Would this still be supported in the query?

kburger

It would work fine, as long as you're using a MySQL database.

chrislead

Lol, well thats the thing I was getting at, I am not. I am connecting to MS Access.

:queasy:

kburger

Well, sorry. Yet another reason not to use Access for a real database application. Unless this is a really small application, I'd really suggest downloading and installing MySQL. It can even be used on Windows (if you must). :rolleyes:

chrislead

Fair doos, I mean I have got both MySQL and Access running on the site, but I can't exactly get away from the Access database, not with over 20,000 products already on there that need to be on the web. Youch.

I'll keep searching, but thanks for ure advice anyway.

kburger

Yeah, I understand. I've been there. I'm actually currently working on a project for a client that is just that. I'm moving their databases from MS Access on their LAN to an internet-based MySQL database.

Wow, that's a lot of data for Access. I'm sure porting it over would be quite a chore. You've probably heard this before, but sometimes you have to stop chopping wood long enough to sharpen the axe. 😉

chrislead

Lol, soo true.