> > $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?
Jeff
> -----Original Message-----
> From: Gabriel Ricard [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 16, 2003 2:11 PM
> To: Jeff McKeon
> Cc: [EMAIL PROTECTED]
> 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:[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]
