473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

searching by tags

I have three tables: `photos`, `tags` and `tags_photos`.

The `photos` table contains a unique ID and a bunch of other stuff, the
`tags` table contains a unique ID and a tag name, and the `tags_photos`
contains the photo ID and the tag ID for an association between a tag
and a photo.

In other words, any photo may have many tags and any tag may have many
photos.

This is great for filtering photos based on tag, but I'm now trying to
allow users to search photos on multiple tags. I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".

I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.

Any ideas?

Jul 23 '05 #1
7 1967
Jasper Bryant-Greene wrote:
I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".
Using the IN predicate gives you the latter case, but not the former.
I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.


Here are two possible solutions:

1. Using a DISTINCT query modifier:

SELECT DISTINCT a.*
FROM photos AS a, tags_photos AS b
WHERE b.photo = a.id AND b.tag in ($tags)

2. Using a subquery:

SELECT a.*
FROM photos AS a
WHERE a.id IN (
SELECT b.photo
FROM tags_photos AS b
WHERE b.tag IN ($tags))

Regards,
Bill K.
Jul 23 '05 #2
Thanks Bill, the DISTINCT modifier worked perfectly for the "any" case.
Does anyone have any ideas for how to implement the "all" case?

I've been playing around but can't seem to get on the right track.

Jul 23 '05 #3
Jasper Bryant-Greene wrote:
Thanks Bill, the DISTINCT modifier worked perfectly for the "any" case.
Does anyone have any ideas for how to implement the "all" case?


This one is a little more tricky. I don't think you can avoid using a
subquery here.

SELECT DISTINCT a.*
FROM photos AS a, tags_photos AS b
WHERE b.photo = a.id AND b.tag in ( $tags )
AND NOT EXISTS (
SELECT 1
FROM tags AS t LEFT OUTER JOIN tags_photos AS b2
ON (t.id = b2.tag AND b2.photo = a.id)
WHERE t.id IN ( $tags ) AND b2.tag IS NULL)

Regards,
Bill K.
Jul 23 '05 #4
Hi Bill

Thanks for that. I get the following error though:

You have an error in your SQL syntax. Check the manual that corresponds
to your MySQL server version for the right syntax to use near 'EXISTS (
SELECT 1 FROM tags AS t LEFT OUTER JOIN tags_photos AS

Query: SELECT DISTINCT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN (1, 3) AND NOT EXISTS ( SELECT 1 FROM tags AS
t LEFT OUTER JOIN tags_photos AS b2 ON (t.id = b2.tag AND b2.photo =
a.id) WHERE t.id IN (1, 3) AND b2.tag IS NULL )

mysqld Ver 4.0.24 for pc-linux-gnu on i686 (Gentoo Linux
mysql-4.0.24-r1)

Cheers,
Jasper

Jul 23 '05 #5
On 14/06/2005, Bill Karwin wrote:
Jasper Bryant-Greene wrote:
Thanks Bill, the DISTINCT modifier worked perfectly for the "any"
case. Does anyone have any ideas for how to implement the "all"
case?


This one is a little more tricky. I don't think you can avoid using
a subquery here.


Wouldn't this work?

USE test;
DROP TABLE IF EXISTS photos;
CREATE TABLE photos (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100)
);

INSERT INTO photos (id, name) VALUES
(1, 'My favourite pet'), (2, 'My house'), (3, "Girlfriend's cat");

DROP TABLE IF EXISTS tags;
CREATE TABLE tags (
tagname VARCHAR(10) NOT NULL,
photo_id INT NOT NULL,
PRIMARY KEY (tagname, photo_id)
);

INSERT INTO tags (tagname, photo_id) VALUES
('pets', 1), ('pets', 3), ('dogs', 1), ('cats', 3), ('buildings', 2);

SELECT
photos.id,
photos.name
FROM photos
JOIN tags ON tags.photo_id = photos.id
WHERE
tags.tagname IN ('pets', 'dogs')
GROUP BY photos.id
HAVING COUNT(*) = 2; # 2 = number of tags
--
felix
Jul 23 '05 #6
Jasper Bryant-Greene wrote:
mysqld Ver 4.0.24


Okay, that explains it. Subqueries were not implemented in MySQL 4.0.x.
They were added in MySQL 4.1. You'll have to use a solution like
Felix's, adapted to your many-to-many table `tags_photos`.

Regards,
Bill K.
Jul 23 '05 #7
Thanks Felix and Bill, I've sorted it out using a solution based on
Felix's but adapted slightly for my many-to-many table.

Once I know whether I'll be using my own server or hosting this
application somewhere, I may well upgrade to MySQL 4.1 anyway.

Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: SK | last post by:
Is there a way to store HTML into a MySQL TEXT column, yet be able to search over textual content only? For example, if I store the following HTML snippet: <p>A very <em>small</em>...
4
by: ash | last post by:
I want search phrase in html file and mark (like Google when I click on "cached") it (phrase). Does somebody know any class, that can help me? Maybe somebody know how could I make this? thanks
0
by: Tom | last post by:
Hi, Does anyone know if MS Indexing Service searches meta tags. I have a requirement to search meta tags. cheers. John.
7
by: Nash Kabbara | last post by:
Hi all, I just finished writing a log reader that reads xml logs (about 1 to 2 MB large). At the command line you can specify the file name, the name of the element and it's value like so:...
14
by: Ludwig77 | last post by:
I read that there are some tags that can be entered in a web page's meta tags in order to prevent web bot searching and indexing of the web page for search engines. What is the tagging that I...
2
by: anand | last post by:
Hello Group , I am making a search programe in which i have to make a search in an HTML document .Now i am having entire html code in a field of table where i search.Now my issue is that i want to...
2
by: neilio2j | last post by:
hi, can anyone tell me how to search a given directory using C++? so far i have been successful in searching a file named, for tags"h:\\tags.cpp", whereas i would like to search for all files...
0
by: skumar2008 | last post by:
This may not be the right forum for this question so my appologies in advance. But I turn to this group as a last resort... To give an example of my problem/question: Suppose I have a database...
1
by: alamodgal | last post by:
hiiiiiii I have a problem in highlighting searching keyword.Actually im using this function for searching Public Function HighLight(ByVal Keyword As String, ByVal ContentFor As String) Dim...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.