Method for Retrieving Result Set Using PDO in PHP

This article example describes the method of obtaining the result set using PDO in php. Shared for everyone's reference, as follows:

fetch() method

The fetch() method is used to obtain the next row of the result set, the syntax is as follows:

mixed PDOStatement::fetch([int fetch_style[, int cursor_orientation[, int cursor_offset]]])

The parameter fetch_style controls the return method of the result set

PDO::FETCH_ASSOC -- associative array form
PDO::FETCH_NUM -- numeric indexed array form
PDO::FETCH_BOTH -- Both array forms are present, which is the default
PDO::FETCH_OBJ -- in the form of an object, similar to the previous mysql_fetch_object()
PDO::FETCH_BOUND--return results in the form of a boolean, while the column values obtained are assigned to the specified variables in the bindParam() method.
PDO::FETCH_LAZY--return results in the form of an associative array, a numeric indexed array, and an object3form to return results

cursor_orientation: A scrolling cursor of the PDOStatement object, which can be used to retrieve a specific row.
cursor_offset: The offset of the cursor

For example:

Execute SQL query statements through prepared statements prepare() and execute() in PDO, and complete the cyclic output of data with while() statements and fetch() method

$dbms='mysql';//Database type
$dbName='admin';//Used database
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database host name
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
$pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$query="select * from user";//The SQL statement to be executed
$res=$pdo->prepare($query);//Prepare the query statement
$res->execute();
while($result=$res->fetch(PDO::FETCH_ASSOC)){
echo $result['id'].' '. $result['username'].' '. $result['password'].'<br>';
  }
}catch(Exception $e){
die("Error!:".$e->getMessage().'<br>';
}

The running result is:

1 107lab e10adc3949ba59abbe56e057f20f883e
4 admin 123456
5 admin 123456

fetchAll() method

The fetchAll() method is used to get all rows in the result set, and its return value is a binary array containing all the data in the result set. The syntax is as follows:

array PDOStatement::fetchAll([int fetch_style[,int column_index]])

Parameter description:

fetch_style: Controls the display method of data in the result set.
column_index: Field index.

For example:

$dbms='mysql';//Database type
$dbName='admin';//Used database
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database host name
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
$pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$query="select * from user";//The SQL statement to be executed
$res=$pdo->prepare($query);//Prepare the query statement
$res->execute();
$result=$res->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
}catch(Exception $e){
die("Error!:".$e->getMessage().'<br>';
}

The running result is:　

Array
(
  [0] => Array
    (
      [id] => 1
      [username] => 107lab
      [password] => e10adc3949ba59abbe56e057f20f883e
    )
  [1] => Array
    (
      [id] => 4
      [username] => admin
      [password] => 123456
    )
  [2] => Array
    (
      [id] => 5
      [username] => admin
      [password] => 123456
    )
)

At this time, you can use foreach to traverse this two-dimensional array

foreach($result as $val){
echo $val['username'].'<br>';
}

The running result is:

107lab
admin
admin

fetchColumn() method

The syntax of the fetchColumn() method to get the value of the specified column in the next row of the result set is as follows:

string PDOStatement::fetchColumn([int column_number])

Optional parameter column_number sets the index value of the column in the row, starting from 0. If this parameter is omitted, it will start from the1Start taking values from the column

For example:

Get the value of the specified column in the next row of the result set through the fetchColumn() method. (Or the value of the first column id.)

$dbms='mysql';//Database type
$dbName='admin';//Used database
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database host name
$dsn="$dbms:host=$host;port=3306;dbname=$dbName";
try{
  $pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
  $query="select * from user";//The SQL statement to be executed
  $res=$pdo->prepare($query);//Prepare the query statement
  $res->execute();
  echo $res->fetchColumn(0).'<br>';
  echo $res->fetchColumn(0).'<br>';
  echo $res->fetchColumn(0).'<br>';
}catch(Exception $e){
  die("Error!:".$e->getMessage().'<br>';
}

The running result is:

1
4
5

Readers who are interested in more about PHP-related content can check out the special topic on this site: PHP Database Operation Skills Summary Based on PDO, PHP+Oracle Database Program Design Skills Summary, PHP+MongoDB Database Operation Skills Summary, PHP Object-Oriented Program Design Tutorial, PHP String (string) Usage Summary, PHP+MySQL Database Operation Tutorial and PHP Common Database Operation Skills Summary

I hope the content described in this article will be helpful to everyone in PHP program design.

Statement: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email for reporting, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)