| 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:
|
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
]