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
> rorym@stripped
> "There are 10 kinds of people in this world,
> those who understand binary and those who don't" (Unknown)
> ----- Original Message -----
> From: "Jeff McKeon" <jmckeon@stripped>
> To: "Rory McKinley" <rorym@stripped>; <mysql@stripped>
> 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/mysql?unsub=1
>
