FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Re: Transforming object properties?
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
Re: Transforming object properties? [message #178148] Tue, 15 May 2012 00:28 Go to next message
Jeff North is currently offline  Jeff North
Messages: 58
Registered: November 2010
Karma: 0
Member
On Wed, 9 May 2012 14:57:10 -0700 (PDT), in comp.lang.php Mike
<mpearl1(at)gmail(dot)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
Re: Transforming object properties? [message #178154 is a reply to message #178148] Tue, 15 May 2012 18:09 Go to previous message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
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).
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: Not understood error...
Next Topic: flush() with gzip
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 10 07:28:06 GMT 2024

Total time taken to generate the page: 0.02606 seconds