Re: Working on a Subsummary Report

[Jim Lucas <lists@xxxxxxxxx>]

On 12/17/2011 4:21 PM, DealTek wrote:
> 
> On Dec 16, 2011, at 12:56 PM, Jim Lucas wrote:
>>
>>
>> 1) What does your db schema look like?
>> 2) What SQL do you currently use?
>> 3) What criteria do you want to use to sort the data?
>> 4) Will the output be plaintext, html, etc?
>> 5) Is this going to be used to import into another app, or display & printing?
>>
> 
> Hi Jim - sorry I didn't see this earlier...
> 
> 
> 1 - schema - think of a basic 2 table system  parent table and line items table... - but really all the important fields are in the child line items...
> 2 - mysql 5.xx
> 3 - sort 1st by date (all records happen 1st of every month) then product (only 2 products needed for this)
> 4 - html for now
> 5 - for now just display and printing like:
> 
> 
> JAN 2011
> --- PRODUCT 1
> 
> data row 1
> data row 2
> 
> --- PRODUCT 2
> 
> data row 3
> data row 4
> 
> like that.......
> 
> 
> - thanks for checking this out....
> 
> 
> 
> 
> --
> Thanks,
> Dave - DealTek
> dealtek@xxxxxxxxx
> [db-11]

Well, by the sounds of it, you are using a join to combine the two tables into
one result set and you have doubling up on the parent information.

I would do it like this.

$SQL = 'SELECT p_id, month_name, prod_id, l_id, descr';

$results = query($SQL);

while ( $r = fetch_assoc($results) ) {
  $data[$r['p_id']]['descr'] = $r['month_name'];
  $data[$r['p_id']]['x'][$r['prod_id']]['descr'] = $r['descr'];
  $data[$r['p_id']]['x'][$r['prod_id']]['x'][$r['l_id']] = $r;
}

print_r($data);

Now, use instead of looping through the result set multiple times, us the
$dataSet array to loop through it once.

foreach ( $data AS $months ) {
  echo "{$months['month']}\n";
  foreach ( $months['x'] AS $prod_id => $products ) {
    echo "--- {$products['descr']} #{$prod_id}\n\n";
    foreach ( $products['x'] AS $product ) {
      echo str_pad($product['l_id'], 16, ' ', STR_PAD_LEFT), '  ',
           str_pad($product['descr'], 32, ' ', STR_PAD_RIGHT), PHP_EOL;
    }
    echo PHP_EOL;
  }
  echo PHP_EOL;
}

YMMV - This is completely untested,  It should give you an idea about how to
prepare/store the data and then display it.

-- 
Jim Lucas

http://www.cmsws.com/
http://www.cmsws.com/examples/
http://www.bendsource.com/

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php