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:gabe@stripped]
>> Sent: Thursday, October 16, 2003 1:47 PM
>> To: Jeff McKeon; mysql@stripped
>> 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
>>> 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
>>>
>>
>>
>
