Select MySQL DB Data With PHP PDO

One simple way of selecting records from a MySQL database using PHP Data Objects (PDO) is with the use of the function query(). This function executes an SQL statement, which in this example is the SELECT Statement. And will return a result set as a PDOStatement Object.

Requirements:

• PHP
• PHP Data Objects
• PDO Drivers
• MySQL Functions (PDO_MYSQL)
• MySQL SELECT Statement

How to select MySQL database records using PDO in PHP.

This assumes that a successful connection to MySQL Database has already been made. And that the connection object was assigned to $dbh.

Select and display all records using PDO query.

Consider below as the employees TABLE inside a MySQL DATABASE.

The objective is to get all data from employees TABLE.

$results = $dbh->query("SELECT * FROM employees");
foreach ($results AS $result) {
    echo '<pre>';
        print_r($result);
    echo '</pre>';
}

Where.

• • $dbh is the connection object. Check How to connect to MySQL database.
  • “SELECT * FROM employees” is the SELECT Statement.
  • employees is the name of the TABLE.

Result.

Array
(
    [id] => 1
    [0] => 1
    [first_name] => John Michael
    [1] => John Michael
    [last_name] => Jhonson
    [2] => Jhonson
)
Array
(
    [id] => 2
    [0] => 2
    [first_name] => Peter Rick
    [1] => Peter Rick
    [last_name] => Williams
    [2] => Williams
)
Array
(
    [id] => 3
    [0] => 3
    [first_name] => Mary Ann
    [1] => Mary Ann
    [last_name] => Thomas
    [2] => Thomas
)
Array
(
    [id] => 4
    [0] => 4
    [first_name] => Shelly Jean
    [1] => Shelly Jean
    [last_name] => Walsh
    [2] => Walsh
)

Note that for security purposes, using the PDO query() function requires that all data inside the statement query should be properly escaped.

Select and display all records using PDO prepare and PDOStatement execute.

Another PDO way of selecting records from a MySQL database in PHP is using the combination of PDO prepare() and PDOStatement execute() functions. This approach is recommended than the PDO query() function when there are user input values to be included on the query. One good reason is that PDO prepare() automatically escapes the given set of parameters. Either the named (:name) or the question mark (?) parameter marker can be used to bind any user input value in the query. It also has the better performance for queries that needs to be issued multiple times.

The goal now is to select and display the record with the id equal to 2 using the named (:name) parameter.

$id = 2;
$sth = $dbh->prepare("SELECT * FROM employees WHERE id = :id");
$sth->execute(array(":id"=>$id));
echo '<pre>';
	print_r($sth->fetchAll());
echo '</pre>';

Result.

Array
(
    [id] => 2
    [0] => 2
    [first_name] => Peter Rick
    [1] => Peter Rick
    [last_name] => Williams
    [2] => Williams
)

Next target is to select and display the record with the id equal to 3 using the question mark (?) parameter.

$id = 3;
$sth = $dbh->prepare("SELECT * FROM employees WHERE id = ?");
$sth->execute(array($id));
echo '<pre>';
	print_r($sth->fetchAll());
echo '</pre>';

Result.

Array
(
    [id] => 3
    [0] => 3
    [first_name] => Mary Ann
    [1] => Mary Ann
    [last_name] => Thomas
    [2] => Thomas
)

Notes:

• Always sanitize user inputs.

References:

• MySQL SELECT Statement
• PDO::query
• PDO::prepare
• PDOStatement::execute
• Access MySQL DB using PDO in PHP