| Re: Transforming object properties? [message #178148] |
Tue, 15 May 2012 00:28  |
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  |
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).
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]