Re: Working on a Subsummary Report

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@gmail.com > [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/