Understanding the process

rasmas

HI All

I need to understand the process before I start coding.
I have a database that contains the following
Item Number
date
Account Number
Quantity
Total value

only if you combine the Item Number date and Account number will it be unique.

What I need to do is the following

I need to extract all the data for a curtain Account number or a range of account numbers for a curtain date range,
I need to add up all the quantities from all the date ranges for the individual Item numbers per account numbers.
The add the quantities from the range if account numbers for the individual Item numbers.

I am correct in saying I would create a array and then work with that array?

Thanks
Martin

Roger_Ramjet

Yes, you could do that. Personally I would do it in sql - but then I'm far more experienced with that. To do it with queries you need to learn how to use the Aggregate Functions . Of course you are also going to need to be able to use the Date Functions to extract the base data whichever way you do it.

One point: CHANGE the name of the 'date' column. Date is an sql function name and should not be used as a column name. It is also meaningless: What Date? Date inserted, Date modified, Birthday, etc. Always make column names as meaningfull as possible - and make them UNIQUE across your whole database. You will find that this helps a lot in the long run.

mjax

This (untested) PHP code should show you how to do this:

// search for accounts with number 1, 20 and 25
$account_numbers = array();

$account_numbers[] = 1;
$account_numbers[] = 20;
$account_numbers[] = 25;

// search for orders in Q1 2006
$date_from = '2006-01-01';
$date_until = '2006-03-31';

// fetch all database data
$result = mysql_query("
				SELECT
					*
				FROM
					orders
				WHERE
					`Account Number` IN (".implode(',', $account_numbers.") AND
					`Date` BETWEEN '$date_from' AND '$date_until'
			");

// construct a hash containing desired output
$hash = array();

// loop through database rows
while($row = mysql_fetch_array($result)) {
	// store running total for this product for this account
	$hash[$row['Account Number']][$hash['Product Number']] += $row['Quantity'];
}

$global_total = 0;

// loop throught all accounts
foreach($hash as $account_number => $order_data) {
	$account_total = 0;

echo "Account $account_number:\n";

// loop through product orders for this account
foreach($order_data as $product_number => $quantity) {
	echo "Product $product_number: $quantity times sold.\n";

	$account_total += $quantity;
	$global_total += $quantity;
}

echo "TOTAL: $account_total\n";
echo "\n\n";
}

echo "GLOBAL TOTAL: $global_total\n";

rasmas

Thank you both for the replys will test it out as soon as I get a chance, I am learing PHP as a hobby.

Roger could you please give me some details how to do this with sql

Thanks
MArtin

Roger_Ramjet

OK, some examples

To get all the quantities for each item from an account

SELECT ItemNumber, SUM(Quantity) AS Quantity FROM table 
WHERE AccountNumber = $acc
GROUP BY ItemNumber

To do the same for a range of account numbers

SELECT AccountNumber, ItemNumber, SUM(Quantity) AS Quantity FROM table
WHERE AccountNumber BETWEEN $x AND $y
GROUP BY AccountNumber, ItemNumber

Likewise for a date range

rasmas

Thanks will try it,

Martin