Access MySQL DB using PDO in PHP

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.

Requirements:

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

How to connect to MySQL database.

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

Where.

• LOCALHOST = the name or ip address of the host where the MySql server is hosted.
• DATABASE = the name of the database to access.
• USER = the user name of the MySql account who has access to the database.
• PASSWORD = the password of the USER.

Other Examples.

Explicitly handle connection errors.

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

Possible errors.

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

• Possible cause : USER was not granted a PRIVILEGE to the DATABASE.
• Possible solution : GRANT the USER a PRIVILEGE to the DATABASE.

• Possible cause : DATABASE does not exists.
• Possible solution : Check if the DATABASE name is correct.

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

• Possible cause : Incorrect USER and/or incorrect PASSWORD.
• Possible solution : Check if the USER and/or the PASSWORD is/are correct.

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;

References:

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