PHP PDO Fetch to return a record from MySQL table with different fetch

WE often need to collect one record from the table to display or to use in our script. To select one record we will use Pdo -> fetch to get next row from a result set. Here are some examples on how to use fetch on different conditions.
We kept our Pdo database connection details in config.php file and called it here.

Podcast on MySQL database management using PHP PDO

Without binding parameters

require "config.php"; // Database connection details. 
$query="SELECT name FROM student WHERE id=5";
$row=$dbo->query($query);
$row=$row->fetch();
echo $row['name'];

FETCH Modes

We will explore different retrieval modes from the result set associated with a PDOStatement object. The mode parameter determines how the fetched row is returned, specifying formats such as associative array, numeric array, object, or a combination of both. This flexibility allows developers to choose the most appropriate data structure for their needs.

Check the code below on how to set default PDO Fetch mode attribute.

FETCH_OBJ

In most of the cases we have to use variables inside our query. The safe way ( to prevent injection attack ) is to use prepared statements where we pass the data separately.

<?Php
error_reporting(E_ERROR | E_PARSE | E_CORE_ERROR);
require "config.php"; // Database connection details. 
//////// End of Database connection /////////
$id='3'; // Collecting one record with id=3
$count=$dbo->prepare("select * from pdo_admin where id=:id");
$count->bindParam(":id",$id,PDO::PARAM_INT,1);

if($count->execute()){
echo " Success <br>";
$row = $count->fetch(PDO::FETCH_OBJ);
print_r($row);
echo "<hr><br>Admin = $row->userid";
echo "<br> pw =$row->password<br>";
echo "<hr>";

}else{
//$row=$count->fetchAll();
print_r($dbo->errorInfo()); 
}
?>

The output of above code is here

( [id] => 3 [userid] => admin3 [password] => test3 [name] => Admin [status] => T )

Admin = admin3
pw =test3

We have used print_r function to display the array
WE have used FETCH_OBJ parameter get property name corresponding to column name. Same way we will try other parameters In your downloaded script ( file name : pdo-fetch.php ) the area to change the part of the code is marked. You can replace them with these lines to check the output

FETCH_NUM

We have to use column number starting from 0 in place of column name , omitted other lines and listed here the changes in above code.

$row = $count->fetch(PDO::FETCH_NUM);
print_r($row);
echo "<hr>";
echo "<br>Admin id = $row[0]";
echo "<br>userid = $row[1]";
echo "<br>password=$row[2]<br>";

The output of above code will be

Array ( [0] => 3 [1] => admin3 [2] => test3 [3] => Admin [4] => T )
Admin id = 3
userid = admin3
password=test3

FETCH_ASSOC

Returned array will have column name ( field name ) as index. Here is the code

$row = $count->fetch(PDO::FETCH_ASSOC);
print_r($row);
echo "<hr>";
echo "<br>Admin id = $row[id]";
echo "<br>userid = $row[userid]";
echo "<br>password=$row[password]<br>";

The output of the above is here

Array ( [id] => 3 [userid] => admin3 [password] => test3 [name] => Admin [status] => T )
Admin id = 3
userid = admin3
password=test3

FETCH_BOTH

Returns both column name ( field name ) and number index ( starting from 0 ) in the array.

$row = $count->fetch(PDO::FETCH_BOTH);
print_r($row);
echo "<hr>";
echo "<br>Admin id = $row[0]";
echo "<br>userid = $row[userid]";
echo "<br>password=$row[2]<br>";

Output is here

Array ( [id] => 3 [0] => 3 [userid] => admin3 [1] => admin3 [password] => test3 [2] => test3 [name] => Admin [3] => Admin [status] => T [4] => T )
Admin id = 3
userid = admin3
password=test3

FETCH_LAZY

This returns both FETCH_OBJ and FETCH_BOTH, code is here

$row = $count->fetch(PDO::FETCH_LAZY);
print_r($row);
echo "<hr>";
echo "<br>Admin id = $row[0]";
echo "<br>userid = $row[userid]";
echo "<br>password=$row->password<br>";

How to set PDO attribute for default fetch mode

Default setting is FETCH_ASSOC. We can change this any other fetch type.

$id='1'; // Collecting one record with id=1
$dbo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$count=$dbo->prepare("SELECT * FROM student where id=:id");
$count->bindParam(":id",$id,PDO::PARAM_INT,3);

if($count->execute()){
$row = $count->fetch(); // Now, it fetches as an object
echo "Name = $row->name"; // Accessing as object

}else{
print_r($dbo->errorInfo()); 
}

Here are the changes required to set the fetch mode to FETCH_NUM

$dbo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
// Other lines remain same as above. 
echo "Name = $row[1]"; // Accessing numerically indexed array

Similarly we can set the default fetch mode to any other type.

SUMMARY : PDO Fetch Modes and their Use Cases

1. PDO::FETCH_ASSOC

Returns results as an associative array where column names are keys. Use when you want to access data by column name for readability.

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

2. PDO::FETCH_NUM

Returns results as an indexed array, useful for memory optimization or when working with column numbers.

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    print_r($row);
}

3. PDO::FETCH_OBJ

Returns rows as objects, ideal for object-oriented programming where accessing fields like properties is needed.

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo $row->name;
}

4. PDO::FETCH_BOTH

Returns both an indexed array and associative array. Use this when you need flexible access to both column numbers and names.

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {
    print_r($row);
}

5. PDO::FETCH_CLASS

Fetches data into an instance of a specified class, great for custom data structures.

class User {
    public $name;
}

$stmt = $pdo->query("SELECT * FROM users");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
while ($row = $stmt->fetch()) {
    echo $row->name;
}

Sample Student table SQL dump
PDO References Collecting multiple records by using parameterized queries

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.