PHP Queries - Compound Words

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Metal-X-Man
Forum Newbie
Posts: 13
Joined: Tue Mar 16, 2010 9:25 am
Location: Columbus, Ohio

PHP Queries - Compound Words

Post 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
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: PHP Queries - Compound Words

Post 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);
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: PHP Queries - Compound Words

Post 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);
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Metal-X-Man
Forum Newbie
Posts: 13
Joined: Tue Mar 16, 2010 9:25 am
Location: Columbus, Ohio

Re: PHP Queries - Compound Words

Post 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!
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: PHP Queries - Compound Words

Post by josh »

User avatar
Metal-X-Man
Forum Newbie
Posts: 13
Joined: Tue Mar 16, 2010 9:25 am
Location: Columbus, Ohio

Re: PHP Queries - Compound Words

Post by Metal-X-Man »

I'll review tonight - good (actually, great) advise.

Thanks!

Metal-X
User avatar
Metal-X-Man
Forum Newbie
Posts: 13
Joined: Tue Mar 16, 2010 9:25 am
Location: Columbus, Ohio

Re: PHP Queries - Compound Words

Post 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
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: PHP Queries - Compound Words

Post 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.
Post Reply