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_id’s 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
}
}
?>
I’ve been able to get the cart_id’s 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
