On Thursday, October 16, 2003, at 02:19 PM, Jeff McKeon wrote:
>>> $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.
>
> So something like the while loop....
>
> While($query_data=mysql_fetch_row($data)) {
> $revenues[ $query_data[0] ][ $query_data[1] ][ $query_data[2] ]
> = $result['Revenue'];
> }
>
> Then I just need to retrieve the data out of the multi-dimensional
> array
> and display it or write it to a file?
Yeah, that will work fine.
> Jeff
>
>> -----Original Message-----
>> From: Gabriel Ricard [mailto:gabe@stripped]
>> Sent: Thursday, October 16, 2003 2:11 PM
>> To: Jeff McKeon
>> Cc: mysql@stripped
>> Subject: Re: Challenging query....
>>
>>
>>
>> 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
>>>>>
>>>>
>>>>
>>>
>>
>>
>
