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.