dongnunai3125
2012-04-17 18:22
浏览 47
已采纳

PHP和MySQL,这个函数有什么问题?

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

图片转代码服务由CSDN问答提供 功能建议

此函数不起作用,我找不到问题,它位于查询内部。

  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`,
`poss.`post_title` AS`title`,
`poss.`post_category`  AS`category`,
`poss.``post_body` AS`preview`,
`post``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`post``nWHERE`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`post``post_id`  =`comments` .post_id` 
ORDER BY`post`  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; 
   
 
 

} 该功能正常,没有 WHERE category ='“。$ category。”',但是,它返回

你有 SQL语法中的错误; 查看与MySQL服务器版本对应的手册,以便在'LEFT JOIN(SELECT post_id ),COUNT( comment_id )AS total_comments ,'第12行

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanbinmi8970 2012-04-17 18:25
    已采纳

    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>
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douxin0251 2012-04-17 18:27

    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}";
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题