Databases
StackOverflow2013
Postsdbo
POmysql php match multiple keywords
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql php match multiple keywords
    primarykey
    data
    text
    <p>I have three tables which are currently structured in the following way</p> <pre><code>Table: Images image_id image_title ... Table: Keywords keyword_id keyword Table: Image_Keyword image_id keyword_id </code></pre> <p>With this structure, I'm able to search if any images match any keywords using joins and or statements - however I would like to be able to retrieve images that have multiple keywords matches e.g. "keyword = ('red' or 'dress') and 'night'" - which would return all images that had either 'red' or 'dress' in them, alongside night.</p> <p>Ideally I want to allow the user to be able to specify the AND and OR commands in the search box, which is why I have so far opted out of making separate joins for each new keyword - however I'm not sure how to proceed with the structuring of the query. </p> <p>Currently I have the following, without the 'and' implementation:</p> <pre><code>SELECT i.* FROM images i JOIN image_keyword ik ON i.id = ik.image_id JOIN keywords k ON k.id = ik.keyword_id WHERE k.keyword IN ('night','red') </code></pre> <p>Any help on how to go about creating the 'and' portion of this query would be greatly appreciated! Thanks kindly,</p> <p>Dan</p> <p>// UPDATE</p> <p>So it looks as if I am going to have to do it by creating joins for each 'AND' request that I need to sort out - however I have an extension on the requirements now...</p> <p>I have two other tables which follow the following structure</p> <pre><code>Table ImageData id image_id caption_id ... Table Caption id data (text) </code></pre> <p>In this instance, I would want to search for the keywords ('red','dress' and 'night'), using the same 'AND' and 'OR' capability as before, but also return the image if the text matches (using the same rules) in the caption data field. I would assume I potentially use an OR after the 'keyword' search, and then use a fulltext search on the caption, however I don't know if there is a cleaner way of combining the two, maybe even as two separate queries and then choosing the distinct results - which might allow for instances where the AND is successful in the keywords, and the OR is successful in the caption.</p> <p>Any thoughts would be fantastic</p> <p>Thanks again</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDan
    1. USDan
    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. VO
      singulars
      1. POmysql php match multiple keywords
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysql php match multiple keywords
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POmysql php match multiple keywords
      1. USEndophage
      1. VTFavorite
    1. This table or related slice is empty.
 

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