How to fetch data from MySQL using PDO

In this tutorial we will tell you how to fetch data from database using pdo object. To query data from the MySQL, follow the steps below:

First, connect to a MySQL database. Check it out the connecting to MySQL database using PDO tutorial for detail information.

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

Then, construct a SELECT statement and execute it by using the query() method of the PDO object.

The query() method of the PDO object returns a PDOStatement object, or false on failure.

Next, set the PDO::FETCH_ASSOC fetch mode for the PDOStatement object by using the setFetchMode() method. The PDO::FETCH_ASSOC mode instructs the fetch() method to return a result set as an array indexed by column name

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

After that, fetch each row from the result set until there is no row left by using the fetch() method of the PDOStatement object.

<table class="table table-bordered table-condensed">
    <thead>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Job Title</th>
        </tr>
    </thead>
    <tbody>
        <?php while ($r = $q->fetch()): ?>
        <tr>
            <td><?php echo htmlspecialchars($r['lastname'])?></td>
            <td><?php echo htmlspecialchars($r['firstname']); ?></td>
            <td><?php echo htmlspecialchars($r['jobtitle']); ?></td>
        </tr>
        <?php endwhile; ?>
    </tbody>
</table>

Putting it all together.

<?php
require_once 'dbconfig.php';
 
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 
    $sql = 'SELECT lastname, 
                   firstname, 
                   jobtitle
            FROM employees
            ORDER BY lastname';
 
    $q = $conn->query($sql);
    $q->setFetchMode(PDO::FETCH_ASSOC);
 
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP MySQL Query Data Demo</title>
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/style.css" rel="stylesheet">
</head>
<body>
<div id="container">
    <h1>Employees</h1>
    <table class="table table-bordered table-condensed">
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Job Title</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($r = $q->fetch()): ?>
            <tr>
                <td><?php echo htmlspecialchars($r['lastname'])?></td>
                <td><?php echo htmlspecialchars($r['firstname']); ?></td>
                <td><?php echo htmlspecialchars($r['jobtitle']); ?></td>
            </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
</body>
</div>
</html>

PHP-PDO-SELECT

PHP MySQL Querying data using PDO prepared statement

In practice, we often pass the argument from PHP to the SQL statement e.g., get the employee whose last name ends with son. To do it securely and avoid SQL injection attack, you need to use the PDO prepared statement.

Let’s take a look at the following example:

<?php
 
require_once 'dbconfig.php';
 
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 
    $sql = 'SELECT lastname, firstname, jobtitle 
            FROM employees
            WHERE lastname LIKE ?';
 
    $q = $conn->prepare($sql);
    $q->execute(array('%son'));
    $q->setFetchMode(PDO::FETCH_ASSOC);
 
    while ($r = $q->fetch()) {
        echo sprintf('%s <br/>', $r['lastname']);
    }
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}
?>

How the script works.

  • First, we use a question mark (?) in the SELECT statement. PDO will replace the question mark in the query by the corresponding argument. The question mark is called positional placeholder.
  • Next, we call the prepare() method of the PDO object to prepare the SQL statement for the execution.
  • Then, we execute the statement by calling the execute() method of the PDOStatement object. In addition, we pass an argument as an array to replace the placeholder in the SELECT statement. By doing this, the SELECT statement will be translated as follows:
SELECT lastname, firstname, jobtitle 
FROM employees
WHERE lastname LIKE '%son'

After that, we set the fetch mode for the PDOStatement object.

  • Finally, we fetch each row of the result set, and display the last name field.
  • PHP provides you with another way to use placeholders in the prepared statement called named placeholder. The advantages of using the named placeholder are:

More descriptive.
If the SQL statement has multiple placeholders, it is easier pass the arguments to the execute() method.

Let’s take a look at the following example:

<?php
 
require_once 'dbconfig.php';
 
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 
    $sql = 'SELECT lastname, firstname, jobtitle 
            FROM employees
            WHERE lastname  LIKE :lname OR
                  firstname LIKE :fname';
 
    $q = $conn->prepare($sql);
    $q->execute(array(':fname' => 'Le%',
                      ':lname' => '%son'));
    $q->setFetchMode(PDO::FETCH_ASSOC);
 
    while ($r = $q->fetch()) {
        echo sprintf('%s <br/>', $r['lastname']);
    }
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}

The :lname and :fname are the named placeholders. They are substituted by corresponding argument in the associative array that we pass to the execute method.

Please follow and like us:



3 Replies to “How to fetch data from MySQL using PDO”

Leave a Comment

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