Harry Harry - 6 months ago 8
SQL Question

Query returning false PHP

I am having trouble with a MySQL query. The query is as follows:

SET @catlocation = (SELECT id FROM categories WHERE url_name='hexcode');

SELECT
subs.display_name AS display,
subs.url_name AS url,
(
SELECT title
FROM threads
WHERE location = subs.id
ORDER BY time_submitted DESC
LIMIT 1
) AS title,
(
SELECT username
FROM users
WHERE uid = (
SELECT uid
FROM threads
WHERE location = subs.id
ORDER BY time_submitted DESC
LIMIT 1
)
LIMIT 1
) AS author,
(
SELECT COUNT(*)
FROM threads
WHERE location = subs.id
ORDER BY time_submitted DESC
LIMIT 1
) AS thread_count

FROM (
SELECT *
FROM categories
WHERE parent_id = @catlocation
) AS subs


When I try to run this through PHP I get a false result and an error of:


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 'SELECT subs.display_name AS display, subs.url_name AS url, ( SELECT threads.' at line 7


I have no idea what the syntax error could be, if someone could point it out to me that would be wonderful.

EDIT: Could this be caused by having two select statements (The one that sets
@catlocation
and the main query?)

Answer

You can refactor your request with joins to increase performance.

SELECT s.display_name display, s.url_name url,
       t1.title, u.username author,
       COUNT(t2.title) thread_count
FROM categories s
JOIN threads t1 ON t1.id = (SELECT id FROM threads
                            WHERE location = s.id
                            ORDER BY time_submitted DESC
                            LIMIT 1)
JOIN users u ON u.uid = t1.uid
JOIN threads t2 ON t2.location = s.id
WHERE s.parent_id = @catlocation
GROUP BY display, url, author

In t1.id = (SELECT id FROM threads ... ) replace with the correct id of the table threads.

Comments