Re: Challenging query....

[Gabriel Ricard]

On Thursday, October 16, 2003, at 02:04  PM, Jeff McKeon wrote:

Do this query:

SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
In practice this would change to something like...

$data=mysql_query("SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID",
$link_id);
Yep.

Then use this code:

$revenues = array();


There's obviously some php code missing here, I'd need to retrive the
query results with something like....
Indeed. I made no assumptions as to whether or not you use the mysql_* 
functions directly, or perhaps dbx or PEAR__DB, ADOdb, etc.


$revenues = mysql_fetch_rows($data);

To dump the data into the array.  Is this correct?
There is no single function in the mysql extension to retrieve all 
records at once. You can do this with the dbx extension, which wraps 
MySQL (& other DBMS) functions. with the MySQL extension you'll have to 
loop through the results and call mysql_fetch_row. There is a 
comprehensive manual on these functions available at 
http://php.net/mysql, which includes many useful tips & examples from 
other users that will help you out.

 - Gabriel


foreach( $results as $result )
{
        $revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a
minimal SQL
query and minimal application code.


Jeff

-----Original Message-----
From: Gabriel Ricard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 1:47 PM
To: Jeff McKeon; [EMAIL PROTECTED]
Subject: Re: Challenging query....
Or you could just do one simply query as I explained previously,
retrieve the data in PHP, and group it by date rather than
spending the
same time in PHP generating a massive, inefficient query (and if you
have a large number of customers, you won't generate a query larger
than the maximum MySQL packet size, or incur any limits on the number
of joins or aliases).
Do this query:

SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS
Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS
Revenue FROM rev GROUP BY Year, Month ORDER BY Year,Month,CustomerID
Then use this code:

$revenues = array();

foreach( $results as $result )
{
        $revenues[ $result['Year'] ][ $result['Month'] ][
$result['CustomerID'] ] = $result['Revenue'];
}
The result will be a simple multi-dimensional array with a
minimal SQL
query and minimal application code.
- Gabriel

On Thursday, October 16, 2003, at 01:19  PM, Rory McKinley wrote:

Hi Jeff

OK, aliasing table is creating a copy of one table but calling it
something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM
revenue AS a,
revenue AS b, revenue AS c

I am referencing revenue three times but have aliased it as
a, b, and
c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of my head in
rough
(very!) PHP , if you don't come right, I can sit down and
do the code a
little more detailed

For simplification purposes, I am going to assume that you can alias
tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest
you check
if this
is possible - if you can't there is a work around that just
requires a
bit
more thought....
//Assume you have an array that has all your client ids in

$client_id_array.

//Create base values based on the first id...

$select_base = "YEAR(1.date) AS year, MONTH(1.date) AS month,
SUM(1.revenue) AS cust1_rev"
$for_base = "FROM revenue 1"

$predicate_base = "WHERE 1.customer_id = ".$client_id_array[0]

//Now loop through and append additional items to each
string for each
instance of a client

//Start at 1 not zero as we already have accounted for the first id
above
for($j=1; $j < count($client_id_array); $j++)
{
    $select_base = $select_base.', SUM('.($j+1).') AS
cust'.($j+1).'_rev';
    $for_base = $for_base.', revenue '.($j+1);

    $predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) =
YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND
'.($j+1).'.customer_id = '.$client_id_array[$j].')';
}

//Once your loop is done, put the parts together

$query = $select_base.$for_base.$predicate_base;

If you can't use numbers, you can use single letters, but that
requires a
little more work incrementing ASCII numbers and then converting to
letters -
also makes things way more complicated if you have more than 26
clients :)
but still doable.
HTH

Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
----- Original Message -----
From: "Jeff McKeon" <[EMAIL PROTECTED]>
To: "Rory McKinley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, October 16, 2003 6:51 PM
Subject: RE: Challenging query....


If you have a way to generate the query code dynamically
(e.g. using
a loop in C, PHP etc.), you can build a query using
aliased tables :
SELECT YEAR(a.date) AS year, MONTH(a.date) AS month,
SUM(a.revenue) AS cust1_rev,
SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev
FROM revenue
a, revenue b, revenue c WHERE a.customer_id = 1 AND
(YEAR(b.date) =
YEAR(a.date) AND
MONTH(b.date) =
MONTH(a.date) AND b.customer_id = 2)
AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) =
MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month
Ok, that looks promising as I'll be using PHP, but I'm a
little fuzzy
on
the logic you've set.  What are "aliased tables" and how would I
define,
use them in an loop?
Thanks,

Jeff



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]