> 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);
> 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....
$revenues = mysql_fetch_rows($data);
To dump the data into the array. Is this correct?
>
> 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]
