Dhwany Technology Pvt. Ltd
dhwany technology menu icon

Blogs

Learn fetch single records or fetch all records from database using PDO (PHP data Object)

Learn fetch single records or fetch all records from database using PDO (PHP data Object)

sachin     20 Apr 2018     PHP      NA     

In this blog, we are going to discuss that how to fetch data from MySQL database through PHP data object (PDO). PDO API allows fetching a single row or more than one row in the result set by its predefined methods. 

PDOStatement::fetch() is used to fetch single row result. It returns the result as an array or an object, which depends on passing the constants on the first parameter. 

PDOStatement::fetchAll() is used to fetch entire rows of the table, which columns are selected in the prepared statement.

Here I’m going to show that how it works.

try{
       $DTPDO = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
       $DTPDO ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch(PDOException $e){
             echo 'ERROR: ' . $e->getMessage();
             }

$statement = “SELECT * FROM topics”;
$DTVariable = $DTPDO->prepare($statement);
$DTVariable->execute();
$DTVariable->fetch();

When the following code is executed. It selects the entire column in the topics table. The result will be got with default constant PDO::FETCH_BOTH. It returns an array with indexed name and indexed position as the result set.

Array
(
    [title] => PHP data object (PDO) advance series: create database connection
    [2] => PHP data object (PDO) advance series: create database connection
)

The constant is used for fetch the result in different ways. It’s the first parameter which is passed in fetch method. I am going to show that how to use the constant in fetch method and what effect it has on the result. For example $DTVariable->fatch(PDO::FETCH_*);

PDO::FETCH_ASSOC

This array returns an associated array with key and value pair. Here, the tag, id and title are keys and they contain the deferent values.

Array
(
    [tag] => PHP
    [id] => 4
    [title] => PHP data object (PDO) advance series: create database connection
)

PDO::FETCH_NUM

In this case, It returns the result with the indexed position. 

Array
(
    [0] => PHP
    [1] => 4
    [2] => PHP data object (PDO) advance series: create database connection
)

PDO::FETCH_OBJ

It returns each row of the database as the object properties with the name of the column. 

stdClass Object
(
    [tag] => PHP
    [id] => 4
    [title] => PHP data object (PDO) advance series: create database connection
)

Here I'm going to show that how we can use more than one constant in a fetchAll() method and what will be its effect on the result. For example, many time developers need to fetch data by parent-child relationship and publish in the same way. By passing PDO::FETCH_GROUP|PDO::FETCH_ASSOC as constant in fetchAll() method and get the result which looks like this:

$DTVariable = $DTPDO->prepare(“SELECT tag, id, title, image FROM topics”);
$DTVariable->execute();
$DTVariable->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

//The result will be shown, while execute the query.

Array
(
    [PHP] => Array
        (
            [0] => Array
                (
                    [id] => 4
                    [title] => PHP data object (PDO) advance series: create database connection
                    [image] => php-PDO.jpg
                )
        )
)

In this case, it created the group by the first column in MySQL query which is passed into the prepared statement. All data has been fetched in the nested array which is connected to the tag “PHP”.

Similarly, the result will be affected by passing the constant in fetchAll() method. Suppose, we want to fetch all blog’s titles, which are grouped by tag’s name. So, the constant PDO::FETCH_COLUMN with PDO::FETCH_GROUP can be used.

$DTVariable = $DTPDO->prepare(“SELECT tag, blog_title FROM topics”);
$DTVariable->execute();
$DTVariable->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);

//The result will be shown, while execute the query.

Array
(
    [PHP] => Array
        (
            [0] => PHP data object (PDO) advance series: create database connection
        )
    [digital marketing] => Array
        (
            [0] => Salman Khan Sentenced To 5 Years In Jail, Other Actors Acquitted In Blackbuck Poaching Case
            [1] => Promotional SMS
            [2] => Bulk SMS Services
            [3] => Hiring a Blogger and SEO Services
            [4] => Competitive Analysis
            [5] => What You Might Not Need In Your Content
        )
)

This is displayed by the following example, how to get results with the associated array grouped by specific columns. Apart from this, I'm going to show two more ways to get results.

PDOStatement::fetchColumn() returns a single column from the next row of a result set or FALSE if there are no more rows.

PDOStatement::fetchObject() returns the next row and returns it as an object.

if you learn more about this topic. Visit on http://php.net/manual/en/pdostatement.fetchall.php


comment icon
asad shafiq 23 May 2018
nice info

Leave Comments