Connecting database with PDO [Part 2]

Jun 5, 2013 | | Say something
Hello,

connecting db part 2
This is part 2 tutorial on Connecting database with PDO. If this is your first visit so i recommend you to read my Part 1 tutorial on this to better understand this post.

In this tutorial i will explain you how to Insert,Update and Delete the table with PDO commands.
First of all, all the queries in PDO just like sql queries and if you have worked with mysql_* functions, you will easily catch this.

Inserting row

 

Do you remember try & catch? simply wrap the code between them.
<?php
try {
     $conn = new PDO(‘mysql:host=$hostname;dbname=myDB’,$username,$password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $st->prepare(“INSERT INTO tblname values(‘:name’)”);

     $st=bindValue(“:name”,’James Bond’,PDO::PARAM_STR);

     $st->execute();
    //counting the affected rows
   
    $st->rowCount();
}
catch(PDOException $e) {
          
         echo “ERROR:” getMessage();
 }
?>

Updating the record

We need to run update query for this like below:
<?php
try {
     $conn = new PDO(‘mysql:host=localhost;dbname=myDB’,$username,$password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $st->prepare(“UPDATE tblname SET name=:name WHERE id=:id”);

     $st=bindValue(“:name”,’Jonhy English’,PDO::PARAM_STR);
     $st=bindValue(“:id”,’1′,PDO::PARAM_INT);

     $st->execute();
   
}
catch(PDOException $e) {
          
         echo “ERROR:” getMessage();
 }
?>
This will upadate the name from James Bond to Johny English in the name field in the DB table.
Thunderbird
Download free

Deleting the record

 

We can delete the exact row or all the records of selected table with this query. See below how to do this:
<?php
try {
     $conn = new PDO(‘mysql:host=localhost;dbname=myDB’,$username,$password);
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $st->prepare(“DELETE from tblname WHERE id=:id”);

     $st=bindValue(“:id”,’1′,PDO::PARAM_INT);

     $st->execute();
   
}
catch(PDOException $e) {
          
         echo “ERROR:” getMessage();
 }
?>
You can use php variable in place of direct value of id in above code like this:
  $st=bindValue(“:id”,’$id’,PDO::PARAM_INT);
But you have to define the $id variable below the actual code.

Posted in: database, php

Leave a Reply

Your email address will not be published. Required fields are marked *