extracting results when using group

[canajun]

Heres my query:

$querye = "SELECT e_group, sum(amount * quantity) as total FROM expense where e_date between '$arr1_date'".
  " and '$dep1_date' GROUP BY e_group";
$resulte=mysql_query($querye,$db_connection);
$rowe=mysql_fetch_object($resulte);

Which returns the results I want, when run in phpmyadmin,
and what I want to do with the results:

print "<br><br><table align='center' width='800' cellspacing='1'
cellpadding='5' bgcolor='#ffffff' class='main'>";

print "<tr><td valign='top' colspan='4'>
  <p align='center' class='titlefont'>
  Invoiced Sales</p></td></tr><table width=800 cellspacing='1' border='1'
   cellpadding='5' bgcolor='#ffffff' border='1' align='center'><tr>";
   print "<table align='center' width='800' cellspacing='1' border='1'
   cellpadding='5' bgcolor='#000000' class='main'>
  <tr bgcolor='#D0CBA5'>
  <td><b>Date</b></td>
  <td><b>Dep</b></td>
  <td> <b>Fish</b> </td>
  <td> <b>Misc</b> </td>
  <td> <b>Snacks</b> </td>
  <td> <b>Groceries</b> </td>
  <td> <b>Cabin</b> </td>
  <td> <b>Campsite</b> </td>
  <td> <b>Rentals</b> </td>
  </tr>";

print "<tr bgcolor='#ffffff'>".
  "<td>". $arr1_date . " to<br>" . $dep1_date . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group1) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group2) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group3) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group4) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group5) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group6) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group7) . "</td>".
  "<td>". sprintf("%.2f",$rowe->e_group8) . "</td>".
  print "</tr></table>";

Of course, as with many of my queries, I can't get from the query to the table.. can someone show where I have gone wrong?

Thanks!

 

[feherke]

Hi

Your query returns many rows with 2 columns : e_group and total. In you code you seem to fetch 1 row and want to ... And this is the part I do not understand.

Now try to explain us from where e_group1 .. e_group8 comes from and why do you have more columns in the HTML [tt]table[/tt] then you have in the SQL [tt]select[/tt].

Until than an advice to make you code readable : instead of [tt]<td><b> .. </b></td>[/tt] use [tt]<th> .. </th>[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

I have been trying to find answers on my own, and have detected a few errors myself.. perhaps this should really be an array..

so, an explanation of what I want to do:

I have various products, and 8 tax groups.

When I enter a purchase, it is stored in a mysql db, which contains price, date entered, quantity, and tax group.

I want to extract from the db, and total the grand total of each tax group, for a certain time period, hence my query:

$querye = "SELECT e_group, sum(amount * quantity) as total FROM expense where e_date between '$arr1_date'".
  " and '$dep1_date' GROUP BY e_group";

The trouble I am having is getting the results to the appropriate place in the table.

 

[feherke]

Hi

Taxes ? And what you have bolded in the [tt]td[/tt]s are the tax names ? And the rows of the [tt]select[/tt] will be returned in the matching order ?

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

Dep = group 1
Fish = group 2
Misc = group 3
Snacks = group 4
Groceries = group 5
Cabin = group 6
Campsite = group 7
Rentals = group 8

There may not always be a value assigned to a particular group in a particular time period.

Maybe it would be better to assign these values to variables.

 

[feherke]

Hi

Becomes interesting. Do you have a table of expense types ? I mean a table with those 8 values ( or other expense types too if there are ) are enumerated, always and only once.

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

Yes, there is a table of expense types, listing all types of products, which populates a drop down list on a form. Once selected and submitted, the info is passed to the table 'expense'.

But this has really nothing to do with my conundrum here.....

 

[feherke]

Hi

Yes, it has. More exactly with the part "There may not always be a value assigned to a particular group in a particular time period.". Such table could ensure that each desired group is included in the result, even if there is no value for it.

Anyway. I put together this solution, which uses an array.

$querye = "SELECT e_group, sum(amount * quantity) as total FROM expense where e_date between '$arr1_date' and '$dep1_date' GROUP BY e_group";
$resulte=mysql_query($querye,$db_connection);

$expense=array();
while ($rowe=mysql_fetch_assoc($resulte))
  $expense[$rowe["e_group"]]=sprintf("%.2f",$rowe["total"]);

echo "<h3>Invoiced Sales</h3>
<table>
<tr>
<th>Date</td><th>Dep</td><th>Fish</td><th>Misc</td><th>Snacks</td>
<th>Groceries</td><th>Cabin</td><th>Campsite</td><th>Rentals</td>
</tr>
<tr>
<td>$arr1_date to<br> $dep1_date </td>
<td>$expense['e_group1']</td>
<td>$expense['e_group2']</td>
<td>$expense['e_group3']</td>
<td>$expense['e_group4']</td>
<td>$expense['e_group5']</td>
<td>$expense['e_group6']</td>
<td>$expense['e_group7']</td>
<td>$expense['e_group8']</td>
</tr>
</table>
";

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

Hmm.. well that produces errors:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/xyz/public_html/res/admin/view_revenue.php on line 198

 

[feherke]

Hi

Well, and in that line of code is...

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

Ya.. sorry about that.. got distracted..

<td>$expense['e_group1']</td>

 

[feherke]

Hi

Oops. I mixed my habits with the intention of writing nicer code.

<td>$expense[e_group1]</td>   [gray]// no quotes, my ugly sytle[/gray]

[gray]// or[/gray]

<td>" .$expense['e_group1'] . "</td>   [gray]// as you done before[/gray]

Feherke.

http://rootshell.be/~feherke/

 

[canajun]

OK.. that fixed the error code, but it did not return any values.. so I changed :

<td>$expense[1]</td>
<td>$expense[2]</td>
<td>$expense[3]</td>
<td>$expense[4]</td>
<td>$expense[5]</td>
<td>$expense[6]</td>
<td>$expense[7]</td>
<td>$expense[8]</td>

And it worked!

Thanks very much for your help and patience!

 

[feherke]

Hi

Ah. Mis. Numeric expense identifiers was my second bet.

Feherke.

http://rootshell.be/~feherke/