目前我正在运行这样的MySQL查询
$query = "
SELECT
t1.id AS 'post_id',
t1.post_title AS 'post_title',
t1.post_status AS 'post_status',
COUNT( t1.id ) AS 'row_count', //this one is not working
COUNT( t2.tag_id ) AS 'link_count',
GROUP_CONCAT( t2.tag_id) AS 'link_id',
GROUP_CONCAT( t2.url) AS 'url',
GROUP_CONCAT( t2.title) AS 'title',
GROUP_CONCAT( t2.active) AS 'active',
FROM $posts_table AS t1
JOIN $tags_table AS t2 ON ( t1.id = t2.post_id )
GROUP BY t1.id
ORDER BY t1.id DESC
LIMIT $start_from, $row_to_show;
";
此查询的每个部分都执行良好,除了COUNT( t1.id ) AS 'row_count',
行。
我想计算在 t1 表中找到的行总数,但是当我转储数组时
$result = $wpdb->get_results($query);
var_dump($result->row_count);
它给了我NULL
.
如果我在循环中使用它
foreach ( $result as $result ){
echo $result->row_count;
}
然后该值与下一行中声明的link_count
相同COUNT( t2.link_id ) AS 'link_count',
该值给出了我想要的值。
请告诉我如何在此查询中使用分页(例如 30 个结果中的 10 个)。
我认为问题出在GROUP BY。
请尝试以下查询:
$query = "
SELECT
t1.id AS 'post_id',
t1.post_title AS 'post_title',
t1.post_status AS 'post_status',
COUNT( t1.id ) AS 'row_count',
COUNT( t2.tag_id ) AS 'link_count',
GROUP_CONCAT( t2.tag_id) AS 'link_id',
GROUP_CONCAT( t2.url) AS 'url',
GROUP_CONCAT( t2.title) AS 'title',
GROUP_CONCAT( t2.active) AS 'active',
FROM $posts_table AS t1, $tags_table AS t2
GROUP BY t1.id
HAVING t1.id = t2.post_id
ORDER BY t1.id DESC
LIMIT $start_from, $row_to_show;
";
像COUNT( DISTINCT X ) .. GROUP BY X
这样的问题总是会导致值1,如果你跳过DISTINCT 你会得到组合连接的数量
有一些方法可以在MySQL中获取计数,但是在PHP中使用mysql_num_rows()
或count()
要容易得多。
$results = $wpdb->get_results($query);
foreach($results as $result)
{
$result->row_count = count($results);
...
}
上面只显示获取的行数,如果你想要总数,你需要使用SQL_CALC_FOUND_ROWS
和mysql_num_rows()
$query = "SELECT SQL_CALC_FOUND_ROWS ...
和
$result_count = mysql_num_rows();
我尝试了很多但没有成功,所以我只为分页做了另一个单独的数据库查询。
$page_query = "
SELECT p.id
FROM
$posts_table as p,
$tag_table as c
WHERE p.id=c.post_id
GROUP BY p.id
";
它为我提供了可用于分页的行总数的结果。