magnusalex Posted April 5, 2011 Share Posted April 5, 2011 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! Quote Link to comment Share on other sites More sharing options...
dreamwest Posted April 6, 2011 Share Posted April 6, 2011 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 Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 6, 2011 Author Share Posted April 6, 2011 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! Quote Link to comment Share on other sites More sharing options...
mrqpro Posted April 6, 2011 Share Posted April 6, 2011 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. Quote Link to comment Share on other sites More sharing options...
dreamwest Posted April 6, 2011 Share Posted April 6, 2011 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"); Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted April 6, 2011 Share Posted April 6, 2011 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 Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 6, 2011 Author Share Posted April 6, 2011 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! Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 6, 2011 Author Share Posted April 6, 2011 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? Quote Link to comment Share on other sites More sharing options...
dreamwest Posted April 6, 2011 Share Posted April 6, 2011 Remove the braces {} SELECT * from test_user_data WHERE MATCH streng AGAINST('+dell +6000 +inspiron' IN BOOLEAN MODE) ORDER BY streng LIMIT 5 Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 6, 2011 Author Share Posted April 6, 2011 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! Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted April 6, 2011 Share Posted April 6, 2011 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. Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 7, 2011 Author Share Posted April 7, 2011 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! Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted April 7, 2011 Share Posted April 7, 2011 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 Quote Link to comment Share on other sites More sharing options...
magnusalex Posted April 7, 2011 Author Share Posted April 7, 2011 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.