Performing MySQL search based on multiple keywords

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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Performing MySQL search based on multiple keywords

Post by simonmlewis »

Hello

This query sounds so straightforward, but I'll be damned if I can crack it.

I have a Product page that has a field named "tags". In tags could be something like this:

Code: Select all

robot, fun, toy
Other products may also have
robot, fun
What I need is for one product to be rendered, and if it sees in the rest of the database "robot" or "fun" or anything else that is in it's own tags field, then I want it to render the data from that result.

So: BIG ROBOT has "robot, fun, toy" in it's tags, and a FUNKY CAN OPENER has "fun, toy" in it's tag. I want "FUNKY CAN OPENER" to be displayed at the bottom of the BIG ROBOT's product page.

does this makes sense?

Iv'e tried the following, but it fails.

Code: Select all

$result = mysql_query ("SELECT * FROM 6_products WHERE id = '$myprod'");
 
while ($row = mysql_fetch_object($result))
      {
            echo "
<table width='100%' cellspacing='0' cellpadding='0'>
<tr><td>
            <div class='productdescription'>$row->description</div>
            <div class='productimage'><img src='images/productphotos/$row->photo'></div>
            <div class='productbuynow'>TAKE A CLOSER LOOK | <font color='#ff0000'>ONLY £6.00 BUY NOW</font></div>
            <div class='productfreeshipping'><img src='images/icon_freeshipping.png' /></div></td></tr>
      <tr><td>
        <div class='youtube'>$row->video</div>
        </td></tr></table>
            ";      
                
                $resulttags = mysql_query ("SELECT * FROM 6_products WHERE tags LIKE '%$row->tags%'");
while ($rowtags = mysql_fetch_object($resulttags))
                  {
                echo "$rowtags->title";     
          }
        
            }
Last edited by Benjamin on Tue May 12, 2009 11:39 am, edited 1 time in total.
Reason: Changed code type from text to php.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Griven
Forum Contributor
Posts: 165
Joined: Sat May 09, 2009 8:23 pm

Re: Performing MySQL search based on multiple keywords

Post by Griven »

If you're searching for the values in one column, you can use MySQL's 'IN' syntax.

Code: Select all

SELECT * FROM 6_products WHERE tags IN ('fun','robot','toy')
This will cause MySQL to return all values that have a tag machine one or more of the values specified in the IN clause.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Performing MySQL search based on multiple keywords

Post by simonmlewis »

Just one problem here. Those three tags are not the only ones.

The tags will be entered in the CMS by the administrator. So there could be one, there could be 50, per field.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Performing MySQL search based on multiple keywords

Post by simonmlewis »

Code: Select all

$string = "$row->tags";
$token = strtok($string,",");
while($token) {
$resulttags = mysql_query ("SELECT DISTINCT title FROM 6_products WHERE tags LIKE '%$token%'");
while ($rowtags = mysql_fetch_object($resulttags))
                  {
                echo "$rowtags->title&nbsp;$token<br/>";
                  }
            $token = strtok(",");
              }
        
            }
    mysql_free_result($resulttags);
Single Touch Jar Opener him
The Air Right Robot him
The Air Right Robot toy
This solution works, to a point. It's actually producing several products several times.
So if it find three of the tags in another product, it renders the Title three times.
I thought DISTINCT would cancel this and only produce it once.

I think, if I can get it to produce it just one, then I have cracked.
Last edited by Benjamin on Wed May 13, 2009 1:07 pm, edited 1 time in total.
Reason: Changed code type from text to php.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply