SELECT WHERE $1 and/or $2 and/or $3

jshowe

Hi,
I am doing a search in my database and want to be able to use multiple words(actually up to 3 is what I have setup and working). The problem is that I want the operator to not be an exclusion. I want in the "WHERE description LIKE variable1 and variable2 and variable3" to be more like and/or... If it doesn't find variable2 or variable3 in the search then still display the stuff found from variable1. Any ideas?

$mysearch = explode(" ",$search);
$i=0;
$j=0;
if(sizeof($mysearch)==1)
{
$i=1;
}
else
{
$i=1;
$j=2;
}

$result = mysql_query("SELECT image,itemname,category,description,price,supporturl,active FROM products WHERE description LIKE '%$mysearch[0]%' AND description LIKE '%$mysearch[$i]%' AND description LIKE '%$mysearch[$j]%'",$dbLink);

John_Canyon

$result = mysql_query("SELECT image,itemname,category,description,price,supportu
rl,active FROM products WHERE description LIKE '%$mysearch[0]%' AND description LIKE '%$mysearch[$i]%' AND description LIKE '%$mysearch[$j]%'",$dbLink);

Just change the above to:

$result = mysql_query("SELECT image,itemname,category,description,price,supportu
rl,active FROM products WHERE description LIKE '%$mysearch[0]%' OR description LIKE '%$mysearch[$i]%' OR description LIKE '%$mysearch[$j]%'",$dbLink);

Good luck.

toma42

Since your code uses mysql functions, read the match() function at the bottom of these docs:

http://www.mysql.com/doc/en/String_comparison_functions.html

John_Canyon

Match() works well if you are using MySQL 4.0, however it is not so great with the earlier versions of SQL.

jshowe

The OR doesn't seem to work as desired because it prints everything in the database. I am thinking the match() should do the trick... I will try that and let everyone know. Thanks! 😃

jshowe

Well... the fulltext search MATCH() was a great idea. Unfortunately it isn't going to do everything because of the 3 character limit. People who are searching for 40 gig hard drives will not find what they are looking for 🙂 Any other ideas or ways around the 3 character limit? The Match() does exactly what I am looking for: find rows that contain at least one of these words.

John_Canyon

There is a way around that problem with match()

You need to locate your mysql.cnf and add this line
to it.

ft_min_word_len = '1' //or whatever minimum length you require.

Good luck.