How to use PHP Data Object - Beginners PHP PDO Tutorial | Coding Cage

How to use PHP Data Object - Beginners PHP PDO Tutorial

By
In this tutorial we will see the basics of PDO(PHP Data Object) the getting started guide for beginners, first and foremost guys if you are still using old and deprecated MySQL extension then please stop using them right away and start use MySQLi or PDO(Recommended) cause MySQL extension is now completely deprecated and got removed from PHP7. Since mysql extension is deprecated it is recommended to switch over from mysql to pdo extension. PDO enables you to write neat and secure code because it has more secure ways which gives extra layer of security in web applications. PDO has it's own fundamental functions and methods which provides the easiest way to access one or more different databases. so let's get started.
How to use PHP Data Object - PDO Tutorial
 

What is PDO ?

PDO is improved MySQL extension which is a neat and consistent way for accessing database, not only MySQL database it also supports many different databases like Microsoft SQL Server, Oracle, ODBC, PostgreSQL, SQLite and many more.

Why PDO ?

Well there are lot's of reasons to use PDO but the main is it reduces the SQL injection possibility because PDO support prepared statement with named parameters and it also has object oriented approach which makes it better performer over other extensions.

PDO MySQL Connection

Following is the connection string to access MySQL database, it is also called DSN(Data Source Name) a simple connection string to connect MySQL database, you can also connect other databases like oracle, sqlite, odbc etc… to get the available drivers use this : print_r(PDO::getAvailableDrivers());

$dbPDO = new PDO("mysql:host=localhost;dbname=database_name",$dbuser,$dbpass);
what if you get error, it also easy to handle error or exceptions

$dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Wrap within try/catch

you should always wrap your PDO connection string in a try/catch to handle exceptions
<?php
 
    define("DB_DRIVER", "mysql");    // database driver, mysql
    define("DB_HOST", "localhost");  // db host
    define("DB_NAME", "dbpdo");      // database name
    define("DB_USER", "root");       // database username
    define("DB_PASS", "");           // database password
 
    try {
       $dbCon = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
       $dbCon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $ex) {
       echo $ex->getMessage();
       die();
    }

connection can be closed by setting $dbCon = null


Create Database and Table :

run the following SQL in your phpmyadmin to create database table.

CREATE DATABASE`dbpdo`;
CREATE TABLE `dbpdo`.`tbl_test` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM ;

Database connection

dbcon.php
PHP PDO connection to MySQL database in try/catch block for error handling.

<?php

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbpdo";

 try
 {
     $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass); 
     $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch(PDOException $e)
 {
     echo "ERROR : ".$e->getMessage();
 }

Create, Update and Delete :

crud.php
in this file you can get the code for insert, update and delete the rows.

<?php

require_once 'dbcon.php';

if(isset($_POST['save']))
{
 $uname = $_POST['uname'];
 $umail = $_POST['umail'];
 
 
 $stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname, :umail)");
 
 $stmt->bindparam(':uname', $uname);
 $stmt->bindparam(':umail', $umail);
 $stmt->execute();

}


if(isset($_GET['delete_id']))
{
 $id = $_GET['delete_id'];
 $stmt = $DBcon->prepare("DELETE FROM tbl_test WHERE id=:id");
 $stmt->execute(array(':id' => $id));
 header("Location: index.php");
}

if(isset($_GET['edit_id']))
{
 $stmt = $DBcon->prepare("SELECT * FROM tbl_test WHERE id=:id");
 $stmt->execute(array(':id' => $_GET['edit_id']));
 $editRow=$stmt->FETCH(PDO::FETCH_ASSOC);
 
}

if(isset($_POST['update']))
{
 $uname = $_POST['uname'];
 $umail = $_POST['umail'];
 $id = $_GET['edit_id'];
 
 $stmt = $DBcon->prepare("UPDATE tbl_test SET username=:uname, email=:uemail WHERE id=:id");
 $stmt->bindparam(':uname', $uname);
 $stmt->bindparam(':uemail', $umail);
 $stmt->bindparam(':id', $id);
 $stmt->execute();
 header("Location: index.php");
}


?>

Data read

index.php
this file contains HTML form to insert new records and read the data from database.
above created 'crud.php' file are included in this file.

<?php
require_once 'crud.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<style type="text/css">
input
{
 width:100%;
}
</style>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO CRUD Tutorial - cleartuts</title>
</head>
<body>
<center>
<form method="post">
<table border="1" width="40%" cellpadding="15">
<tr>
<td><input type="text" name="uname" placeholder="Username" value="<?php if(isset($_GET['edit_id'])){ print($editRow['username']); } ?>" /></td>
</tr>
<tr>
<td><input type="text" name="umail" placeholder="Email" value="<?php if(isset($_GET['edit_id'])){ print($editRow['email']); } ?>" /></td>
</tr>
<tr>
<td>
<?php
if(isset($_GET['edit_id']))
{
 ?>
    <button type="submit" name="update">update</button>
    <?php
}
else
{
 ?>
 <button type="submit" name="save">save</button>
 <?php
}
?>
</td>
</tr>
</table>
</form>

<br />

<?php

$stmt = $DBcon->prepare("SELECT * FROM tbl_test ORDER BY id DESC");
$stmt->execute();
?>
<table border="1" cellpadding="15" width="40%">
<?php
if($stmt->rowCount() > 0)
{
 while($row=$stmt->FETCH(PDO::FETCH_ASSOC))
 {
  ?>
     <tr>
     <td><?php print($row['username']); ?></td>
     <td><?php print($row['email']); ?></td>
        <td><a onclick="return confirm('Sure to Edit ? ')" href="index.php?edit_id=<?php print($row['id']); ?>">EDIT</a></td>
        <td><a onclick="return confirm('Sure to Delete ? ')" href="index.php?delete_id=<?php print($row['id']); ?>">DELETE</a></td>
     </tr>
     <?php
 }
}
else
{
 ?>
    <tr>
    <td><?php print("nothing here...");  ?></td>
    </tr>
    <?php
}
?>
</table>
</center>
</body>
</html>

that's it we have created here CRUD operations using PHP Data Object (PDO).
hope this was helpful for you.



27 comments:

  1. Nice tut about PDO...
    it's good to use pdo or mysqli instead of old mysql extension...

    ReplyDelete
    Replies
    1. What do you need with the table you're using in Create database at the top?
      I ask that,
      Because you used a very different table here.....

      $stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname, :umail)");

      Delete
    2. I just made the correction in the table.
      and thanks a lot for correcting me on it.

      Delete
  2. What is uname and umail?
    I have that changed to username and email

    When I click on save I get this error message

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang '' voor zoeksleutel 'password'' in C:\wamp\www\pdo\crud.php on line 15

    PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang '' voor zoeksleutel 'password' in C:\wamp\www\pdo\crud.php on line 15

    Dubbele ingang '' voor zoeksleutel. = in English means that this: Double input '' for search key
    But I have no password in there, only in dbcon. php is a password

    ReplyDelete
  3. Is this dead

    ReplyDelete
  4. it's working....

    ReplyDelete
  5. I have now 12 fields, with these names, foto, gebruikers_naam, naam, gebdatum, straat, postcode, plaats, telefoon, user_email, club, caramboles, hoogste.
    in English is that: Photo, user_name, name, date of birth, postal code, city, street, phone number. Association, carambole, highest serie.
    So there are some fields with only numbers. like phone number, caramboles and highest serie and there is an date field.
    Now I don't know if that influence has
    but now it doesn't work,
    I get an error message when entering and update at this line: $stmt >-execute ();

    Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

    ReplyDelete
    Replies
    1. i think u have mismatch in prepare statement query and bindparam values...

      $stmt = $DBcon->prepare("INSERT INTO tbl_test(username,email) VALUES(:uname)");

      $stmt->bindparam(':uname', $uname);
      $stmt->bindparam(':umail', $umail);
      $stmt->execute();

      here in above code in insert query values(:uname) and stmt->bindparam(":uname",$uname) must be same , so check them again..

      this type of error occurs when u have mismatch in prepare and bindparam values.

      Delete
  6. Yes that does work like you have it. But it seems that it only works with 2 fields

    I have this, And at the edit and update the same
    [code]
    $stmt = $DBcon->prepare("INSERT INTO tbl_test(foto, user_name, naam, user_email) VALUES(:ufoto, :uname, :unaam, :ugebdatum, :ustraat , :postcode, :uplaats, :telefoon, :umail, :club, :ucaraboles, :hoogste)");

    $stmt->bindparam(':ufoto', $ufoto);
    $stmt->bindparam(':uname', $uname);
    $stmt->bindparam(':unaam', $unaam);
    $stmt->bindparam(':ugebdatum', $ugebdatum);
    $stmt->bindparam(':ustraat', $ustraat);
    $stmt->bindparam(':upostcode', $upostcode);
    $stmt->bindparam(':uplaats', $plaats);
    $stmt->bindparam(':utelefoon', $utelefoon);
    $stmt->bindparam(':umail', $umail);
    $stmt->bindparam(':uclub', $uclub);
    $stmt->bindparam(':ucaramboles', $ucaramboles);
    $stmt->bindparam(':uhoogste', $uhoogste);
    $stmt->execute();
    [/code]

    ReplyDelete
    Replies
    1. Your elements in query in incorrect.

      Delete
  7. Yes I no that works, But not with other field names

    ReplyDelete
  8. Now it works
    I have put the fields one by one and each time tested.
    But I have one more question: On different forums.
    They told me that I not should use this * because that everything can be, But just select what I want to select
    But I don't understand that,
    Because it shows only what's in the database

    And thanks for this tutorial.

    ReplyDelete
  9. one more question.
    I want a textarea in the post, so I also can post and edit messages

    with a ubb code with smileys and bold, underlined and italic.
    that works with onClick="javascript:addSC

    But that do not work,
    Because if I click on a smiley or a button I always get the placeholder back in the textarea

    How does that work in pdo?

    ReplyDelete
  10. ,This is not really safe to use because it can fill in everything, and that is not intended I assume.
    There is nothing done to stop SQL injection

    ReplyDelete
    Replies
    1. ya but it's good to use PDO instead of mysql queries

      Delete
  11. nice tutorial, can u post a tutorial pdo class to handle your database operations

    ReplyDelete
  12. nice tutorial , can u post a tutorial on oop class to handle your database operations

    ReplyDelete
  13. Great beginning php tutorials Very clear and helpful for beginners.

    ReplyDelete
  14. Hi... nice tutorial, but i can't seem to figure out how to get the files via Box. Is there another way to download the files for this tutorial? Thanks!

    ReplyDelete
  15. Hi, Nice tutorial! I'm wondering about nested sql queries with pdo.
    ex:
    foreach($db->query('SELECT id, donor_fname, donor_lname FROM donors') as $row) {
    echo $row['id'].' '.$row['donor_fname'] . '' . $row['donor_lname'].'
    ';
    foreach($db->query('SELECT donors.id, gifts.amount, gifts.date_given FROM donors,gifts WHERE donors.id = gifts.donor_id') as $gift) {
    echo "\t". $gift['id'].' '.$gift['amount'].' '.$gift['date_given'].'
    ';
    }
    }
    will only print the inner (or last executed) query. A result set from a join leaves me to parse the result sets with php employing the same logic that sql queries seem better suited to... next tutorial??

    ReplyDelete
  16. How to use PDO in functions!? Thx!

    ReplyDelete
  17. You can also also pass an array of $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,); at the time of PDO connection

    ReplyDelete