Page 1 of 1

Performing MySQL search based on multiple keywords

Posted: Tue May 12, 2009 11:17 am
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";     
          }
        
            }

Re: Performing MySQL search based on multiple keywords

Posted: Tue May 12, 2009 12:44 pm
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.

Re: Performing MySQL search based on multiple keywords

Posted: Tue May 12, 2009 12:53 pm
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.

Re: Performing MySQL search based on multiple keywords

Posted: Wed May 13, 2009 6:24 am
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.