Databases
StackOverflow2013
Postsdbo
POmysql HAVING group for multiple LIKE conditions?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql HAVING group for multiple LIKE conditions?
    text
    copied!<p>I have a list of items that each contain a related to a list of tags.</p> <p>I can retrieve these items by filtering the tag:</p> <pre><code>HAVING (GROUP_CONCAT(tag.name) LIKE '%$tag%') </code></pre> <p>So a list of <strong>tag.names</strong> for "ITEM x" could be: shoes, sandals, heel, black, beautiful</p> <p>And the <strong>$tag</strong> that I'm searching for is: "<strong>shoes</strong>" - which would give me the "ITEM x".</p> <p>But what happens when the user searches for tags with more than 1 word? ie. "<strong>nice black shoes</strong>". The condition above wouldn't deliver the result.</p> <p>I understand that I can explode each word to have individual terms, but what to I do then, this?:</p> <pre><code>HAVING (GROUP_CONCAT(tag.name) LIKE '%$tag1%' OR GROUP_CONCAT(tag.name) LIKE '%$tag2%' OR GROUP_CONCAT(tag.name) LIKE '%$tag3%') </code></pre> <p>Where <strong>$tag1</strong> = nice , <strong>$tag2</strong> = black , <strong>$tag2</strong> = shoes</p> <p>It seems to me that defining the GROUP_CONCAT x times (3 in the example) could be a bit consuming?</p> <p>Any ideas?</p> <p>Please note that I am using "<strong>HAVING</strong>" not "<strong>WHERE</strong>".</p> <p>Thanks!</p> <p><strong>UPDATE:</strong> Posting the full query:</p> <pre><code>$query = mysql_query("SELECT i.id , SUBSTRING_INDEX(GROUP_CONCAT(t.name ORDER BY t.stats_items DESC), ',', 3) as tagList FROM items AS i LEFT JOIN tag_rel AS tr ON (tr.item = i.id) LEFT JOIN tags AS t ON (t.id = tr.tag) GROUP BY i.id HAVING (GROUP_CONCAT(t.name) LIKE '$tag%') ORDER by i.DESC LIMIT 0, 120"); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload