Giant query, Weird Error
Posted: Fri Mar 23, 2007 8:16 pm
Code: Select all
SELECT images.*, COUNT(tags.tag) as `tagtablecount` FROM `images`
LEFT JOIN `tags` ON images.id = tags.fileid
GROUP BY images.id WHERE (
`tagtablecount` > '8' OR
(`tagtablecount` = '8' AND images.rating > '87') OR
(`tagtablecount` = '8' AND images.rating = '87' AND images.views > '35') OR
(`tagtablecount` = '8' AND images.rating = '87' AND images.views = '35' AND images.id > '586')
) ORDER BY `tagtablecount` ASC, images.rating ASC, images.views ASC, images.id ASC
LIMIT 2;Code: Select all
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 'WHERE (`tagtablecount` > '8' OR (`tagtablecount` = '8' AND images.rating > '87') OR (' at line 1Code: Select all
SELECT images.*, COUNT(tags.tag) as `tagtablecount` FROM `images`
LEFT JOIN `tags` ON images.id = tags.fileid
WHERE (
`tagtablecount` > '8' OR
(`tagtablecount` = '8' AND images.rating > '87') OR
(`tagtablecount` = '8' AND images.rating = '87' AND images.views > '36') OR
(`tagtablecount` = '8' AND images.rating = '87' AND images.views = '36' AND images.id > '586')
) GROUP BY images.id ORDER BY `tagtablecount` ASC, images.rating ASC, images.views ASC, images.id ASC
LIMIT 2;Code: Select all
Unknown column 'tagtablecount' in 'where clause'Code: Select all
SELECT images.*, COUNT(tags.tag) as tags.tagtablecount FROM `images`
LEFT JOIN `tags` ON images.id = tags.fileid
WHERE (
tags.tagtablecount > '8' OR
(tags.tagtablecount = '8' AND images.rating > '87') OR
(tags.tagtablecount = '8' AND images.rating = '87' AND images.views > '39') OR
(tags.tagtablecount = '8' AND images.rating = '87' AND images.views = '39' AND images.id > '586')
) GROUP BY images.id ORDER BY tags.tagtablecount ASC, images.rating ASC, images.views ASC, images.id ASC
LIMIT 2;Code: Select all
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 '.tagtablecount FROM `images` LEFT JOIN `tags` ON images.id = tags.fileid WHERE ' at line 1I'm generating a list of images sorted by how many tags each has. (And then sorted by rating, views, and id).
THIS code is to figure out the two previous entries in the list (assuming you're moving down the list).
The list generation query is:
Code: Select all
SELECT {$_CATTABLE}.*, COUNT({$_TAGTABLE}.tag) as `tagtablecount` FROM `{$_CATTABLE}`
LEFT JOIN `{$_TAGTABLE}` ON {$_CATTABLE}.id = {$_TAGTABLE}.fileid GROUP BY {$_CATTABLE}.id
ORDER BY `tagtablecount` DESC, {$_CATTABLE}.rating DESC, {$_CATTABLE}.views DESC, {$_CATTABLE}.id DESC
LIMIT 100;tables:
`images`
id, file, rating, views
`tags`
id, fileid, tag
Ideas?