StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql many-to-many tag implementation + full tag listing
text
Body
copied!<p>based on <a href="https://stackoverflow.com/questions/1529073/how-to-implement-tagging-system-similar-to-so-in-php-mysql">How to implement tagging system similar to SO in php/mysql?</a></p> <p>ive made small changes, and have the following image tag implementation:</p> <p><code>SELECT I.imageId, GROUP_CONCAT(DISTINCT T.tagName SEPARATOR ' '), COUNT(*)<br> FROM Images I<br> INNER JOIN ImageTagMap M ON I.imageId = M.imageId<br> INNER JOIN ImageTags T ON T.tagId = M.tagId<br> WHERE T.tagName LIKE '%new%' OR T.tagName LIKE '%yo%'<br> GROUP BY I.imageId<br> ORDER BY COUNT(*) DESC</code></p> <p>currently, this successfully gets the imageId's of all of those images that fullfil the WHERE clause, and orders them according to the most hits (no. hits being generated by the GROUP BY clause). it also gets all the matched tag names and sticks them in a single string. nice.</p> <p>what i would really like for the user, is to have all the tagNames for the relevant picture. currently the tagNames returned are only those that matched with the LIKE statements. e.g. if a picture is tagged "2010 new york" and i search (as above) "new yo" it will return imageId, "new york". but i want all the tag info for the matched image, i want imageId, "new york 2010".</p> <p>a simple solution would be to stick the above query in a subquery and rerun the join with a new WHERE clause of imageId IN( above query as subquery ). although that seems silly (even if the optimiser might do magic with it), surely i dont need to rerun the exact same join twice? or do i?</p> <p>as an aside, is there a better way to search tags than the bunch of LIKE's ive stuck in?</p>

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