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

Groups > comp.lang.php > #6071

Re: Transforming object properties?

Followups directed to: comp.lang.php

Show key headers only | View raw

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).

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web