[SOLVED] More Efficient Query?

[jxrd]

Hello,

 

For my blog, the task of finding out how many entries there are is given to this monster:

$sql = $mysql->query("SELECT * FROM `$tb_Blog` WHERE `Type`='entry';") or $mysql->trigger_error();
$entry_count = $mysql->count_rows($sql);

$vsql = $mysql->query("SELECT * FROM `$tb_Blog` WHERE `Status`='visible' AND `Type`='entry';") or $mysql->trigger_error();
$visible_count = $mysql->count_rows($vsql);
$psql = $mysql->query("SELECT * FROM `$tb_Blog` WHERE `Status`='private' AND `Type`='entry';") or $mysql->trigger_error();
$private_count = $mysql->count_rows($psql);
$hsql = $mysql->query("SELECT * FROM `$tb_Blog` WHERE `Status`='hidden' AND `Type`='entry';") or $mysql->trigger_error();
$hidden_count = $mysql->count_rows($hsql);
$dsql = $mysql->query("SELECT * FROM `$tb_Blog` WHERE `Status`='deleted' AND `Type`='entry';") or $mysql->trigger_error();
$deleted_count = $mysql->count_rows($dsql);

And obviously, that's quite a lot of queries for a relatively simple task. I was just wondering if there was a way to combine them all into one, without using something like mysqli_multi_query()?

 

I know I should use COUNT() instead, I just haven't bothered thus far.

 

So yeah, any improvements are greatly appreciated.

 

Thanks

[PFMaBiSmAd]

Are those 4 status values all of the possible status values?

 

Your query will end up looking something like this -

 

"SELECT `Status`, count(*) as cnt FROM `$tb_Blog` WHERE `Type`='entry' GROUP BY `Status` WITH ROLLUP"

This will give you a result like this -

 

Status | cnt

deleted| 5

hidden | 8

private| 3

visible | 6

NULL  | 22

 

Where row with the NULL status will be the total of the other values.

[jxrd]

Oh dude, that works awesome. Cheers.

 

Yeah, final code, for anyone who's interested

$sql = $mysql->query("SELECT `Status`, COUNT(*) AS `Count` FROM `$tb_Blog` WHERE `Type`='entry' GROUP BY `Status` WITH ROLLUP;") or $mysql->trigger_error();
$entry_count = array();
while($entry_fetch = $mysql->fetch_array($sql))
{
$entry_fetch['Status'] = ($entry_fetch['Status'] == null) ? 'total' : $entry_fetch['Status'];
$entry_count[$entry_fetch['Status']] += $entry_fetch['Count'];
}

Yeah, it seemed to return `Status` as NULL for the total, so I set it up to count a null status as total.

 

Yeah, thanks Much nicer to look at.

 

JW, what does WITH ROLLUP do? I had a look at the mysql site, but didn't really understand tbh.