Databases
StackOverflow2013
Postsdbo
POMySQL tag search
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL tag search
    primarykey
    data
    text
    <p>I have a table with images and tags in it (called <code>tbl_images</code>)</p> <pre><code>image_file image_tags ---------- ---------------------------- test.png tag1 another_tag tag2 hi.jpg tag9 tag1 another_tag qwerty </code></pre> <p>How would I do it so that:</p> <ul> <li>searching 'another' returns nothing</li> <li>searching 'tag1' returns the test.png row</li> <li>searching 'tag1 another_tag' returns the test.png row</li> <li>searching 'qwerty tag2' returns nothing</li> </ul> <p>?</p> <p>As in, the ability to search using the <em>full</em> tag name and to search multiple tags at the same time if they all occur in the same image_tags field in the same row. Note:-</p> <ul> <li>I'm only searching within one field</li> <li>Its being done with a PHP search input (retrieved in <code>$_GET['search_terms']</code>)</li> <li>I can't do <code>LIKE '%tag9%</code> because tag9 could appear in a larger tag, such as <code>Atag98</code>, and as I said I would only like the <em>exact</em> tag match.</li> <li>I can't do <code>LIKE ' tag9 '</code> (note the space before and after,) because tag9 may appear at the beginning of the image_tags field (where there is no space, so there would be no match)</li> <li>I can't do <code>LIKE 'tag9 another_tag'</code> because I don't know if another_tag comes after tag9, similarly I can't do <code>LIKE 'tag9%another_tag'</code> for the same reason; also tag9 could be part of a larger tag (as mentioned previously.)</li> </ul> <p>Also, is fulltext search appropriate for this? And if so, could you provide an example please?</p> <p>A tricky problem (for me anyway;) any help would be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USq3d
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL tag search
      1. This table or related slice is empty.
      1. VTDownMod
    1. COAre you set on this table design? Ideally, you would put the image tags into a junction table along with the image id, then do a join to pull it out. In that way, MySQL doesn't have to use the intensive LIKE to parse through individual rows - http://stackoverflow.com/questions/5812680/many-to-many-relationship
      singulars
      1. POMySQL tag search
      1. UScitizenen
    2. COIt doesn't seem to work for me. How would I use a query to find the results? I tried: `SELECT * FROM images JOIN tags ON tags.image_id = images.id WHERE tag = 'hello'` which returned images with the hello tag, but this doesn't work when I have multiple tags. `WHERE tag = 'hello' OR tag = 'flowers'` works if the image has the 'hello' and 'flowers' tags next to its id in the tags table. But `WHERE tag = 'hello' OR tag = 'tag_doesnt_exist'` returns the row with the image in it, even if the image does not have the 'tag_dosent_exist' tag.
      singulars
      1. POMySQL tag search
      1. USq3d
    3. COIf you want the image to have both tags, use `AND`: `WHERE tag = 'hello' AND tag = 'tag_doesnt_exist'`
      singulars
      1. POMySQL tag search
      1. USbfavaretto
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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