help with loop and query
| From: | Aaron Wolski | Date: | Fri, 07 Feb 2003 14:19:12 +0000 |
| Subject: | help with loop and query | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-25506@lists.php.net to get a copy of this message | ||
Hi guys,
Hopefully last time I need to call of you people for this report I am
creating.
In this report there is a multiple select box like:
<select name="province[]" class="selectcontent" size="3"
multiple>
<option
value="" class="selectcontent">Province/State</option>
<option value="AK" selected
class="selectcontent">Alaska</option>
<option value="AB" selected
class="selectcontent">Alberta</option>
<option value="BC" class="selectcontent">British
Columbia</option>
<option value="CA" class="selectcontent">California</option>
<option value="CO" class="selectcontent">Colorado</option>
<option value="CT" class="selectcontent">Connecticut</option>
</select>
Using this select box
comparison data for each of the selected
provinces where an order was placed is supposed to be displayed.
For example if Alaska, Alberta and British Columbia were selected
1) Grab all cart_ids from the OrderTable for each province
2) Reference the cart_id from the OrderTable to the cart_id in the
CartTable
3) Display results and products order totals for each province
individually.
Illustration:
Product | Total Orders | Total Sold | Dollar Total
Pronvince Name: Alaska
Product 1 | 12 | 350 | $3215.45
Product 2 | 8 | 123 | $2143.23
Product 3 | 14 | 113 | $6412.43
Pronvince Name: Alberta
Product 1 | 5 | 87 | $1215.45
Product 2 | 3 | 23 | $143.23
Product 3 | 7 | 101 | $5412.43
Pronvince Name: British Columbia
Product 1 | 6 | 71 | $1015.45
Product 2 | 25 | 66 | $643.23
Product 3 | 3 | 23 | $412.43
Hopefully you can see what I am trying to do here.
Here is the code I have been working this thus far..
** CODE **
<?php
foreach ($province as $value) {
$provinceQuery = db_query("SELECT cart_id FROM
OrderTable WHERE submitted=1 AND province='$value'");
while ($provinceResult = db_fetch($provinceQuery)) {
$cartid[] = $provinceResult[cart_id];
}
?>
<tr valign="middle">
<td class="cartlink" colspan="5"><?php echo $value;
?></td>
</tr>
<?php
$province_search = "in(";
for ($i=0;$i<count($cartid);$i++) {
$province_search .=
escapeQuote($cartid[$i]);
if ($i !=
(count($cartid) - 1)) {
$province_search
.= ",";
}
}
$province_search .= ")";
$provinceCart = "AND cart_id $province_search";
$query = "SELECT name, COUNT(*) AS orders, SUM(quantity) AS
sold, SUM(quantity * price ) AS value FROM CartTable WHERE $query
$provinceCart GROUP BY name ORDER BY $orderby DESC";
$result = mysql_query($query);
$total_items = 0;
$total_orders = 0;
$total = 0.0;
while($cartResult = mysql_fetch_array($result)) {
$allrows[] = $cartResult;
$total_items += $cartResult['sold'];
$total_orders += $cartResult['orders'];
$total += $cartResult['value'];
}
foreach($allrows as $cartResult) {
?>
<tr valign="middle">
<td class="cartlink"><?php echo $cartResult["name"];
?></td>
<td class="cartlink"><?php echo $cartResult["orders"];
?></td>
<td class="cartlink" align="right"><?php echo
$cartResult["sold"]; ?></td>
<td class="cartlink" align="right"><?php echo
number_format($cartResult["value"],2); ?></td>
<td class="cartlink" align="right"><?php echo
number_format($cartResult["sold"]*100/$total_items,1); ?>%</td>
</tr>
<?php
}
}
?>
Ive been able to get the cart_ids associated with each province
selected but I am having lots of trouble displaying the result FOR the
selected $provinces.
Can anyone help me with where I am erroring?
Thanks a ton!
Aaron