X
    Categories: MysqlPHP

PHP PDO Database Connection

PHP PDO Database Connection

Hi, In this tutorial we are going to see how to connect the database in PDO using php. Now a days Mysql are getting depricated, so the developers need to learn  Mysqli or PDO.
Here we can see few stuffs in PDO.

How to connect database:

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

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

 

Let us assume we have table called tbl_users and has the following fields,

id,
name,
email_id,
contact_no

 

Select Operations :

The following query will select all the records from the table.

$query = 'Select * from tbl_users';
$stmt = $DB_con->prepare($query);
$stmt->execute();

if($stmt->rowCount()>0)
{
   while($row=$stmt->fetch(PDO::FETCH_ASSOC))
   {
   	$id = $row['id'];
   	$name = $row['name'];
   	$email = $row['email_id'];
   	$contact_no = $row['contact_no'];
   }
}

 Or When trying in function

function getData($db) {
	$query = 'Select * from tbl_users';
	$stmt = $DB_con->prepare($query);
	$stmt->execute();
	return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

 


Insert Operation :

Here we are using binding statement to insert the data into database, By using this we can avoid the mysql injections.
The way of binding data is :foo without any quotes. instead of :foo you can use ? also,

$fname = 'Marimuthu';
$email = 'marimuthu@17educations.com';
$contact = 'support@17educations.com';

$stmt = $DB_con->prepare("INSERT INTO tbl_users(name,email_id,contact_no) VALUES(:name, :email, :contact)");
$stmt->bindparam(":name",$fname);
$stmt->bindparam(":email",$email);
$stmt->bindparam(":contact",$contact);
$stmt->execute();

or 

$statement = $link->prepare("INSERT INTO tbl_users(name,email_id,contact_no)
    VALUES(?, ?, ?)");
$statement->execute(array($fname, $email, $contact));

 Select Single Row

The following query will select single row based in id.

 $stmt = $DB_con->prepare("SELECT * FROM tbl_users WHERE id=:id");
  $stmt->execute(array(":id"=>$id));
  $editRow=$stmt->fetch(PDO::FETCH_ASSOC);
  $name =  $editRow['name'];
  $email_id =  $editRow['email_id'];
  $contact_no =  $editRow['contact_no'];

Updating Row

The below query will update the row based in ID.

$stmt = $DB_con->prepare("UPDATE tbl_users SET name=:name, 
                email_id=:email, 
                contact_no=:contact
             WHERE id=:id ");
   $stmt->bindparam(":name",$name);
   $stmt->bindparam(":email",$email);
   $stmt->bindparam(":contact",$contact);
   $stmt->bindparam(":id",$id);
   $stmt->execute();

Deleting Row

The below query will delete the row based in ID.

  $stmt = $DB_con->prepare("DELETE FROM tbl_users WHERE id=:id");
  $stmt->bindparam(":id",$id);
  $stmt->execute();

  $stmt = $this->db->prepare($query);
  $stmt->execute();

Thanks for reading this article, If you like this please don’t forget to share and comment.

Marimuthu: