On Fri, Mar 13, 2009 at 03:29:00PM -0700, revDAVE wrote:
> Hi Folks,
>
> I would like to make a summary report with details for a products inventory
> list - so it could list:
>
> A - the product & a few summaries like total count on 1 line
> B - ------- below that: the details of a multi line sub list with inventory
> data like:
>
> A - PRODUCT #1 - count = 25
> B ---------- id 56 - condition = good
> ------------ id 98 - condition = new
> A - PRODUCT #2 - count = 18
> B ---------- id 205 - condition = new
> ------------ id 381 - condition = poor
>
> and repeat for all inventory products....
>
> -----
>
> The way I'm doing it now is :
>
> main query #1
>
> SELECT name,model, count(prid) as thecount FROM inventory group by model
>
> then do a repeat region table to display 1 of each
>
> - then - on each row - do sub query#2 :
>
> SELECT name,model, condition (etc....) FROM inventory where model =
> quety#1.model....
>
> - then display these details in a sub table....
>
>
> Q: This seems to work fine. I was just wondering if there is a better way to
> construct queries to get the same result?
>
>
> Thanks in advance for your help...
I'm not an expert, but the way I normally do something like this is with
a join that would give name, model and condition on each row (so you
have a lot of duplicate fields which are the same for a series of
records). I make sure they're ordered properly, and then process them in
a loop. Something like:
$product = '';
$count = 0;
while ($a = fetch_array()) {
if ($product != $a['name']) {
// do whatever for prior product
// do whatever for new product
// $count will be the number of a given product
$product = $a['name'];
}
else {
$count++;
// print out whatever you need to
}
}
If anyone knows a better way to do this with just SQL, I'm interested.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
