IDIOT DEVELOPER

Querying Data using PDO (PHP Data Object)
Querying Data using PDO (PHP Data Object)

This article is about fetching or retrieving data from the database using PDO (PHP Data Object). So if you are new to PDO then see some of my previous post.

 

1. Introduction to PDO

2. Connecting to Database using PDO

3. Exceptions in PDO

4. Prepared Statement in PDO .

 

Querying data is initially more interesting than creating data, and as this is a tutorial on data connectivity rather than on SQL, we’ll get straight to querying and visit creation, insertion, updating and deletion later.

 

In PDO we fetch the data from database using the fetch(), and fetchAll()  function of the statement handler.

 

fetch() Fetches the next row from a result set.

fetchAll() Returns an array containing all of the result set rows.

 

There are a variety of ways to fetch the data , lets discuss them first.

 

1. PDO::FETCH_ASSOC: returns an array indexed by column name.

2. PDO::FETCH_BOTH (default): returns an array indexed by both column name and number.

3. PDO::FETCH_BOUND: Assigns the values of your columns to the variables set with the ->bindColumn() method.

4. PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist.

5. PDO::FETCH_INTO: Updates an existing instance of the named class.

6. PDO::FETCH_LAZY: Combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating the object variable names as they are used.

7. PDO::FETCH_NUM: returns an array indexed by column number.

8. PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names.

 

We use setFetchMode()  to set the mode in which we wants to fetch the data.

 


$pdo_stmt->setFetchMode(PDO::FETCH_ASSOC);

 

PDO::FETCH_BOTH

This is the default mode to fetch the data from the database. In this we need not to specify the mode.

 


$pdo_stmt = $pdo->query('SELECT name, email, password from user');
 
while($row = $STH->fetch()) {
    echo $row['name'] . "\n";
    echo $row['email'] . "\n";
    echo $row['password'] . "\n";
}

 

PDO::FETCH_ASSOC

This fetch type creates an associative array, indexed by column name. This should be quite familiar to anyone who has used the mysql/mysqli extensions. Here’s an example of selecting data with this method:

 


$pdo_stmt = $pdo->query('SELECT name, email, password from user');

$pdo_stmt->setFetchMode(PDO::FETCH_ASSOC);
 
while($row = $STH->fetch()) {
    echo $row['name'] . "\n";
    echo $row['email'] . "\n";
    echo $row['password'] . "\n";
}

 

PDO::FETCH_NUM

This fetch type creates an associative array, indexed by column number. Here’s an example of selecting data with this method:

 


$pdo_stmt = $pdo->query('SELECT name, email, password from user');

$pdo_stmt->setFetchMode(PDO::FETCH_NUM);
 
while($row = $STH->fetch()) {
    echo $row['name'] . "\n";
    echo $row['email'] . "\n";
    echo $row['password'] . "\n";
}

 

fetchAll

The fetchAll function of the PDOStatment class returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch, or FALSE on failure.


$stmt = $db->prepare("SELECT name, colour FROM fruit");
$stmt->execute();

$result = $stmt->fetchAll();
print_r($result);

 

The above result looks like this

 

Array
(
    [0] => Array
        (
            [name] => apple
            [0] => apple
            [colour] => red
            [1] => red
        )

    [1] => Array
        (
            [name] => pear
            [0] => pear
            [colour] => green
            [1] => green
        )

    [2] => Array
        (
            [name] => watermelon
            [0] => watermelon
            [colour] => pink
            [1] => pink
        )

)

Leave a Reply

Your email address will not be published. Required fields are marked *