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 i want to explain about PDO(PHP Data Objects), this is another and awesome method to interact with MySQL database, PDO are different from the old procedural method and it is improved MySQL extension, and nowadays most of PHP programmers uses PDO extension instead of old MySQL, so I’m going to create here CRUD operations using PDO, It’s easy to create and handle such operations with database using PDO and how to use PDO functions lets see it in detail.
How to use PHP Data Object - PDO Tutorial
 

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.



24 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
  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