Php pdostatement to array

Класс PDOStatement

Представляет подготовленный запрос к базе данных, а после выполнения запроса соответствующий результирующий набор.

Обзор классов

public bindColumn (
string | int $column ,
mixed &$var ,
int $type = PDO::PARAM_STR ,
int $maxLength = 0 ,
mixed $driverOptions = null
): bool

public bindParam (
string | int $param ,
mixed &$var ,
int $type = PDO::PARAM_STR ,
int $maxLength = 0 ,
mixed $driverOptions = null
): bool

public fetch ( int $mode = PDO::FETCH_DEFAULT , int $cursorOrientation = PDO::FETCH_ORI_NEXT , int $cursorOffset = 0 ): mixed

public setFetchMode ( int $mode = PDO::FETCH_CLASS , string $class , ? array $constructorArgs = null ): bool

Свойства

Используемая строка запроса.

Список изменений

User Contributed Notes 2 notes

I don’t know why PDOStatement don’t return «execution time» and «found rows» so here I created an extended class of PDOStatement with these attributes.

Just have to «setAttribute» of PDO’s object to $PDO->setAttribute(\PDO::ATTR_STATEMENT_CLASS , [‘\customs\PDOStatement’, [&$this]]);

final class PDOStatement extends \ PDOStatement

protected $PDO = null ;
protected $inputParams = [];
protected $executionTime = 0 ;
protected $resultCount = 0 ;

protected function __construct ( PDO & $PDO ) $this -> PDO = $PDO ;
$this -> executionTime = microtime ( true );
>

final public function getExecutionError ( int $i = 2 ) $executionError = $this -> errorInfo ();

if (isset( $executionError [ $i ]))
return $executionError [ $i ];

final public function getExecutionTime ( $numberFormat = false , $decPoint = ‘.’ , $thousandsSep = ‘,’ ) if ( is_numeric ( $numberFormat ))
return number_format ( $this -> executionTime , $numberFormat , $decPoint , $thousandsSep );

return $this -> executionTime ;
>

final public function getResultCount ( $numberFormat = false , $decPoint = ‘.’ , $thousandsSep = ‘,’ ) if ( is_numeric ( $numberFormat ))
return number_format ( $this -> resultCount , $numberFormat , $decPoint , $thousandsSep );

final public function getLastInsertId () return $this -> PDO -> lastInsertId ();
>

final public function bindValues (array $inputParams ) foreach ( $this -> inputParams = array_values ( $inputParams ) as $i => $value ) $varType = is_null ( $value ) ? \ PDO :: PARAM_NULL : is_bool ( $value ) ? \ PDO :: PARAM_BOOL : is_int ( $value ) ? \ PDO :: PARAM_INT : \ PDO :: PARAM_STR ;

if (! $this -> bindValue (++ $i , $value , $varType ))
return false ;
>

final public function execute ( $inputParams = null ) if ( $inputParams )
$this -> inputParams = $inputParams ;

if ( $executed = parent :: execute ( $inputParams ))
$this -> executionTime = microtime ( true ) — $this -> executionTime ;

final public function fetchAll ( $how = null , $className = null , $ctorArgs = null ) $resultSet = parent :: fetchAll (. func_get_args ());

if (!empty( $resultSet )) $queryString = $this -> queryString ;
$inputParams = $this -> inputParams ;

if ( preg_match ( ‘/(.*)?LIMIT/is’ , $queryString , $match ))
$queryString = $match [ 1 ];

$queryString = sprintf ( ‘SELECT COUNT(*) AS T FROM (%s) DT’ , $queryString );

if (( $placeholders = substr_count ( $queryString , ‘?’ )) < count ( $inputParams ))
$inputParams = array_slice ( $inputParams , 0 , $placeholders );

I think I found a way to execute a protected SQL query and at the same time find out the number of affected records.
I have the table ‘tbl_users’ with the following fields: id, login, password, age

const DB_DRIVER = «mysql»;
const DB_HOST = «localhost»;
const DB_NAME = «my_db_name»;
const DB_LOGIN = «root»;
const DB_PASS = «root»; //OpenServer.

$connectionString = DB_DRIVER.’:host=’.DB_HOST.’;dbname=’.DB_NAME;
try
//Connect to database.
$db = new PDO($connectionString, DB_LOGIN, DB_PASS);
>
catch(PDOException $e)
die(«Error: «.$e->getMessage());
>
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//Decreasing age for user ‘nick1’.
$prep1 = $db->prepare(«UPDATE tbl_users SET age=age-1 WHERE login=’nick1′»);

//Increasing age for user ‘nick2’.
$prep2 = $db->prepare(«UPDATE tbl_users SET \r\n age=age+1 WHERE login=’nick2′»);

//Start transaction.
$db->beginTransaction(); //Table type must be InnerDB!

//We assume that everything will be fine.
$flagDone = true;

//The exec() method returns the number of rows affected by the query.
//$prep1->queryString is already an escaped SQL string.
$result = $db->exec($prep1->queryString);
if($result==false || $result!=1) //var_dump($result) — int(1) or bool(false).
$flagDone = false;

$result = $db->exec($prep2->queryString);
if($result==false || $result!=1)
$flagDone = false;

if($flagDone)
if($db->commit())
echo «Transaction was successful»;
>
else echo «Transaction fail»;
$db->rollback();
>
echo «
«;

• PDO
  • Введение
  • Установка и настройка
  • Предопределённые константы
  • Подключения и управление подключениями
  • Транзакции и автоматическая фиксация изменений
  • Подготовленные запросы и хранимые процедуры
  • Ошибки и их обработка
  • Большие объекты (LOB)
  • PDO
  • PDOStatement
  • PDOException
  • Драйверы PDO

  Источник

  A Strategy for using PDO? (Pushing results into an Array)

  Problem I’m facing is that I need the first method to return an array or array of arrays like the second and third.

  $this->database_top->quote($query); // quote it $pdoStatement = $this->database_top->prepare($query); // prepare it $results = $pdoStatement->execute($parameterArray); // execute it 

  How can I modify my code so that all 3 methods return arrays or array of arrays?

  Iterating over query()

  $result_array = $this->DatabaseObject->_pdoQuery( 'multiple', 'tweet_model' ); foreach( $result_array as $array_1d )

  2 Answers 2

  query does not return results, so I’m not sure what you meant on the first example. query returns a PDOStatement. It’s basically the same as:

  $qry = $db->query(". "); //equiv: $qry = $db->prepare(". "); $qry->execute(); 

  As vicTROLLA noted, the simplest solution is to just use:

  $results = $stmt->fetch(PDO::FETCH_ASSOC); 

  However, if you need to process the results as you go for some reason, you could always loop over them manually.

  $stmt = . ; $results = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) < $results[] = $row; >//use or return $results 

  $results will thus always be an array with 0 or more arrays inside of it.

  I find it useful to build arrays where the array key is the primary key of the record:

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC))

  Also, I suspect that you’ve misunderstood the purpose and functionality of quote. It is used for escaping strings that you are going to interpolate into a query, not for magically escaping all values in a query (hence $db->quote($query) makes no sense).

  Even worse is that drivers are not required to support quote. (Though it does at least return false if there’s no support.)

  prepare is massively preferred over quote .

  Источник

  How to force PDOStatement->fetchAll to return array of objects?

  I am writing my own simply ORM using PDO. My question is if you can force PDOStatement::fetchAll() method to return array of objects of stdClass? For example:

  $result = $q->fetch_all(/* some magic here */); print_r($result); 

  Array ( [0] => stdClass Object ( [NAME] => pear [COLOUR] => green ) [1] => stdClass Object ( [NAME] => watermelon [COLOUR] => pink ) ) 

  Is this posible? NAME and COLOUR are of course names of columns. I read documentation but I didn’t find anything interesting.

  I don’t think you read the documentation clearly enough: «PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set»

  4 Answers 4

  Use $result = $q->fetchAll(PDO::FETCH_OBJ);

  Oh my. I miss this in the documentation. I thought that this is only about the first level (arrays in my case). Thanks.

  Nothing, but the PDO has several modes, even define a name of an object that is defined getters, see PDO:: FETCH_CLASS

  It is a good practice when demonstrating a documented feature such as a flag for a function argument to add a link to the documentation page that mentions this flag or copy and paste the necessary bits for the docs itself.

  PDO::FETCH_OBJ is documented in PDOStatement::fetch and not mentioned in PDOStatement::fetchAll which probably adds to the confusion.

  In my experience with PDO::FETCH_CLASS fetchAll returns array of objects in php 5.6 and array of arrays in php 5.4. This also contributes to confusion. Are there php.ini settings that can modify it?

   prepare("SELECT name, colour FROM fruit"); $sth->execute(); $result = $q->fetchAll(PDO::FETCH_OBJ); //$result contains an array of stdObjects ?> 

  However even cooler way is to get PDO to instantiate your own class and populate the properties for you:

  Example #4 Instantiating a class for each result

  The following example demonstrates the behaviour of the PDO::FETCH_CLASS fetch style.

    $sth = $dbh->prepare("SELECT name, colour FROM fruit"); $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_CLASS, "fruit"); //$result contains an array of fruit objects ?> 

  Источник