How we can update records using PDO MySQL Connection

update records using PDO Connection


update records using PDO :  Hi readers, today in this tutorial we will explain you how we can update records of MySQL database using PDO Connection. Here we will provide you the Step-by-Step guide to update records using PDO MySQL Connection in PHP Script. Further, Screen Shot view makes your concept more clear.

Lets begin the Step-By-Step guide to update records using PDO MySQL Connection :

Step 1: Create a connection using PDO class:


In this step first we create a connection using PDO base class by creating an instance of PDO and later on checking condition if any error then it is handled by catch block otherwise established connection Successfully.

You can view the details on how we can create a connection using PDO MySQL by the link provided below :

Create a PDO Connection

Step 2: Write a query to update records :


Here in this step we simply write the SQL query to update the desired records from MySQL table using PDO connection just as we do in the SQL command prompt.

UPDATE statement is generally used to update records. Below is the brief description of UPDATE statement to make your concept clear :

Update Statement :

UPDATE is a DML statement which is mainly used to manipulate data.

The update statement is used to update data of an existing field of a table.

UPDATE table_name
SET field1=value1,field2=value2,...
where field=value;

Below is the code snippet of Update query : 

//query to update records
$sql="update customer SET custName='Akansha gupta' WHERE id=1";

 

Step 3: Prepare statement to execute the Query and finally display message


Here in this step first we prepare a statement using prepare() method of PDO class.

prepare(string $statement )

prepare() method of PDO is generally used to prepares a statement to execute and returns a statement object.

//prepare statement
$st=$conn->prepare($sql);

After preparing statement we simply execute the above query by using the query() method of PDO. 

query() method  of PHP Data Object (PDO) is used to execute an SQL Statement that returns a result set as a PDOStatement Object or fails on failure.

Below is the code snippet to execute the above query and fetch all the records of result set :

//execute the query
$st->execute();

Then finally display the message if one or more rows has been updated or affected.

//echo message to show that update is successfull
echo $st->rowCount()." records updated successfully"; 

Here rowCount() method is used which returns one or more number of rows affected by the last DML (DELETE, INSERT or UPDATE) statement executed by the corresponding PDOStatement object.

Step 3: Close the Connection:


It’s always a good practice to close the connection after completing the task. Simply assign “null” value to the initiated object created by PDO base class.

//close the connection
$conn=null;

Full Source code to update records using PDO MySQL Connection :


<?php
$host="localhost";
$username="root";
$password="";
$dbname="pdo";
try
{
//create a connection
$conn=new PDO("mysql:host=$host;dbname=$dbname",$username,$password);

//set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//query to update records
$sql="update customer SET custName='Shreya Jaiswal' WHERE id=1";

//prepare statement
$st=$conn->prepare($sql);

//execute the query
$st->execute();

//echo message to show that update is successfull
echo $st->rowCount()." records updated successfully";
}
catch(PDOException $e)
{
 echo $sql."<br>". $e->getMessage();
}

//close the connection
$conn=null;

?>

Screen Shot View:


screenshot view of MySQL database “pdo” in which we want to update  :

Result of update records using PDO MySQL Connection:


update records using PDO
update records using PDO

                                                                    update records using PDO MySQL Connection in PHP


I hope you would like this tutorial on how we can update records using PDO MySQL Connection in PHP !

Leave a Comment