PDOStatement
在线手册：中文 英文
PHP手册

PDOStatement::setFetchMode

(PHP 5 >= 5.1.0, PECL pdo >= 0.2.0)

PDOStatement::setFetchMode — Set the default fetch mode for this statement

说明

bool PDOStatement::setFetchMode ( int $mode )
bool PDOStatement::setFetchMode ( int $PDO::FETCH_COLUMN , int $colno )
bool PDOStatement::setFetchMode ( int $PDO::FETCH_CLASS , string $classname , array $ctorargs )
bool PDOStatement::setFetchMode ( int $PDO::FETCH_INTO , object $object )

参数

mode

The fetch mode must be one of the PDO::FETCH_* constants.

colno

Column number.

classname

Class name.

ctorargs

Constructor arguments.

object

Object.

返回值

Returns 1 on success 或者在失败时返回 FALSE.

范例

Example #1 Setting the fetch mode

The following example demonstrates how PDOStatement::setFetchMode() changes the default fetch mode for a PDOStatement object.

<?php
$sql = 'SELECT name, colour, calories FROM fruit';
try {
  $stmt = $dbh->query($sql);
  $result = $stmt->setFetchMode(PDO::FETCH_NUM);
  while ($row = $stmt->fetch()) {
    print $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
  }
}
catch (PDOException $e) {
  print $e->getMessage();
}
?>

以上例程会输出：

apple   red     150
banana  yellow  250
orange  orange  300
kiwi    brown   75
lemon   yellow  25
pear    green   150
watermelon      pink    90

PDOStatement
在线手册：中文 英文
PHP手册
PHP手册 - N: Set the default fetch mode for this statement

用户评论:

ronhenry at netspeed dot com dot au (05-Aug-2010 04:43)

To create an object instance via the constructor solely from the result of the PDO query you must use PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, followed by the name of the class, then an array consisting of the names of the constructor elements. This may be specified in setFetchMode() for prepared statements, or fetch*() otherwise. This forces PDO to create your object instance by calling the constructor.

e.g setFetchMode (PDO::FETCH_CLASS , 'classname', array('paramName1', 'paramName2', ...).

An example using a prepared statement (without variable bindings) and fetchALL().

<?php
class Test
{
  private $id;
  private $a;
  private $b;

  function __construct ($id, $a, $b)
  {
    $this->id = $id;
    $this->a = $a;
    $this->b = $b;
  }

  public function display()
  {
    echo "<p>id: $this->id, a: $this->a, b: $this->b</p>";
  }
}

$testx = new Test(10, 'AAA', 'BBB' );
$testx->display();
$testx = NULL;

try
{
  $pdo = new PDO('mysql:host=127.0.0.1;dbname=Testing', 'user', 'pswd', array (PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
 
  // Prepared statement
  $stmt = $pdo->prepare("SELECT id, a, b FROM test WHERE id = 1");
  $stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "Test", array('id', 'a', 'b'));
  $stmt->execute();
  foreach ($stmt as $test)
    $test->display();

  // Same again with fetchALL()
  $stmt = $pdo->query("SELECT id, a, b FROM test WHERE id = 2");
  $test = $stmt->fetchALL(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Test', array('id', 'a', 'b'));
  $test[0]->display();
   
  $pdo = NULL;
}
catch (PDOException $e)
{
  echo 'Error: ', $e->__toString();
}
?>

You can avoid all this PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE and constructor fuss, and create an object instance from the result of a PDO query without a constructor, and with class properties declared as public, protected, private, or not at all:  just let PDO use reflection injection to populate your object instance with data from your query. This is PDO's default action. PDO will inject the specified variables into your object instance before php calls the constructor, so you should either not have a constructor, or have one with no parameters. Of course, you may have problems using this as a normal class. Also see the comments about reflection injection below.

<?php
class Test1
{
  public function display()
  {
    echo "<p>id: $this->id, a: $this->a, b: $this->b</p>";
  }
}

try
{
  $pdo = new PDO('mysql:host=127.0.0.1;dbname=Testing', 'user', 'pswd', array (PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
 
  $stmt = $pdo->prepare("SELECT id, a, b FROM test WHERE id = 1");
  $stmt->setFetchMode(PDO::FETCH_CLASS, "Test1");
  $stmt->execute();
  foreach ($stmt as $test1)
    $test1->display();

  $stmt = $pdo->query("SELECT id, a, b FROM test WHERE id = 2");
  $test1s = $stmt->fetchALL(PDO::FETCH_CLASS, 'Test1');
  foreach ($test1s as $test1)
    $test1->display();

  $pdo = NULL;
}
catch (PDOException $e)
{
  echo 'Error: ', $e->__toString();
}
?>

You can also use variables with PDO::FETCH_CLASS and the constructor parameter array, what you do is up you.

Reflection injection increases the risk of variable, code, and SQL injection if user input is used unwisely, so ALWAYS validate user input on the server side, and ONLY insert it into your SQL via bound parameters in prepared SQL statements. If you do not use PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, and a constructor element name array, you should be especially careful.
See: http://www.owasp.org/index.php/Reflection_injection for a non php description of reflection injection.

Dormilich at netscape dot net (13-Jul-2010 10:16)

if you want to fetch your result into a class (by using PDO::FETCH_CLASS) and want the constructor to be executed *before* PDO assings the object properties, you need to use the PDO::FETCH_PROPS_LATE constant:

<?php
$stmt = $pdo->prepare("your query");

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "className", $constructorArguments);

# pass parameters, if required by the query
$stmt->execute($parameters);

foreach ($stmt as $row)
{
    // do something with (each of) your object
}
?>

harlequin2 at gmx dot de (10-Jun-2008 01:22)

You can also set the fetch mode to FETCH_ASSOC if you do not need the column index. This reduces both the size of the resultset and is faster in some cases.

<?php
$stmt = $dbh->prepare("SELECT mykey, myvalue FROM mytable");
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$arrValues = $stmt->fetchAll();
foreach ($arrValues as $row){
    print $row["mykey"]." -> ".$row["myvalue"]."<br />\n";
}
?>

stein_AT_rhrk.uni-kl.de (20-Nov-2007 12:04)

Some note on PDO :: FETCH_CLASS | PDO :: FETCH_CLASSTYPE

It took me some time, to realize, that you can not use this fetch-mode in $stm->setFetchMode(), when you want to retrieve objects from the database whereas the type is defined by the first column in the resultset.
You have to define this mode directly in the $stm->fetch() method.

To make it clearer:

$stm = $pdo->query("SELECT * FROM `foo`);
$stm->setFetchMode(FETCH_CLASS | PDO :: FETCH_CLASSTYPE);

$object = $stm->fetch();

Will not return the expected object, whereas

$stm = $pdo->query("SELECT * FROM `foo`");

$object = $stm->fetch(FETCH_CLASS | PDO :: FETCH_CLASSTYPE);

will give you the object of the class, defined in the first column of `foo`.

matt at kynx dot org (18-Feb-2006 11:13)

To fetch the rows into an existing instance of a class, use PDO::FETCH_INTO and pass the object as the second parameter.

The class _must_ have the column names declared as public members, or the script will die. But overloading with __set() and __get() lets it handle any column your query throws at it. 

Set the fetch mode right after you call prepare(). It appears you _must_ use execute() - fetch() won't work. A small example, adapted from ext/pdo/tests/pdo_025.phpt:

<?php
class Test
{
    protected $cols;
   
    function __set($name, $value) {
        $this->cols[$name] = $value;
    }
   
    function __get($name) {
        return $this->cols[$name];
    }
}

$obj = new Test();
$db = PDOTest::factory();
$stmt = $db->prepare("select * from test");
$stmt->setFetchMode(PDO::FETCH_INTO, $obj);
$stmt->execute();

foreach ($stmt as $a) {
    print_r($a);
}

print_r($obj); // contains the same values as the last iteration above
?>

Dariusz Kielar (13-Feb-2006 06:55)

PDO::FETCH_CLASS may be used only with this method and it takes up to three parameters then:
 - int mode
 - string className
 - array constructorArgs (optional)

<?php
$stmt = $pdo -> query('your query');
$stmt -> setFetchMode(PDO::FETCH_CLASS, 'yourClass', array(0 => false));

while($row = $stmt -> fetch())
{
   // $row is an object of "yourClass" class
}
$stmt -> closeCursor();
?>

Note also that the constructor is called AFTER the data is set on the object.