Is it possible to call a PHP function from an SQL query?_问答_开发者

Say there is a special PHP function foo($number) that returns double the value of its input. Further, say there is a table that contains the column "number." Is there a way to have the PHP function and SQL query to run together so that I would get the following:

Number | Double
================
1 | 2
5 | 10

While in this simple example column Double can easily be implemented within the SQL statement, answers should cover the more general case of calling any PHP function, as开发者_开发问答 there are many operations that are difficult to impossible in SQL but easy in PHP.

No, since the query results come straight from MySQL. You can apply the function to the result set after you execute your query, either by using a loop or by applying your function to the results using array_map() function.

Depending on what you're actually trying to achieve it might be possible to decouple the data source and the consumer a bit, enough to put another component between them.

Let's start with

<?php
$result = getData($pdo); // a)
doSomething($result); // b)

function getData($mysql) {
  return mysql_query('SELECT x FROM soTest', $mysql);
}

function doSomething($result) {
  while ( false!==($row=mysql_fetch_assoc($result)) ) {
    echo ' ', join(', ', $row), "\n";
  }
  echo "----\n";
}

There's very little you can do to alter a mysql result resource. And doSomething() does nothing more than iterating over the result set. It does nothing that is special to a mysql result set, yet it allows nothing else but this exact resource type by using mysql_fetch_xyz().
Let's try this again using PDO (PDO_MYSQL).

$result = getData($pdo);
doSomething($result);

function getData($pdo) {
  return $pdo->query('SELECT x FROM soTest');
}

function doSomething(PDOStatement $result) {
  while ( $row=$result->fetch(PDO::FETCH_ASSOC) ) {
    echo ' ', join(', ', $row), "\n";
  }
  echo "----\n";
}

That didn't change much. Some names but essentially this is the same. But PDOStatement implements the Traversable interface, so you can use it directly with foreach.

$result = getData($pdo);
doSomething($result);

function getData($pdo) {
  return $pdo->query('SELECT x FROM soTest', PDO::FETCH_ASSOC);
}

function doSomething($traversable) {
  foreach( $traversable as $row ) {
    echo ' ', join(', ', $row), "\n";
  }
  echo "----\n";
}

That's a game changer... We can pass any traversable/iterator to doSomething() and it still does more or less the same thing as before.
Now we can put something "between" getData() and doSomething(). This something takes an inner iterator (like getData() provides in the form of an PDOStatement) and behaves itself like an iterator (so DoSomething() can use it) returning all elements of its inner iterator but modifying some elements.
I chose to extend FilterIterator for this example for no particular reason. You need php 5.3+ to run this demo since it uses an anonymous function:

<?php
$pdo = initDemo();

echo "#1: passing \$result\n";
$result = getData($pdo); // a)
doSomething($result); // b)

echo "#2: passing ModifyIterator\n";
$result = getData($pdo); // exact same as a)
$result = new ModifyIterator($result, null, function($e) {
  $e['y'] = '/' . ($e['x'] * 2) .'/';
  return $e;
});
doSomething($result);  // exact same as b)

function getData($pdo) {
  return $pdo->query('SELECT x FROM soTest', PDO::FETCH_ASSOC);
}  

function doSomething($traversable) {
  foreach($traversable as $row) {
    echo ' ', join(', ', $row), "\n";
  }
  echo "----\n";
}

class ModifyIterator extends FilterIterator {
  protected $fnAccept, $fnModify;
  public function __construct($it, $fnAccept, $fnModify) {
    // todo: test parameters!
    $this->fnAccept = $fnAccept;
    $this->fnModify = $fnModify;
    if ( !($it instanceof Iterator) ) {
      $it = new IteratorIterator($it);
    }
    parent::__construct($it);
  }

  public function accept() {
    return $this->fnAccept ? $this->fnAccept(parent::current()) : true;
  }

  public function current() {
    if ( $this->fnModify ) {
      $fn = $this->fnModify;
      return $fn(parent::current());
    }
    return parent::current();
  }
}

function initDemo() {
  $pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('CREATE TEMPORARY TABLE soTest (x int auto_increment, primary key(x))');
  $pdo->exec('INSERT INTO soTest VALUES (),(),(),(),()');
  return $pdo;
}

prints

#1: passing $result
 1
 2
 3
 4
 5
----
#2: passing ModifyIterator
 1, /2/
 2, /4/
 3, /6/
 4, /8/
 5, /10/
----

The important part is that ModifyIterator forces very little particular behaviour on the inner iterator (e.g. you can still use an unbuffered query without the need to transfer all the data into the php process' memory at once) and that both getData() and doSomething() are left unchanged.

One way would be to fetch the results into objects:

class NumberDoubler
{
    public $number;

    public function __construct()
    {
        $this->number *= 2;
    }
}

$pdo = new PDO('mysql:host=localhost;dbname=db_name', 'uname', 'pword');
$stmnt = $pdo->query('SELECT number FROM db_table');
$result = $stmnt->fetchAll(PDO::FETCH_CLASS, 'NumberDoubler');

print_r($result);

The result will be an array of objects with '$number' doubled. Of course, iteration will still be done "behind the scenes", and the manual warns, "Using this method to fetch large result sets will result in a heavy demand on system and possibly network resources."