How to connect MySql Database using PHP PDO (PHP Data Objects). What’s the advantage of using PHP data objects.
PHP PDO (PHP data objects) is a database access layer providing a uniform method of access to multiple databases. PHP Data Objects is written to provide a consistent API. Before MySqli and PDO, we used MySql API to connect MySql Database. Let’s see how PDO is better than MySql Api.
Why PDO is better than MySql
1. It supports multiple databases using a single interface.
2. PHP Data Objects uses prepared statement so you don’t need to worry about SQL injection.
3. It provides consistent error handling.
4. Most important, MySql extension is deprecated as of PHP 5.5.0, and will be removed in the future
If you have worked in PHP, then you are familiar with MySQL API syntax.
|
1 2 3 |
// In mysql, we connect to a database using following syntax mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error()); |
In MySQL, we need to escape special characters to prevent our code from SQL injection. To prevent our code from SQL injection we use mysql_real_escape_string() method. But in a Prepared Statement, we don’t need to worry about SQL Injection.
Let’s check the syntax of PDO.
|
1 2 3 |
// Creating $DatabaseConn object $DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password); |
In the PHP Data Objects syntax, We have mentioned driver name which is MySQL and then other details such as host, dbname, username and a password.
Error Handling in PDO
PDO provides us three error handling strategies.
1. PDO::ERRMODE_SILENT – This is a default mode. If you don’t set any error mode it will set this as default.
2. PDO::ERRMODE_WARNING – throws PHP Warnings
3. PDO::ERRMODE_EXCEPTION – throws Exception when error occurs. Always use this mode during development.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
try { $DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password); $DatabaseConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE // Query some Data $result = $DatabaseConn->query("select employeeId from employee"); while($values = $result->fetch(PDO::FETCH_ASSOC)){ echo $values['employeeId']." "; // Print the values } } catch(PDOException $e) { echo $e->getMessage(); // Display error message echo $e->getCode(); // Display error code } |
PDO Fetch Modes
To Fetch a result, it provides following choices.
1. PDO::FETCH_ASSOC – It returns an array. In above example, I use this one.
|
1 2 3 |
while($values = $query->fetch(PDO::FETCH_ASSOC)){ print_r($values['employeeId']); } |
output:
|
1 |
Array ( [employeeId] => 56) |
2. PDO::FETCH_NUM
|
1 2 3 |
while($values = $query->fetch(PDO::FETCH_NUM)){ print_r($values['employeeId']); } |
output:
|
1 |
Array ( [0] => 56) |
3. PDO::FETCH_BOTH
|
1 2 3 |
while($values = $query->fetch(PDO::FETCH_BOTH)){ print_r($values['employeeId']); } |
output:
|
1 |
Array ( [employeeId] => 56 [0]=>56) |
4. PDO::FETCH_OBJ
|
1 2 3 |
while($values = $query->fetch(PDO::FETCH_OBJ)){ print_r($values['employeeId']); } |
output:
|
1 2 3 4 5 |
stdClass Object( [employeeId] => 56 ) |
Fetch Mode Complete Documentation
Insert Operation
Inserting new data or updating a data using PHP Data Objects works in three steps first it prepares, then bind and finally execute.
i) Prepare
ii) Bind
iii) Execute
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Take two inputs $username = $_POST['username'] = raj; $password = $_POST['password'] = '12345'; try { $DatabaseConn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password); $DatabaseConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting ERRMODE $sql = "INSERT INTO users (username,password) VALUES (:username,:password)"; $insert = $DatabaseConn->prepare($sql); $insert->execute(array(':username' => $username, ':password' => $password)); } catch(PDOException $e) { echo $e->getMessage(); } |
Conclusion
If you are working on some PHP projects, try to use Prepared Statements as it is much better than MySql api. If you have any comments or suggestions for the improvement please let us know.
