SYNTAX:

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

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

<?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(); } ?>

PHP Insert Data Into MySQL Database Using PDO

Method : 1

<?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(); } ?>

Method : 2

<?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(); } ?>

Method : 3

<?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(); } ?>

PHP Update Data Into MySQL Database 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.<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(); } ?>

PHP Delete Data From MySQL Database 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.<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(); } ?>

PHP Select/Fetech Data From MySQL Using PDO

Method : 1 (Using PDO::FETCH_ASSOC)

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(); } ?>





Method : 2 (Using PDO::FETCH_OBJ)

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

<?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(); } ?>





PHP MYSQL Query Function 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.<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(); } ?>

Download Link

Today, In this post we will learnand just for the information PDO stands for "".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, andLets see the simple example to establish the database connection to MySQL using PDO.----------------------Connected successfullyLets see the simple examples to insert data to MySQL database using PDO.Here we are executing the query, while passing an array, which contains the data that should be bound to those placeholders.--------------------------Connected successfully.Record Inserted to tableLets see the another example to insert the records in database, we are executing the query, by passing the data to corresponding parameters usingfunction.------------------------Connected successfully.Record Inserted to tableAnother way to usefunction by providing the data type of corresponding parameters used in database.-------------------------Connected successfully.Record Inserted to tableLets see the simple example to update the data of existing records in MySQL database using PDO.------------------Connected successfully.Record Updated to tableLets see the simple example to delete records from MySQL database using PDO connection.-------------------------Connected successfully.Record deleted to tableRetrieving the data from MySQL database usingMode.--------------------------Connected successfully.Total Records Count : 2 .20 | Rahul | Sumit@gmail.com | India | 2322 | Mark | Mark@gmail.com | USA | 33Retrieving the data from MySQL database usingMode.returns an anonymous object with property names that correspond to the column names returned in your result set.-------------------------------Connected successfully.Total Records Count : 2 .20 | Rahul | Sumit@gmail.com | India | 2322 | Mark | Mark@gmail.com | USA | 33The 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 usingfunction.Lets see the simple example for more understanding.------------------------Connected successfully.Get the last record id : 24Get the last record id : 25Record Inserted to tableLets see the simple example to retrieve records from MySQL database using PDO and here we are usingto get the data.places quotes around the input string (if required) and escapes special characters within the input stringOUTPUT:--------------------------Connected successfully.20 | Sumit@gmail.com22 | Mark@gmail.com24 | Mark@gmail.com25 | wayplus@gmail.com