PHP and MySQL, what's wrong with this function?

Question

This function doesn't work and I can't find the problem, which is somewhere inside the query.

function get_category_posts($category, $page, $per_page) {
$start = (int)($page - 1) * $per_page;
$per_page = (int)$per_page;

$sql = "SELECT
    `posts`.`post_id` AS `id`,
    `posts`.`post_title` AS `title`,
    `posts`.`post_category` AS `category`,
    `posts`.`post_body` AS `preview`,
    `posts`.`post_user` AS `user`,
    DATE_FORMAT(`posts`.`post_date`, '%Y-%m-%d %H:%i:%s') AS `date`,
    `comments`.`total_comments`,
    DATE_FORMAT(`comments`.`last_comment`, '%Y-%m-%d %H:%i:%s') AS `last_comment`
FROM `posts`
WHERE `category`='".$category."'
LEFT JOIN (
    SELECT
        `post_id`,
        COUNT(`comment_id`) AS `total_comments`,
        MAX(`comment_date`) AS `last_comment`
    FROM `comments`
    GROUP BY `post_id`
) AS `comments`
ON `posts`.`post_id` = `comments`.`post_id`
ORDER BY `posts`.`post_date` DESC
LIMIT {$start}, {$per_page}";

$posts = mysql_query($sql);
//die(mysql_error());

$rows = array();
while (($row = mysql_fetch_assoc($posts)) !== false) {
    $rows[] = array(
        'id' => $row['id'],
        'title' => html_entity_decode($row['title']),
        'category' => html_entity_decode($row['category']),
        'preview' => html_entity_decode($row['preview']),
        'user' => html_entity_decode($row['user']),
        'date' => $row['date'],
        'total_comments' => ($row['total_comments'] === null) ? 0 : $row['total_comments'],
        'last_comment' => ($row['last_comment'] === null) ? 'aldrig' : $row['last_comment']
        );
}

return $rows;

} The function works fine without the WHEREcategory='".$category."', however, with it it returns

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 'LEFT JOIN ( SELECT post_id, COUNT(comment_id) AS total_comments, ' at line 12

Mikey · Accepted Answer · 2012-04-17 18:30:19Z

5

The WHERE clause must come after the table joins and before the ORDER BY and LIMIT

SELECT <column list>
FROM <table>
JOIN <table> USING (<column>)
WHERE <condition>
OR <condition>
AND <condition>
GROUP BY <column list>
HAVING <expression>

edited Apr 17, 2012 at 18:30

answered Apr 17, 2012 at 18:25

Mikey

2,95134 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Jonsson Over a year ago

If I put it after the join, I get this: "Unknown column 'category' in 'where clause'"

Mikey Over a year ago

thats because category isn't in the table - it's just a column alias, try using posts.post_category instead

Starx · Accepted Answer · 2012-04-18 01:07:33Z

2

JOINs have to come before WHERE condition

$sql = "SELECT
    `posts`.`post_id` AS `id`,
    `posts`.`post_title` AS `title`,
    `posts`.`post_category` AS `category`,
    `posts`.`post_body` AS `preview`,
    `posts`.`post_user` AS `user`,
    DATE_FORMAT(`posts`.`post_date`, '%Y-%m-%d %H:%i:%s') AS `date`,
    `comments`.`total_comments`,
    DATE_FORMAT(`comments`.`last_comment`, '%Y-%m-%d %H:%i:%s') AS `last_comment`
FROM `posts`
LEFT JOIN (
    SELECT
        `post_id`,
        COUNT(`comment_id`) AS `total_comments`,
        MAX(`comment_date`) AS `last_comment`
    FROM `comments`
    GROUP BY `post_id`
) AS `comments`
ON `posts`.`post_id` = `comments`.`post_id`
WHERE `posts`.`category`='".$category."'
ORDER BY `posts`.`post_date` DESC
LIMIT {$start}, {$per_page}";

edited Apr 18, 2012 at 1:07

answered Apr 17, 2012 at 18:27

Starx

79.3k50 gold badges187 silver badges266 bronze badges

1 Comment

Peter Jonsson Over a year ago

I get this then: "Unknown column 'category' in 'where clause'".

Collectives™ on Stack Overflow

PHP and MySQL, what's wrong with this function?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related