Tabular Report question

jgesolutions

I am looking for an expert opinion.

I have a client that requested a tabular report of sales information i.e.:
using a grid as an example:
across the top the item numbers, top to bottom the customer names and corresponding cell the quantity purchased.

In order to do this, I queried the database for the information requested and built an array using the customer number and item number as keys to the array. I also created an array with every item number available. I then stepped through the item number array with a for/next statement for each customer.

My question is, is this the best way to do it or is there a better more appropriate method. If there is a more appropriate method, can you tell me how or point me in the right direction?

ixalmida

Depends...is there a database involved? If so, it is better to let the database do the work of retrieving data, ordering, sorting and grouping and then echo the results than to build arrays. I rarely build arrays unless I need to save and reuse the data later or I'm dealing with some unusual complexities.

However, if you're pulling data from a flat file in a certain format and you need to sort it or something, then you're probably stuck with array building.

jgesolutions

I am using mySql and everything is in the database. The issue I have run into in the past is not all customers are ordering the same products, ie one customer may order parts 1, 2, 3 and the next may order 2, 4, 5. I have not been able to display the information correctly, unless i left join the complete item table with the customer order table, thus giving me nulls in columns where the customer didn't order all of the items.

Is there a better way I am missing?

ixalmida

MySQL has many wonderful methods for weeding out NULL results. Guessing at your query, you probably want it to look something like:

SELECT customer_name, item1, item2, item3 FROM Customers JOIN Items USING(customer_id)
WHERE item1 + item2 + item3 IS NOT NULL

This query assumes the item1, item2, and item3 are all integer fields and can be null. If these fields were strings and non-NULL you'd probably want something like:

SELECT customer_name, item1, item2, item3 FROM Customers JOIN Items USING(customer_id)
WHERE CONCAT(item1,item2,item3) <> ''

johanafm

For the table header, I'd pull either all the items or inner joined with what's been bought if you don't want to explicitly show that some products havn't been bought, ordered by item name.
Then I'd simply get the customers inner joined with their purchased products, ordered by customer name and item name.

First loop over the items and display the header, and while doing so create an array on the form

$i = 0;
while ($item = fetch_assoc($item_result))
	$items[$row['item_name']] = $i++;

Then loop over each row of customers and purchases, and as long as you have the same customer as before, you continue on the same table row. For each row you get, you also have an item name, which you use against $items[$purchases['item_name']], and this will tell you what column to put the data into. Also keep track of what column the last row went into, and you can use str_repeat to output a sufficient number of "<td></td>", divs or whatever you use. No need to create an array padded with null values, neither in PHP, nor by LEFT JOINS or such in SQL.
When you come to a row that holds a new customer id, you start on a new row.

Weedpacket

Without seeing the database schema, it sounds like it's not very well normalised: in a reasonable setup there wouldn't be any problem with "nulls in columns where the customer didn't order all of the items" because the result would simply be a list of the items that the customer did order - and they'd all be in one column.

jgesolutions

Oh its well normalized. There is no set maximum or minimum number of items. Here is an example of the tables.

Customer Table:
CustomerID ItemNo ItemQty OrdDate
1 1 3 12/10/2009
1 2 5 12/10/2009
1 3 7 12/10/2009
2 3 2 12/10/2009
2 4 8 12/10/2009
2 5 7 12/10/2009

Item Table:
ItemID ItemDesc
1 Cucumbers
2 Green Bell Peppers
3 Red Bell Peppers
4 Yellow Bell Peppers
5 Yellow Corn
6 White Corn

The outcome I would like to display:

Customer Cucumbers Green Bell Peppers Red Bell Peppers Yellow Bell Peppers Yellow Corn White Corn

 1               3                         5                               7                 
 2                                                                           2                            8                 7

jgesolutions

Oh its well normalized. There is no set maximum or minimum number of items. Here is an example of the tables.

Customer Table:
CustomerID ItemNo ItemQty OrdDate
1 1 3 12/10/2009
1 2 5 12/10/2009
1 3 7 12/10/2009
2 3 2 12/10/2009
2 4 8 12/10/2009
2 5 7 12/10/2009

Item Table:
ItemID ItemDesc
1 Cucumbers
2 Green Bell Peppers
3 Red Bell Peppers
4 Yellow Bell Peppers
5 Yellow Corn
6 White Corn

The outcome I would like to display:

Customer Cucumbers Green Bell Peppers Red Bell Peppers Yellow Bell Peppers Yellow Corn White Corn

_1___3_____5______7

2 _____________________________2________8___________7

Weedpacket

Ah, so the nulls aren't in the database, it's a display issue (FYI, the formatting tags described in the FAQ may be easier to use).

Select Customer.CustomerID, Item.ItemDesc, SUM(Customer.ItemQty) as ItemQty
From Customer, ItemQty
Where Item.ItemID=Customer.ItemNo
Group By Customer.CustomerID, Item.ItemDesc

Which can be massively simplified if you can guarantee the customer table will only have one record per CustomerID/ItemNo pair. At least this query ensures that there is not more than one. It also retrieves the ItemDesc rather than the ItemID.

A list of ItemDescs from the Item table gives you (most) of the column headings, so all you need are default values (specifically empty strings) for the display values.

$defaults = array_fill_keys($itemdescs ,'');

And then for each $record from the above query

if(empty($rows[$record['CustomerId']]))
        $rows[$record['CustomerID']] = $defaults;
$rows[$record['CustomerId']][$record['ItemDesc']] = $record['ItemQty];

Display:

echo '<tr><th>Customer</th>';
foreach($itemdescs as $desc) echo '<th>'.$desc.'</th>';
echo '</tr>';

and

foreach($record as $customer=>$items)
{
    echo '<tr><td>', $customer, '</td>';
    foreach($items as $qty)
        echo '<td>', $qty, '</td>';
    echo '</tr>';
}