Access MySQL database using PDO in PHP

June 23, 2013 | 831 views | Comments

Most, if not all new PHP server installations now a days include the PHP Data Objects API (PDO_MySQL), this extension is recommended if there's a chance of connecting to different DBMS (Database Management Systems) in the future. Because same functions will be used to issue queries and to fetch data, only a single API needs to be learned.

For example.

$dbh = new PDO('mysql:host=LOCALHOST;dbname=DATABASE','USER','PASSWORD');

Where:

  1. LOCALHOST = the name or ip address of the host where the MySql server is running.
  2. DATABASE = the name of the database to access.
  3. USER = the user name of the MySql account who has access to the database.
  4. PASSWORD = the password of the user.

Notes:

  • If there are any connection errors, the script will be terminated, and a back trace will be displayed.
  • When the script ends, PHP will automatically close the connection. However, there's also a way to explicitly close it, and that is by assigning a NULL value to the object. Thus, $dbh = null;
  • The complete list of available PDO drivers can be found on this link.

 

Other Examples

Explicitly handle connection errors.

try{
	$dbh = new PDO('mysql:host=LOCALHOST;dbname=DATABASE','USER','PSSWORD');
	echo 'Connection to MySQL server via PDO was successful...';
}catch(PDOException $e){
	print "Error!: " . $e->getMessage() . "<br/>";
	die();
}

 

Possible errors


SQLSTATE[42000] [1044] Access denied for user 'USER'@'%' to database 'DATABASE'

1. Possible cause : USER was not granted a PRIVILEGE to the DATABASE.

   Possible solution : GRANT the USER a PRIVILEGE to the DATABASE.

2. Possible cause : DATABASE does not exists.

   Possible solution : Check if the DATABASE name was correctly spelled out.


SQLSTATE[28000] [1045] Access denied for user 'USER'@'LOCALHOST' (using password: YES)

1. Possible cause : Incorrect USER and/or incorrect PASSWORD.

   Possible solution : Check if the USER and/or the PASSWORD is/are correctly spelled out.