Select MySQL database records using PDO in PHP

June 29, 2013 | 949 views | Comments

One simple way of selecting records from a MySQL database using PDO is with the use of the function query(). This function executes an SQL select statement, and returns a result set as a PDOStatement object.

For example.

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

Select and display all records.

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

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

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

For example.

Select and display a record where the id is from a user-input using the named (:name) parameter.

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

The function fetchAll() will return an array containing all of the result set rows, this includes both with the numeric and string keys.

Select and display a record where id is from a user-input using the question mark (?) parameter.

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

Check if there are any records found.

$id = $_GET['id'];
$sth = $dbh->prepare("SELECT * FROM TABLES WHERE id = ?");
$sth->execute(array($id));
$results = $sth->fetchAll();
if($results){
	echo '<pre>';
		print_r($results);
	echo '</pre>';
}
else{
	echo 'No records found...';
}