Topic: Need help with reducing queries

[Zergman]

So I need to retrieve the totals of certain queries from the same DB table.  Basically need to display the total records for certain conditions.

For example, here's part of the queries

Code: [Select]

$query_rsToday1 = "
SELECT data_id
FROM data
WHERE tdate = '$date'
AND resolution = 'Resolved'
";
$rsToday1 = mysql_query($query_rsToday1, $cnepix) or die(mysql_error());
$totalRows_rsToday1 = mysql_num_rows($rsToday1);

$query_rsToday2 = "
SELECT data_id
FROM data
WHERE tdate = '$date'
AND resolution = 'Dispatch'
";
$rsToday2 = mysql_query($query_rsToday2, $cnepix) or die(mysql_error());
$totalRows_rsToday2 = mysql_num_rows($rsToday2);

$query_rsToday3 = "
SELECT data_id
FROM data
WHERE tdate = '$date'
AND level1 = 'Abandon'
";
$rsToday3 = mysql_query($query_rsToday3, $cnepix) or die(mysql_error());
$totalRows_rsToday3 = mysql_num_rows($rsToday3);
I just then display the counts like

Code: [Select]

<?php echo $totalRows_rsToday1; ?>
There has got to be an easier way to condense the queries but I just can't figure it out.

[Zergman]

Tried this, but its not working.

Code: [Select]

SELECT level1, level2, resolution, count( * ) AS "total"
FROM `NS_data`
WHERE tdate = '$date'
GROUP BY level1, level2, resolution
ORDER BY level1, level2, resolution
Perhaps i'm not displaying it right

[ignace]

$query = "SELECT data_id, resolution
FROM data
WHERE tdate = '$date' 
AND resolution IN ('Resolved', 'Dispatch')
ORDER BY resolution";

$result = mysql_query($query);
if ($result) {
    $resolved = array();
    $dispatch = array();
    while ($row = mysql_fetch_assoc($result)) {
        if ($row['resolution'] === 'Resolved') {
            $resolved[] = $row;
        } else if ($row['resolution'] === 'Dispatch') {
            $dispatch[] = $row;
        }
    }

    //use $resolved, $dispatch
}

[Zergman]

Great stuff!

Only problem im having is when I use $resolved and $dispatch, the output I get is Array and not the actual value.

I'm stumped