Mysql search problem

runelore

Hi, I have a problem..
I have a database of companies and they are in different categories.. (eg. hotels, restaurants, retail etc..).
When I have a search such as :

$result = mysql_query("SELECT * FROM details where name like '%$name%' and town like '%$address%' and postcode like '%$address%' and category like '%$name%' and keywords like '%$name%' order by name");

I want to be able to search on the above fields for results.. the problem is that if you try to search with something such as "hotels and restaurants" it shows no results... How do I enable this ability... Thanks for any help.... -Mo

raymie

If I am understanding correctly you need something like....

SELECT * FROM details where 
      name like '%$name%' 
and town like '%$address%' 
and postcode like '%$address%' 
and (category like '%$cat1%' or category like '%$cat2%') 
and keywords like '%$name%' 
order by name

you could also use a clause such as

category in ('hotels','restaurants')

when you have fixed values to seach for.

Hope that this helps.

VaTech

Or you could parse the text box as in

Hotels+restaurants

and have your code run the query multiple times. Pull all the data for hotels and then for restaurants.

However your query may not work unless they fill in all the fields. SQL is not "smart" enough to say "This field is empty so ignore it".

Instead, run a separate query for each field whose length is greater than 0.

runelore

the main problem is that I have 2 input boxes, "searchdescription box" for like "hotels" or "restaurants" etc.. I also have a location box.. that is not a problem.. The main thing is that if someone searches for "hotels and restaurants" it brings up nothing... but if they just search for "hotels" or "restaurnats" individually it works.. I really need to know why and how to get round this... please help....

raymie

You would not want to run a seperate query for each non-null entry because that would lead to duplicates in the output (potentially)

You would build the query dynamically based on the fields.

such as (example only)

$query = "SELECT * FROM TABLE ";
$clause = " WHERE ";

if($field1 != "") {$query .= $clause." col1 like '%$field1%' "; $clause = ' and '; }

if($field2 != "") {$query .= $clause." col2 like '%$field2%' "; $clause = ' and '; }

etc...

Not pretty but easy for beginners to work with

raymie

reply to runelore's last message.

Ok I get it now.

What you need to do is use explode to split the search term up and then build the query accordingly while looking for the "or" and "and" etc...

Not that difficult if the users enter valid data.

runelore

Hi there, that looks perfect but can you explain it very very briefly, so I can understand it properly.. thanks again for all the help...

raymie

Sure,

(but the following is not in strict PHP)....

Assume search string has words with spaces in between

$snippet = "";
$prev_word = false;
$words_array = explode(" ",$search_string);
for each element in $words_array as $w
{
if($w = "on" or "and")

{
if($prev_word)
{
$snippet .= " ".$w." ";
$prev_word = false;
}
}
else
{
if($prev_word)
{
$snippet .= " OR ";
}
$snippet .= " field1 like '%".$w."%' ";
$prev_word = true;

}

The above is only pseudo-PHP but the basic idea is to take the search term a word at a time.

The trick is to allow for search strings like

"OR HOTEL"
"HOTEL OR RESTARAUNT"
"HOTEL RESTARAUNT"

The above code (if it works) should allow for all of these and will result in the variable $snippet having the clause needed for that field you should then surround it with parenthesis when appending to the search string.

This is a candidate for a function rather than coding in place for each field.

Hope that this helps.

runelore

That is perfect, I am not 100% on the code you wrote but I will persist and I will understand it soon. Thank you for all the help...
-Mo

raymie

Not a problem,

please remember that this was of the top of my head and is probably a big bugged but if you still have problems then give me a shout and I will knock up some proper code for you.

Regards

runelore

Can you explain why I need the prev_word variable... thanks.... sorry for bothering you again....

deathrider

from my point of view the prev variable is getting asked for to check wether the query snippet which should be added has to begin with "AND" as it connects the snippets...practically you mix the strings which should be searched for with SQL syntax.

hope that helps 🙂

raymie

You need to know if the last work processed was a search term such as HOTEL or a predicate such as OR or AND and adjust the created SQL accordingly,

for example if the previous word was a search term and the current word is also a search term then the user omitted the OR and you need to add one in.

runelore

I am working hard on this one,. thanks again... You don't by any chance know how to find out how many items an array contains do you?

thanks again....

raymie

sizeof

runelore

You have been the most helpful person I have ever met on any forum. Thank you very much, I really appreciate you help.. thanks again.... I am a 3D CGI artist, need any CG doing?? Hehehe..
thanks again....

raymie

Nice of you to say but I think that over time you will find a lot of helpful people here and in related sites and usenet.

I suspect that if you posted enough questions you could probably get most of a site coded for you by the answers 🙂