Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #5862 > unrolled thread

Transforming object properties?

Back to article view | Back to comp.lang.php

Contents

  Transforming object properties? Mike <mpearl1@gmail.com> - 2012-05-08 16:44 -0700
    Re: Transforming object properties? Jeff North <jnorthau@yahoo.com.au> - 2012-05-09 11:01 +1000
      Re: Transforming object properties? Mike <mpearl1@gmail.com> - 2012-05-09 14:57 -0700
        Re: Transforming object properties? Captain Paralytic <paul_lautman@yahoo.com> - 2012-05-10 03:24 -0700
        Re: Transforming object properties? Jeff North <jnorthau@yahoo.com.au> - 2012-05-15 10:28 +1000
          Re: Transforming object properties? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2012-05-15 20:09 +0200
    Re: Transforming object properties? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-05-09 10:02 +0200
      Re: Transforming object properties? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-05-09 10:05 +0200

#5862 — Transforming object properties?

I need an array of objects that looks like this:

$items[$i]->name
          ->new_amount
          ->old_amount

The data would look like this:

name          new_amount          old_amount
John          500.00              200.00      
Sue           15.00               <NULL>
Frank         254.00              <NULL>
Max           <NULL>              28.00The data would look like this:

But the results of my sql query comes back as:

$items[$i]->name
          ->type
          ->amount

name          type          amount
----------------------------------
John          new_amount    500.00
John          old_amount    200.00
Sue           new_amount    15.00
Frank         new_amount    254.00
Max           old_amount    28.00

Is there a reasonable way to accomplish this?

Thanks!

Mike

[toc] | [next] | [standalone]

#5863

On Tue, 8 May 2012 16:44:48 -0700 (PDT), in comp.lang.php Mike
<mpearl1@gmail.com>
<16764404.423.1336520688722.JavaMail.geo-discussion-forums@pblw1>
wrote:

>| I need an array of objects that looks like this:
>| 
>| $items[$i]->name
>|           ->new_amount
>|           ->old_amount
>| 
>| The data would look like this:
>| 
>| name          new_amount          old_amount
>| John          500.00              200.00      
>| Sue           15.00               <NULL>
>| Frank         254.00              <NULL>
>| Max           <NULL>              28.00The data would look like this:
>| 
>| But the results of my sql query comes back as:
>| 
>| $items[$i]->name
>|           ->type
>|           ->amount
>| 
>| name          type          amount
>| ----------------------------------
>| John          new_amount    500.00
>| John          old_amount    200.00
>| Sue           new_amount    15.00
>| Frank         new_amount    254.00
>| Max           old_amount    28.00
>| 
>| Is there a reasonable way to accomplish this?
>| 
>| Thanks!
>| 
>| Mike

You need a cross-tab query to bring the data across in the format that
you want.

SELECT firstname
, sum(old_amount) AS old_amount
, sum(new_amount) AS new_amount
FROM people
GROUP BY firstname

http://dev.mysql.com/tech-resources/articles/wizard/index.html

[toc] | [prev] | [next] | [standalone]

#6058

I didn't do a good job of presenting this, my apologies.

I have two tables, one with user info, the other with commission amounts.  I need to get a list of sales people that gives the total commissions for each status (there are four of them).   My SQL query looks like this:

SELECT name, status, SUM(commission) 
FROM transactions 
GROUP BY name, status
ORDER BY name ASC

This is giving the right results, but I will have multiple rows for each name, showing the status and total for that status:

Joe   new      10.00
Joe   pending  44.00
Joe   closed   88.00
etc.

I need a single row for each name, something like this:

name          new          pending          closed
Joe           10.00        44.00            88.00

Is there a way to create a sql query that will return this?  If not, how can I get the data in that format?

Thanks again!
Joe     

[toc] | [prev] | [next] | [standalone]

#6060

On May 9, 10:57 pm, Mike <mpea...@gmail.com> wrote:
> I didn't do a good job of presenting this, my apologies.
>
> I have two tables, one with user info, the other with commission amounts.  I need to get a list of sales people that gives the total commissions for each status (there are four of them).   My SQL query looks like this:
>
> SELECT name, status, SUM(commission)
> FROM transactions
> GROUP BY name, status
> ORDER BY name ASC
>
> This is giving the right results, but I will have multiple rows for each name, showing the status and total for that status:
>
> Joe   new      10.00
> Joe   pending  44.00
> Joe   closed   88.00
> etc.
>
> I need a single row for each name, something like this:
>
> name          new          pending          closed
> Joe           10.00        44.00            88.00
>
> Is there a way to create a sql query that will return this?  If not, how can I get the data in that format?
>
> Thanks again!
> Joe

This is not a PHP question. I suggest you take it over to a relevant
SQL forum (e.g. comp.databases.mysql)

[toc] | [prev] | [next] | [standalone]

#6065

On Wed, 9 May 2012 14:57:10 -0700 (PDT), in comp.lang.php Mike
<mpearl1@gmail.com>
<21352106.71.1336600630360.JavaMail.geo-discussion-forums@pbmf10>
wrote:

>| I didn't do a good job of presenting this, my apologies.
>| 
>| I have two tables, one with user info, the other with commission amounts.  I need to get a list of sales people that gives the total commissions for each status (there are four of them).   My SQL query looks like this:
>| 
>| SELECT name, status, SUM(commission) 
>| FROM transactions 
>| GROUP BY name, status
>| ORDER BY name ASC
>| 
>| This is giving the right results, but I will have multiple rows for each name, showing the status and total for that status:
>| 
>| Joe   new      10.00
>| Joe   pending  44.00
>| Joe   closed   88.00
>| etc.
>| 
>| I need a single row for each name, something like this:
>| 
>| name          new          pending          closed
>| Joe           10.00        44.00            88.00
>| 
>| Is there a way to create a sql query that will return this?  If not, how can I get the data in that format?
>| 
>| Thanks again!
>| Joe     

SELECT name
, SUM(if(status='new',commission,0) ) AS `new` 
, SUM(if(status='pending',commission,0) ) AS `pending`
, SUM(if(status='closed',commission,0) ) AS `closed`
FROM transactions 
GROUP BY name
ORDER BY name

Now to put this back on-topic for this group.
As you can see there is a lot of repetition here and what if the data
was dynamic where you didn't know the field names in advance. Or
someone decides they need to add new status types. Or you wanted to
add the count of each status type.

You could go and change all your code or you could make your query
dynamic from the outset.

Get the list of the table values:
$sql = "SELECT status FROM transaction GROUP BY status";
$records = // use whatever method to get the results from the database

$sql = "SELECT name ";	//--- start building query, re-use $sql
foreach($records as $r) {
   $sql .= ",SUM(if(status='{$r}',commission,0) ) AS `{$r}`"; 
   $sql .= ",COUNT(if(status='{$r}',1,0) ) AS `{$r}Count`";
}
//--- now append the rest of the query
$sql .= "FROM transactions 
GROUP BY name
ORDER BY name";
$xtab = // use whatever method to get the results from the database

Now your boss comes up to you and tells you that they want the
minimum, average and maximum values. You say, no problem - what order
do you want the results?

Your boss turns to you and says 'For being such a smart-arse you can
now do the totals for each quarter'.

If you want to know how to produce more complex cross tab queries then
head over to:
http://dev.mysql.com/tech-resources/articles/wizard/index.html
its a bit long winded but I find very useful.

HTH

[toc] | [prev] | [next] | [standalone]

#6071

Jeff North wrote:

> You could go and change all your code or you could make your query
> dynamic from the outset.
> 
> Get the list of the table values:
> $sql = "SELECT status FROM transaction GROUP BY status";
> $records = // use whatever method to get the results from the database
> 
> $sql = "SELECT name ";	//--- start building query, re-use $sql
> foreach($records as $r) {
>    $sql .= ",SUM(if(status='{$r}',commission,0) ) AS `{$r}`";
>    $sql .= ",COUNT(if(status='{$r}',1,0) ) AS `{$r}Count`";
> }
> //--- now append the rest of the query
> $sql .= "FROM transactions
> GROUP BY name
> ORDER BY name";

The second query will probably not work in MySQL because the `FROM' keyword 
is not preceded by whitespace.  Consecutive concatenation-assignment is also 
comparably very inefficient in probably all programming languages.

I strongly suggest to build that query (as a first step) thus:

  function mapper($r)
  {
    return "SUM(IF(status = '{$r}', commission, 0)) AS `{$r}`"
         . ",COUNT(IF(status = '{$r}', 1, 0)) AS `{$r}Count`";
  }

  $sql = "SELECT name,\n  ";
       . implode("\n, ", array_map('mapper', $records))
       . ' FROM transactions GROUP BY name ORDER BY name';

Since PHP 5.3 you can also use an anonymous function (closure) for 
callbacks, as known e.g. from ECMAScript implementations like JavaScript:

  $sql = "SELECT name,\n  ";
       . implode(",\n  ",
           array_map(function ($r) {
             return "SUM(IF(status = '{$r}', commission, 0)) AS `{$r}`"
               . ",COUNT(IF(status = '{$r}', 1, 0)) AS `{$r}Count`";
           }, $records))
       . ' FROM transactions GROUP BY name ORDER BY name';
  
<http://php.net/manual/en/functions.anonymous.php>

The next step should be using Prepared Statements, so that SQL injection can 
be avoided.  See <http://php.net/manual/en/
security.database.sql-injection.php> (including the user comments) for more.


PointedEars
-- 
When all you know is jQuery, every problem looks $(olvable).

[toc] | [prev] | [next] | [standalone]

#5868

Am 09.05.2012 01:44, schrieb Mike:
> I need an array of objects that looks like this:
> 
> $items[$i]->name
>           ->new_amount
>           ->old_amount
> 
> The data would look like this:
> 
> name          new_amount          old_amount
> John          500.00              200.00      
> Sue           15.00               <NULL>
> Frank         254.00              <NULL>
> Max           <NULL>              28.00The data would look like this:
> 
> But the results of my sql query comes back as:
> 
> $items[$i]->name
>           ->type
>           ->amount
> 
> name          type          amount
> ----------------------------------
> John          new_amount    500.00
> John          old_amount    200.00
> Sue           new_amount    15.00
> Frank         new_amount    254.00
> Max           old_amount    28.00
> 
> Is there a reasonable way to accomplish this?

$stmt = $pdo->query("select name, new_amount, old_amount from table1");
if ($stmt) {
   $res = $stmt->fetchAll(PDO::FETCH_CLASS);
} else {
   trigger_error('statement failed, see error messages',E_USER_ERROR);
}

/Str.

[toc] | [prev] | [next] | [standalone]

#5869

Am 09.05.2012 10:02, schrieb M. Strobel:
> Am 09.05.2012 01:44, schrieb Mike:
>> I need an array of objects that looks like this:
>>
>> $items[$i]->name
>>           ->new_amount
>>           ->old_amount
>>
>> The data would look like this:
>>
>> name          new_amount          old_amount
>> John          500.00              200.00      
>> Sue           15.00               <NULL>
>> Frank         254.00              <NULL>
>> Max           <NULL>              28.00The data would look like this:
>>
>> But the results of my sql query comes back as:
>>
>> $items[$i]->name
>>           ->type
>>           ->amount
>>

-cut-
> 
> $stmt = $pdo->query("select name, new_amount, old_amount from table1");
> if ($stmt) {
>    $res = $stmt->fetchAll(PDO::FETCH_CLASS);
$res is $items
> } else {
>    trigger_error('statement failed, see error messages',E_USER_ERROR);
> }
> 
> /Str.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web