PHP MySQL connection using PDO

Today, In this post we will learn how to connect to MySQL using PDO in PHP and just for the information PDO stands for "PHP Data Objects".
Here we will see the some useful examples to perform CURD operations in MySQL database using PDO and also it provides you some information about MySQL Prepared Statement.

Connections to MySQL database can be established by creating instances of the PDO base class and specify the driver, database name, username, and password.

SYNTAX:

$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

Create "Userdetails" Table

--
-- Table structure for table `userdetails`
--

CREATE TABLE `userdetails` (
  `UID` int(10) NOT NULL,
  `USERNAME` varchar(100) NOT NULL,
  `EMAILID` varchar(100) NOT NULL,
  `COUNTRY` varchar(100) NOT NULL,
  `AGE` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Connection to MySQL Databse Using PDO

Lets see the simple example to establish the database connection to MySQL using PDO.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully"; 
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
----------------------
Connected successfully

PHP Insert Data Into MySQL Database Using PDO

Lets see the simple examples to insert data to MySQL database using PDO.

Method : 1
Here we are executing the query, while passing an array, which contains the data that should be bound to those placeholders.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('INSERT INTO `userdetails`(USERNAME,EMAILID,COUNTRY,AGE) 
VALUES(:USERNAME,:EMAILID,:COUNTRY,:AGE)');
  $stmt->execute(array(
    ':USERNAME' => 'Rahul',
 ':EMAILID' => 'Sumit@gmail.com',
 ':COUNTRY' => 'India',
 ':AGE' => '23',
 
  ));

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "Record Inserted to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
--------------------------
Connected successfully.
Record Inserted to table

Method : 2
Lets see the another example to insert the records in database, we are executing the query, by passing the data to corresponding parameters using bindParam() function.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('INSERT INTO `userdetails`(USERNAME,EMAILID,COUNTRY,AGE) 
VALUES(:USERNAME,:EMAILID,:COUNTRY,:AGE)');
$username = "Mark" ;
$email = "Mark@gmail.com" ;
$country = "USA" ;
$age = "33" ;
$stmt->bindParam(':USERNAME', $username ); 
$stmt->bindParam(':EMAILID', $email); 
$stmt->bindParam(':COUNTRY', $country ); 
$stmt->bindParam(':AGE', $age); 
$stmt->execute();

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "Record Inserted to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
------------------------
Connected successfully.
Record Inserted to table

Method : 3
Another way to use bindParam() function by providing the data type of corresponding parameters used in database.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('INSERT INTO `userdetails`(USERNAME,EMAILID,COUNTRY,AGE) 
VALUES(:USERNAME,:EMAILID,:COUNTRY,:AGE)');
$username = "Mark000" ;
$email = "Mark000@gmail.com" ;
$country = "USA" ;
$age = "53" ;
$stmt->bindParam(':USERNAME', $username,PDO::PARAM_STR ); 
$stmt->bindParam(':EMAILID', $email,PDO::PARAM_STR); 
$stmt->bindParam(':COUNTRY', $country ,PDO::PARAM_STR ); 
$stmt->bindParam(':AGE', $age, PDO::PARAM_INT); 
$stmt->execute();

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "Record Inserted to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
-------------------------
Connected successfully.
Record Inserted to table

PHP Update Data Into MySQL Database Using PDO

Lets see the simple example to update the data of existing records in MySQL database using PDO.

Before update :

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('UPDATE `userdetails` SET `AGE` = :AGE 
WHERE `UID` = :UID ');
$uid = 23 ;
$age = 93 ;
$stmt->bindParam(':UID', $uid,PDO::PARAM_INT );
$stmt->bindParam(':AGE', $age, PDO::PARAM_INT); 
$stmt->execute();

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "Record Updated to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

After Update :

OUTPUT:
------------------
Connected successfully.
Record Updated to table

PHP Delete Data From MySQL Database Using PDO

Lets see the simple example to delete records from MySQL database using PDO connection.

Before Delete :

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('DELETE FROM `userdetails` WHERE `UID` = :UID ');
$uid = 23 ;
$stmt->bindParam(':UID', $uid,PDO::PARAM_INT );
$stmt->execute();

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "Record deleted to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

After Delete :

OUTPUT:
-------------------------
Connected successfully.
Record deleted to table

PHP Select/Fetech Data From MySQL Using PDO

Database Records :

Method : 1 (Using PDO::FETCH_ASSOC)
Retrieving the data from MySQL database using PDO::FETCH_ASSOC Mode.

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('SELECT * FROM `userdetails` WHERE `AGE` > 10 ');
$stmt->execute();

$Count = $stmt->rowCount(); 
echo " Total Records Count : $Count .<br>" ;

if ($Count  > 0){
while($data=$stmt->fetch(PDO::FETCH_ASSOC)) {
echo $data['UID']." | ".$data['USERNAME']." | ".$data['EMAILID'].
" | ".$data['COUNTRY']." | ".$data['AGE']."<BR>" ;

}
}

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
--------------------------
Connected successfully.
Total Records Count : 2 .
20 | Rahul | Sumit@gmail.com | India | 23
22 | Mark | Mark@gmail.com | USA | 33

Method : 2 (Using PDO::FETCH_OBJ)
Retrieving the data from MySQL database using PDO::FETCH_OBJ Mode.
PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('SELECT * FROM `userdetails` WHERE `AGE` > 10 ');
$stmt->execute();

$Count = $stmt->rowCount(); 
echo " Total Records Count : $Count .<br>" ;

if ($Count  > 0){
while($data=$stmt->fetch(PDO::FETCH_OBJ)) {
echo $data->UID." | ".$data->USERNAME." | ".$data->EMAILID.
" | ".$data->COUNTRY." | ".$data->AGE."<BR>" ;

}
}

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

Multiple Execution Of Query

The PDO extension is more powerful when executing the same SQL query multiple times. Here in this example we are inserting two rows at time using same query. Similarly you can use for update and delete operation.
Also you can get the last inserted record id using lastInsertId() function.
Lets see the simple example for more understanding.

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 

$stmt = $dbConnection->prepare('INSERT INTO `userdetails`(USERNAME,EMAILID,COUNTRY,AGE)
 VALUES(:USERNAME,:EMAILID,:COUNTRY,:AGE)');
//first record data...
$username = "Mark" ;
$email = "Mark@gmail.com" ;
$country = "USA" ;
$age = "33" ;
$stmt->bindParam(':USERNAME', $username ); 
$stmt->bindParam(':EMAILID', $email); 
$stmt->bindParam(':COUNTRY', $country ); 
$stmt->bindParam(':AGE', $age); 
$stmt->execute();

echo "<br> Get the last record id : ".$dbConnection->lastInsertId(); 

//seconds record data...
$username = "Wayplus" ;
$email = "wayplus@gmail.com" ;
$country = "US" ;
$age = "38" ;
$stmt->bindParam(':USERNAME', $username ); 
$stmt->bindParam(':EMAILID', $email); 
$stmt->bindParam(':COUNTRY', $country ); 
$stmt->bindParam(':AGE', $age); 
$stmt->execute();

echo "<br> Get the last record id : ".$dbConnection->lastInsertId(); 

$Count = $stmt->rowCount();
if ($Count  > 0)
    echo "<br> Record Inserted to table"  ;

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
------------------------
Connected successfully.

Get the last record id : 24
Get the last record id : 25
Record Inserted to table

After running above query :

PHP MYSQL Query Function Using PDO

Lets see the simple example to retrieve records from MySQL database using PDO and here we are using query() function to get the data.
PDO::quote() :places quotes around the input string (if required) and escapes special characters within the input string

<?php
$dbhost ="localhost"; // set the hostname
$dbname ="skptricksdemo" ; // set the database name
$dbuser ="root" ; // set the mysql username
$dbpass ="";  // set the mysql password


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully.<br>"; 
$a = 22;
$stmt = $dbConnection->query('SELECT * FROM `userdetails` 
WHERE `AGE` > '.$dbConnection->quote($a) );

foreach($stmt as $row) {
        //print_r($row); 
  echo $row["UID"]." | ".$row[2]."<br>";
    }

}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

?>

OUTPUT:
--------------------------
Connected successfully.
20 | Sumit@gmail.com
22 | Mark@gmail.com
24 | Mark@gmail.com
25 | wayplus@gmail.com

Download Link : https://github.com/skptricks/php-Tutorials/tree/master/PHP%20MySQL%20Connection%20Using%20PDO

Thursday, October 19, 2017

PHP MySQL connection using PDO

Connection to MySQL Databse Using PDO

PHP Insert Data Into MySQL Database Using PDO

PHP Update Data Into MySQL Database Using PDO

PHP Delete Data From MySQL Database Using PDO

PHP Select/Fetech Data From MySQL Using PDO

Multiple Execution Of Query

PHP MYSQL Query Function Using PDO

1 comment:

Follow Us

Get Latest Updates

Tags

Popular Posts

Skptricks