php - Mysqli 带条件查询

Question

我正在使用查询来获取 json 结果。它工作正常，但现在我只想获取表引号中 qu_status=1 的结果。但我无法让它发挥作用。我的工作查询没有检查上述条件，如下所示

$sql = "SELECT q.*,c.au_picture as picture FROM tbl_quotes q INNER JOIN tbl_category c ON q.qu_author=c._auid Order By q.".$orde." Desc LIMIT ".$limit." OFFSET ".$offset;

我尝试像下面这样使用它

$sql = "SELECT q.*,c.au_picture as picture FROM tbl_quotes where qu_status=1 q INNER JOIN tbl_category c ON q.qu_author=c._auid Order By q.".$orde." Desc LIMIT ".$limit." OFFSET ".$offset;

但是我在这方面错了，所以我无法得到任何结果。如果有人可以纠正我，请告诉我。谢谢

Answer 1

where 子句必须位于连接之后

$sql = "SELECT q.*,c.au_picture as picture 
      FROM tbl_quotes  q
      INNER JOIN tbl_category c ON q.qu_author=c._auid 
      where q.qu_status=1  
      Order By q.".$orde." Desc LIMIT ".$limit." OFFSET ".$offset;

或者你可以直接在 join 中避免 where

$sql = "SELECT q.*,c.au_picture as picture 
       FROM tbl_quotes q
       INNER JOIN tbl_category c ON q.qu_author=c._auid and q.qu_status=1  

  Order By q.".$orde." Desc LIMIT ".$limit." OFFSET ".$offset;