complex select query result help

[Anon]

Hope someone can help with the following query:
a)I want to extract data from several rows by date range.
b)I want to add + the results.
c)I want to display the totals by/for each State and this is where I am stuck !!

$result = mysql_query("select SubTotal,Tax,TotalCost,State from Taxes WHERE Date>="'$startdate' and Date<='$enddate'");
while($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
$j=mysql_num_fields($result);
for($i=0;$i<$j;$i++) {
$k=mysql_field_name($result,$i);
$$k=$row[$k];
}
$Totalsub+=$SubTotal;
$Totaltax+=$Tax;
$Ttotal+=$TotalCost;
}

Many thanks,
Peter Morrissette

[[deleted]]

a) you've got that
b) read about SUM() in the MySQL manual
c) read about GROUP BY in the MySQL manual

[tomhath]

Along with the good advice to read the mysql manual regarding SUM and GROUP BY, you might want to read what the php manual says about associative arrays.

Your "for" loop with a variable variable is really the hard way of doing whatever it is you tried to do (you might've gotten this to work if you had kept track of what "State" the rows had, but that's why vincent said to use the GROUP BY).

[Anon]

I have worked a little with associative arrays but seems like there should be a quicker way.
Looked at the MySQL dos about SUM() & GROUP BY() but I didn't realy get, so perhaps I am a little slow.

Tried many variation of the following tests without any luck !
could use a few hints !!

$result = mysql_query("select Tax, SUM(Tax) from taxes GROUP BY State");
// $result = mysql_query("select Tax, from taxes GROUP BY State and SUM(Tax)");
// $result = mysql_query("select SUM(Tax) from taxes GROUP BY State");

while($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
$j=mysql_num_fields($result);
for($i=0;$i<$j;$i++) {
$k=mysql_field_name($result,$i);
$$k=$row[$k];
}

        echo "$State : $SubTotal, $Tax, $TotalCost, $SUM<BR>";

}

Thanks,
Peter

[[deleted]]

I suggest you start using the commandline to try your queries without PHP so you can see just what it is returning.