Page 1 of 1
PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 12:44 pm
by Metal-X-Man
Here's my question: In my MySQL table, I have a field that I search. In this field, I can have a compound word... or several words describing a product like: "Chevy Red Truck". as you can see, three words in one field.
My limited knowledge expected a simple query to work:
Code: Select all
mysql_select_db($database_ml, $ml);
$query_reviews2 = ("SELECT * FROM ap_form_2 WHERE element_3 LIKE '$_GET[element_3]'");
$reviews2 = mysql_query($query_reviews2, $ml) or die(mysql_error());
$row_reviews2 = mysql_fetch_assoc($reviews2);
$totalRows_reviews2 = mysql_num_rows($reviews2);
As you can see, my "=" doesn't work with multiple words in a field. I am passing a URL variable to this page for the query (i.e. $GET[element_3]). Additionally, "LIKE" doesn't work either.
It looks like I'm going to have to use "EXPLODE" to get all the words into and array and then query. I found this suggestion on the web:
Code: Select all
$array = explode(' ', $_GET[element_3]);
$query = 'SELECT * FROM ap_form_2 WHERE ';
foreach($array as $in)
$query .= 'element_3 LIKE "%'.$in.'%" OR ';
mysql_query($query.'0');
But I'm not sure what it's doing. The $array gets each individual word in the field, but the loop doesn't make sense to me. It takes the varible $IN and concatenates the looping results.
Any direction would be appreciated!!! Thanks again!
Metal-X
Re: PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 1:08 pm
by Jade
It looks like what you really need is this:
Code: Select all
mysql_select_db($database_ml, $ml);
//replace all the spaces with % signs
//then escape any bad characters to prevent SQL injections
$value = mysql_real_escape_string(str_replace(" ", "%", $_GET['element_3']));
$query_reviews2 = ("SELECT * FROM ap_form_2 WHERE element_3 LIKE '%$value%'");
$reviews2 = mysql_query($query_reviews2, $ml) or die(mysql_error());
$row_reviews2 = mysql_fetch_assoc($reviews2);
$totalRows_reviews2 = mysql_num_rows($reviews2);
Re: PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 1:12 pm
by AbraCadaver
Something like this maybe:
Code: Select all
$expression = "element_3 LIKE '%" . implode("%' OR element_3 LIKE '%", explode(' ', $_GET['element_3'])) . "%'";
$query_reviews2 = ("SELECT * FROM ap_form_2 WHERE $expression);
Re: PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 2:02 pm
by Metal-X-Man
You guys are the best! I have it fixed now. I'll study this a little further so I can learn. I built a review forum that has some basic search queries and this should finish it up!
Re: PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 2:16 pm
by josh
Re: PHP Queries - Compound Words
Posted: Thu Jan 13, 2011 2:23 pm
by Metal-X-Man
I'll review tonight - good (actually, great) advise.
Thanks!
Metal-X
Re: PHP Queries - Compound Words
Posted: Fri Jan 14, 2011 3:02 pm
by Metal-X-Man
Jade wrote:It looks like what you really need is this:
Code: Select all
mysql_select_db($database_ml, $ml);
//replace all the spaces with % signs
//then escape any bad characters to prevent SQL injections
$value = mysql_real_escape_string(str_replace(" ", "%", $_GET['element_3']));
$query_reviews2 = ("SELECT * FROM ap_form_2 WHERE element_3 LIKE '%$value%'");
$reviews2 = mysql_query($query_reviews2, $ml) or die(mysql_error());
$row_reviews2 = mysql_fetch_assoc($reviews2);
$totalRows_reviews2 = mysql_num_rows($reviews2);
Last night I used this code on my .php web page and the query works perfectly. The search was too broad so I removed the leading wildcard. Now it works even better.
For my edification, what is the purpose of filling in the blanks with a "%" character? Off the top of my head, it makes the field content look like one big word verse individual words. Furthermore the "LIKE" in the query doesn't worry about the "%" characters, but looks for a rough match. Am I thinking correctly?
Thanks again!
Metal-X
Re: PHP Queries - Compound Words
Posted: Fri Jan 14, 2011 4:16 pm
by Jade
% is a wildcard. What it does is look for words that have parts of the phrase. For instance:
SELECT * FROM users WHERE username LIKE '%red' -- this returns everyone whose username ends in the letters RED
SELECT * FROM users WHERE username LIKE 'red%' -- this returns everyone whose username starts with the letters RED
SELECT * FROM users WHERE username LIKE '%red%' -- this returns everyone whose has the word RED inside of their name. It could be at the beginning or the end of the word.