Databases
StackOverflow2013
Postsdbo
POMySQL search for item with all tags
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL search for item with all tags
    primarykey
    data
    text
    <p>I'm working on a search engine for an online library, but I'm kind of stuck here. When searching for tags, OR searches (ie books with "tag1" OR "tag2") work fine, but the AND searches are giving me some trouble.</p> <p>The tables (and their columns) I use for this are:</p> <pre><code>books | book_id, other_info tagmap | map_id, book_id, tag_id tags | tag_id, tag_text </code></pre> <p>Since a bunch of other search options can be en/disabled by the user, the query is generated by PHP. When searching for books with the tags "tag1" AND "tag2", the following query is generated:</p> <pre><code>SELECT DISTINCT b.book_id, b.other_info FROM books b, tagmap tm, tags t WHERE b.book_id = "NA" OR ( (t.tag_text IN ("tag1", "tag2")) AND tm.tag_id = t.tag_id AND b.book_id = tm.book_id ) HAVING COUNT(tm.book_id)=2 </code></pre> <p>The WHERE line (which doesn't give any results) is there so that additional parameters may be strung to the query more easily. I know this can be handled a lot nicer, but for now that doesn't matter.</p> <p>When doing an OR search (same query but without the HAVING COUNT line), it returns the two books in the database that have either of those tags, but when searching for the one book in the database that has BOTH tags, it returns nothing.</p> <p>What's wrong with the query? Is this not the/a way to do it? What am I overlooking?</p> <p>Thanks!</p> <p>EDIT: As per request, the data from each table relating to the book that should be returned:</p> <pre><code>books table: book_id 110 tagmap table: book_id 110 110 tag_id 15 16 tags table: tag_id 15 16 tag_text tag1 tag2 </code></pre> <p><strong>SOLUTION</strong>: All I had to do was include</p> <pre><code>GROUP BY b.book_id </code></pre> <p>before the HAVING COUNT line. Simple as that. The answer provided by taz is also worth looking into, especially if you're aiming for optimising your search queries.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFang
    1. USFang
    plurals
    1. This table or related slice is empty.
    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
    1. This table or related slice is empty.
    1. COCan you post the data from the tables that should be displaying?
      singulars
      1. POMySQL search for item with all tags
      1. UStaz
    2. COAlso, you should use inner joins instead of a comma-separated list of table names. It is very easy to make mistakes and sometimes to compute a large cartesian product with the list syntax!
      singulars
      1. POMySQL search for item with all tags
      1. UStaz
    3. COThe only thing that will be displaying is other information about the book, which I summarised in the other_info column. Or do you mean all data involved in running the query?
      singulars
      1. POMySQL search for item with all tags
      1. USFang
 

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