How to execute a PDO Prepared Statement Query Example with MySQL

I recently decided to make the switch to using MySQL PDO Prepared Statements for my database queries. I wanted to add an additional layer of protection against SQL injection and PDO prepared statements are a perfect solution. When used properly PDO prepared statements are an excellent defense against SQL injections.

For this example, I’ll be using named placeholders. It is possible to use question marks for the placeholders but I’ve found it easier to keep track of your parameters when they are formally named. See PDO bindParam for more information.

There is much online discussion about defining the connection and setting the connection attributes. See PDO setAttribute for more information. The attribute ATTR_EMULATE_PREPARES warrants review.

Note: This example exposes the database connection credentials. It is recommended you store these values in a folder outside of the public root and use an include to obtain the connection values.

	
// define the connection values
function dbConn() 
{ 
	$conn[0] = 'localhost';		// server
	$conn[1] = 'xx_dbuser';		// user
	$conn[2] = 'xxxxxxxxx';		// password
	$conn[3] = 'xx_db';		// db
	return $conn;
} 

// make the connection
function pdoConnection() 
{ 
	$conn = dbConn();
	$pdoConnection = new PDO("mysql:host=$conn[0];dbname=$conn[3]", $conn[1], $conn[2]);
	$pdoConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
	$pdoConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$pdoConnection->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");		
	return $pdoConnection;
}

// SELECT query example

	$loan_id = 123;
	$member_id = 55555;

	$pdoConnection = pdoConnection();			// make the connection
	$query = "SELECT * FROM tbl_loans";			// build the query
	$query .= " WHERE loan_id = :loan_id";			// :loan_id is our named placeholder
	$query .= " AND loan_member = :member_id";
	$query .= "  ORDER BY loan_date";
	$pdoStatement = $pdoConnection->prepare($query);	// prepare the query
	$pdoStatement->bindParam(':loan_id', $loan_id);		// bind the parameters
	$pdoStatement->bindParam(':member_id', $member_id);
	$pdoStatement->execute();				// execute
	$row_count = $pdoStatement->rowCount();			// get a row count
	$result = $pdoStatement->fetchAll();			// fetch results

	if ($row_count > 0)					// process results
	{
		foreach ($result as $row)
		{
			$loan_date = $row['loan_date'];
			$loan_amount = $row['loan_amount'];
			$loan_rate = $row['loan_rate'];
		}
	}

	$pdoConnection = null;					// close the connecion

// UPDATE query example

	$loan_id = 123;
	$member_id = 55555;

	$pdoConnection = pdoConnection();			// make the connection
	$query = "UPDATE tbl_loans";				// build the query
	$query .= " SET loan_member = :member_id";
	$query .= " WHERE loan_id = :loan_id";
	$pdoStatement = $pdoConnection->prepare($query);	// prepare the query
	$pdoStatement->bindParam(':loan_id', $loan_id);		// bind the parameters
	$pdoStatement->bindParam(':member_id', $member_id);
	$pdoStatement->execute();				// execute

	$pdoConnection = null;					// close the connection

Copy Code

If you have more than one query to execute it is possible and more efficient to open the connection, execute multiple queries, then close the connection.

References

PHP PDO
PDO setAttribute
PDO bindParam