CODEWITHSUNDEEP
mysql
Harry
Harry

Reputation: 304

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?)

Upvotes: 1

Views: 73

Answers (3)

user6307642
user6307642

Reputation:

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) total
FROM categories s
LEFT JOIN threads t1 ON t1.id = (SELECT id FROM threads
                                 WHERE location = s.id
                                 ORDER BY time_submitted DESC
                                 LIMIT 1)
LEFT JOIN users   u  ON u.uid = t1.uid
LEFT JOIN threads t2 ON t2.location = s.id
WHERE s.parent_id = @catlocation
GROUP BY s.display_name, s.url_name, t1.title, u.username

Upvotes: 1

Harry
Harry

Reputation: 304

It appears the first SELECT statement which set @catlocation was causing the problem. I moved it into a subquery and the query executed successfully

The new query is as follows:

SELECT categories.display_name display, 
       categories.url_name url, 
       threads.title title, 
       users.username author,
       ( SELECT COUNT(title) 
         FROM threads 
         WHERE location = categories.id
       ) total
FROM categories

LEFT JOIN threads 
ON threads.tid = ( SELECT tid 
                   FROM `threads`
                   WHERE location = categories.id
                   ORDER BY time_submitted DESC 
                   LIMIT 1 )

LEFT JOIN users ON users.uid = threads.uid

WHERE categories.parent_id = ( SELECT id
                               FROM `categories`
                               WHERE url_name='hexcode' 
                               LIMIT 1 );

I will continue to refactor the query by using JOINs (once I learn how to use them). Thanks to all that suggested fixes, I didn't understand the JOIN answer and still couldn't get it to run without error.

Upvotes: 1

Ian
Ian

Reputation: 11

In a ansi SQL you need to declare a tag for each table or omit it if there is only one. Try taking out the "threads." everywhere, it is not needed

Upvotes: 1

Related Questions