Using the count(*) feature for multiple options.

[DBookatay]

On my index page (in the employee only section) of my url I have a stats section, that counts values of our dB. Problem is that there are many many values that it counts, based on the business year.

It counts sales based on salesman, month, make (Audi, Ford, Honda, ect.), and so on and so on... The (edited down) "count" code I have now is:[code]$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Brad'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_BG = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Charles'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_CG = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Doug'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_DD = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Jerry'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_JF = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Joe'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_JC = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' salesman = 'Jennifer'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_JL = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '01'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_01 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '02'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_02 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '03'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_03 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '04'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_04 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '05'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_05 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '06'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_06 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '07'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_07 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '08'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_08 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '09'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_09 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '10'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_10 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '11'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_11 = $row[thecount];
$query = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' sold_dte1 = '12'"; $result = mysql_query($query); $row = mysql_fetch_array($result); $stats_12 = $row[thecount];[/code]

Is there a way to simplify all of this?

[Orio]

Arrays and foreach loops....

Run this:

[code]<?php

$sold_dtel = array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12");
$salesman = array("Brad", "Charles", "Doug", "Jerry", "Joe" "Jenifer");

$basic = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' AND ";

$stats = array();
foreach ($sold_dtel as $key => $val)
{
$row = mysql_fetch_array(mysql_query($basic."sold_dtel = '$val'"));
$stats[$key] = $row['thecount'];
}
foreach ($salesman as $key => $val)
{
$row = mysql_fetch_array(mysql_query($basic."salesman = '$val'"));
$stats[$key] = $row['thecount'];
}

echo "<pre>";
print_r($stats);
echo "</pre>";

?>[/code]

Orio.

[DBookatay]

I get an error: PHP Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ')' in C:\Documents and Settings\Stan Miller\Desktop\My Portfolio\Car City\login\passed\index.php on line 9

Line 9 being:

$salesman = array("Brad", "Charles", "Doug", "Jerry", "Joe" "Jenifer");

[Orio]

A little parse error Forgot a comma, also made a few minor tweaks.

Try now:

 

<?php

$sold_dtel = array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12");
$salesman = array("Brad", "Charles", "Doug", "Jerry", "Joe", "Jenifer");

$basic = "SELECT count(*) as thecount from sold where sold_dte3 = '{$_POST['sold_dte3']}' AND ";

$stats = array();
foreach ($sold_dtel as $val)
{
$row = mysql_fetch_array(mysql_query($basic."sold_dtel = '$val'"));
$stats[$val] = $row['thecount'];
}
foreach ($salesman as $val)
{
$row = mysql_fetch_array(mysql_query($basic."salesman = '$val'"));
$stats[$val] = $row['thecount'];
}

echo "<pre>";
print_r($stats);
echo "</pre>";

?>

 

Orio.

[rantsh]

GROUP... You could try and group by salesman instead of using the WHERE and then just handle the array

[Jenk]

holy insecurity batman

[Nhoj]

I just thought I'd let you know that using count(0) instead of count(*) can in most cases drastically reduce the time required to execute the query.