Search MySQL with several words in once?

[magnusalex]

Hi,

 

I am currently using this code as part of my search module:

 

$sql_res = mysql_query("SELECT * from test_user_data WHERE streng like '%$q%' or beskrivelse like '%$q%' or sku like '%$q%' ORDER BY streng LIMIT 5");

 

Simple code that works if my client searches for a string (or part of) that is contained in test_user_data table. If he searches "Dell Inspiron 6000", he will get a hit. However, if he searches for "Inspiron Dell 6000", he will not. Is it a simple way to make the client/enduser search for multiple words contained in a single cell?

 

Thank you! This forum is awesome!

[dreamwest]

 foreach(explode(' ',$title) as $word)
$q .= "{$word}* ";

$sql_res = mysql_query("SELECT * from test_user_data WHERE MATCH streng AGAINST('{$q}' IN BOOLEAN MODE) ORDER BY streng LIMIT 5");

 

http://www.wizecho.com/nav=php&s=php_mysql_boolean

 

[magnusalex]

Ah, thanks!

 

Do you happen to know how to make that require all the words in the same cell? Like it is now it prints hits from all of the words searched. All the hits for Dell, all hits for Inspiron, all hits for 6000 etc...

 

I really appreciate your help!

[mrqpro]

I think in this case you have to use "Full text search". That's sure. You can search this technique on google.

Remember that your database engine must be InnoDB to support full text search. You also have change database structure.....

Good luck.

 

[dreamwest]

So you need both words? Just add a "+". You can find all the options here http://www.wizecho.com/nav=php&s=php_mysql_boolean

 

 foreach(explode(' ',$title) as $word)
$q .= "+{$word}* ";

$sql_res = mysql_query("SELECT * from test_user_data WHERE MATCH streng AGAINST('{$q}' IN BOOLEAN MODE) ORDER BY streng LIMIT 5");

 

[QuickOldCar]

I think in this case you have to use "Full text search". That's sure. You can search this technique on google.

Remember that your database engine must be InnoDB to support full text search. You also have change database structure.....

Good luck.

 

You have that backwards, innodb does not support full text search.

 

As for the change of database structure, you make an index on anywhere in the query is a  where or and

[magnusalex]

Got it to work! Using fulltext does not seem to make any difference. I tried the query directly in phpMyAdmin as well to see the raw result - it's exacly the same with and without fulltext.

 

Thank you so much!

[magnusalex]

Hm... Just one more thing:

 

My SQL table coloumn "streng" have these values (and 200.000 more):

 

310-3543 Dell P 1500 compatible toner 6000 pages
310-5402 Dell P 1700 compatible toner 6000 pages
593-10239 Dell P 1720 compatible toner 6000 pages
Dell Inspiron 6000 Compatible battery
Dell Inspiron 6000 Original battery

 

If I run this query:

 

SELECT * from test_user_data WHERE MATCH streng AGAINST('{+inspiron +dell +6000}' IN BOOLEAN MODE) ORDER BY streng LIMIT 5

 

I will get this result:

 

310-3543 Dell P 1500 compatible toner 6000 pages
310-5402 Dell P 1700 compatible toner 6000 pages
593-10239 Dell P 1720 compatible toner 6000 pages
Dell Inspiron 6000 Compatible battery
Dell Inspiron 6000 Original battery

 

But if I swap position of the words in the query to this:

 

SELECT * from test_user_data WHERE MATCH streng AGAINST('{+dell +6000 +inspiron}' IN BOOLEAN MODE) ORDER BY streng LIMIT 5

 

This is my result:

 

Dell Inspiron 6000 Compatible battery
Dell Inspiron 6000 Original battery

 

How is that possible? How come it returns hits without the word inspiron in the first query?

[dreamwest]

Remove the braces {}

 

SELECT * from test_user_data WHERE MATCH streng AGAINST('+dell +6000 +inspiron' IN BOOLEAN MODE) ORDER BY streng LIMIT 5

 

[magnusalex]

Thank you, again!

 

But I am getting some strange behavior... This is another few of the cell values from my database:

 

2HR-AAAU battery
90.AA202.001 Compatible battery
AAAx3 Compatible battery
AACR50100001K0 Compatible battery
AACR50100001K2 Compatible battery
9V Alkaline Duracell Procell
Duracell Procell AA 1,5V Alkaline
Duracell Procell AAA 1,5V Alkaline
Duracell Procell C 1,5V Alkaline
Duracell Procell D 1,5V Alkaline

 

By doing this query:

 

SELECT * from test_user_data WHERE MATCH streng AGAINST('%+procell*%' IN BOOLEAN MODE) ORDER BY streng LIMIT 5

 

I get this result:

 

9V Alkaline Duracell Procell
Duracell Procell AA 1,5V Alkaline
Duracell Procell AAA 1,5V Alkaline
Duracell Procell C 1,5V Alkaline
Duracell Procell D 1,5V Alkaline

 

Wich is correct. When doing this query:

 

SELECT * from test_user_data WHERE MATCH streng AGAINST('%+procell* +aa*%' IN BOOLEAN MODE) ORDER BY streng LIMIT 5

 

I would expect it to return:

 

Duracell Procell AA 1,5V Alkaline
Duracell Procell AAA 1,5V Alkaline

 

But in reality, it returns this:

 

2HR-AAAU battery
90.AA202.001 Compatible battery
AAAx3 Compatible battery
AACR50100001K0 Compatible battery
AACR50100001K2 Compatible battery

 

... wich has nothing to do with the word "procell" at all. Is it forgetting the word?

 

My complete code that you helped me with up to now is like this:

 

$q=$_POST['searchword'];

foreach(explode(' ',$q) as $word)
$q2 .= "+{$word}* ";

$sql_res = mysql_query("SELECT * from test_user_data WHERE MATCH streng AGAINST('%$q2%' IN BOOLEAN MODE) ORDER BY streng LIMIT 5");

 

If anyone could helt getting this right, I will be more than happy to donate some cash your way!

[QuickOldCar]

Here's an example of the "at least one word" search query I use which also can do the +,- and the or if no + or - is used between the words.

 

If you don't explode the search word by + and just use the POST value as is, you can then use the add/include/or with +,-,space

 

Typical searches would be

+battery +procell

+battery +procell -aaa

battery +a +c -d

battery procell

 

 

$q= mysql_real_escape_string($_POST['searchword']);
$q = trim($q);
$sql_res = mysql_query("SELECT * FROM test_user_data WHERE MATCH streng AGAINST ('$q' IN BOOLEAN MODE)  ORDER BY streng LIMIT 5" );

 

Dreamwest posted the same query.

[magnusalex]

But that would require the user to write his search with the separators and/or/both? I am afraid that is not something I could use... My client runs a store that sells batteries. If a customer wants a new battery for his Dell Inspiron 6000, I can't require him to write his search like "+Dell +Inspiron +6000".

 

If there is another way, I would really love to hear the idea! I have an example of the searchbox here: http://himmelriket.org/example/

 

The entire file that generates the results:

 

<?php
include 'configuration.php';
if($_POST) {

$q=$_POST['searchword'];

foreach(explode(' ',$q) as $word)
$q2 .= "+{$word}* ";

$sql_res = mysql_query("SELECT * from test_user_data WHERE MATCH streng AGAINST('%$q2%' IN BOOLEAN MODE) OR MATCH sku AGAINST('%$q2%' IN BOOLEAN MODE) ORDER BY streng LIMIT 5");

//The original query, not able to pick up multiple words 
//$sql_res=mysql_query("select * from test_user_data where streng like '%$q%' or beskrivelse like '%$q%' or sku like '%$q%' order by streng LIMIT 5");

while($row=mysql_fetch_array($sql_res)) {

$streng=$row['streng'];
$beskrivelse=$row['beskrivelse'];
$bilde=$row['bilde'];
$url=$row['url'];

$re_streng='<b>'.$q.'</b>';
$re_beskrivelse='<b>'.$q.'</b>';

$final_streng = str_ireplace($q, $re_streng, $streng);

$final_beskrivelse = str_ireplace($q, $re_beskrivelse, $beskrivelse);
?>

<div class="display_box" align="left" onclick="location.href='<?php echo $url; ?>';" style="cursor:pointer;">
<img src="<?php echo $bilde; ?>" style="width:25px; float:left; margin-right:6px" /><?php echo $final_streng; ?><br/>
<span style="font-size:9px; color:#999999"><?php echo $final_beskrivelse; ?></span></div>

<?php } } else { } ?>

 

If you try with the searchword "duracell aaa procell" you will not get any results, i think that there must be an error somewhere. The content of that product "streng" cell is "Duracell Procell AAA 1,5V Alkaline".

 

Thank you so much for your time!

[QuickOldCar]

OK, you can look here a bit.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

 

So keep your code as it is, but get rid the % in your query.

 

Again.....the one that dreamwest posted.

$sql_res = mysql_query("SELECT * FROM test_user_data WHERE MATCH streng AGAINST ('$q' IN BOOLEAN MODE)  ORDER BY streng LIMIT 5" );

 

If you do the above query....any word they insert you then added the + to the front of the word by using $q2 .= "+{$word}* ";..., so that means in any order it should find the same results no matter which order of the words they use.

 

All these would return the same exact results

Duracell Procell AAA

Procell Duracell AAA

AAA Procell Duracell

AAA Duracell Procell

 

as for AAA not there, most likely has to do with mysql minimum word length default of 4 minimum characters.

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_min_word_len

 

 

[magnusalex]

Hm... About the word-length, why do I get results that all contains AA or AAA, but not Procell, if AA or AAA is too short? Do you think I should try changing the ft_min_word_len setting?

 

Ok, changed it to this like dreamwest's code:

 

$q=$_POST['searchword'];

foreach(explode(' ',$q) as $word)
$q .= "+{$word}* ";

$sql_res = mysql_query("SELECT * FROM test_user_data WHERE MATCH streng AGAINST ('$q' IN BOOLEAN MODE) ORDER BY streng LIMIT 5" );

 

Correct?

 

By removing the % in the query I often get unexpected results. For example "Inspiron Dell 6000" gives hits on both dell and 6000, but not inspiron... Try that in the url above and you'll see.

 

For reference: In my DB i have the "streng" coloum set at FULLTEXT index, and type "text".

 

Again: Thank you so much for your time!