Create PHP PDO To Insert, Select, Update, Delete In MySQL Database Table

Share:

Create PHP PDO To Insert, Select, Update, Delete In MySQL Database Table. PDO (PHP Data Objects) is a PHP extension to access the MySQL database. function the same as, mysql_query or mysql_fetch_array. Currently functions like mysql_query and mysql_fetch_array already been deprecated and will be removed in the next version of the php program.
Create PHP PDO To Insert, Select, Update, Delete In MySQL Database TableTherefore, we need to immediately switch to using PHP PDO or MySQLi to save, edit or delete data in a MySQL database table. PHP PDO can be used to access several different databases, while MySQLi only for mysql database. If at this time we are using a MySQL database and later on changed using the PostgreSQL database, then we do not need to change the code that has been created.

Some of the benefits that can be gained from using PHP PDO. First, PHP PDO help prevent SQL injection, PHP PDO will automatically do quote user input. Second, PHP PDO supports database transactions. Third, PHP PDO process the data faster when compared to using the mysql_query function.

Create PHP PDO To Insert, Select, Update, Delete In MySQL Database Table

In the post, Create PHP To Insert, Select, Update, Delete In MySQL Database Table. We’ve learned how to make access to the MySQL database table using a mysql_ query. This php script is about to be removed by the php developers because it is time for us to learn to use PHP PDO as an alternative to access MySQL database using php programming. If you’ve mastered mysql_query, to master PHP PDO will not be difficult. Immediately, following the tutorial …

Create MySQL Database Table
Previously, you created a table in the localhost or web hosting server. In this example, we create a table with the table name is people. And here are the contents of the table :

CREATE TABLE 'people' (
  'id' int(11) NOT NULL auto_increment,
  'name' varchar(100) NOT NULL,
  'email' varchar(100) NOT NULL,
  'country' varchar(25) NOT NULL,
  PRIMARY KEY  ('id')
) ;

Create PHP PDO to Connect in MySQL Database Server
To connect in MySQL database table, the mysql_ query be written using the following script :

$db = mysql_connect($host, $user, $password);
mysql_select_db($database_name);

But in PHP PDO, you only need to write this php script

$pdo = new PDO("mysql:host=localhost;dbname=databasename", $user, $password, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

The explanation you can see from the image below:
Create PHP PDO To Insert, Select, Update, Delete In MySQL Database TableSo how to connect database using PHP PDO are as follows :

<?php
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "databasename";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
?>

Create PHP PDO INSERT Statement To Save Data in MySQL Database Table
To save the data into a MySQL database table, we can use PHP PDO insert statement. Here’s the script :

<?php
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "databasename";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    try{
    $query = $pdo->prepare("insert into people (name,email,country)
    values (:name,:email,:country)");
    $data = array(
    ':name' => 'Robby',
    ':email' => 'robby@world.edu',
    ':country' => 'United State'
    );
    $query->execute($data);
    echo "Data successfully inserted into the database table ...";
    }catch(PDOException $e){
    echo "Error! failed to insert into the database table :".$e->getMessage();
}
?>

Create PHP PDO UPDATE Statement For Editing Data in MySQL Database Table
To edit the data that has been stored in a MySQL database table, we can use the PHP PDO update statement. Here’s the script :

<?php
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "test";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    try{
    $query = $pdo->prepare("update people set country = :country where email = :email");
    $data = array(
    ':country' => 'Canada',
    ':email' => 'robby@world.edu'
    );
    $query->execute($data);
    echo "Data successfully updated into the database table ...";
    }catch(PDOException $e){
    echo "Error! failed to update into the database table ... :".$e->getMessage();
    }
?>

Create PHP PDO SELECT Statement For Displaying Data in MySQL Database Table
To display data from a MySQL database table, we can use PHP PDO select statement. Here’s the PHP PDO script :

<?php
$host = "localhost";
$user = "root";
$password = "";
$database_name = "databasename";
$pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
$query = $pdo->prepare("select * from people");
$query->execute();
while($people = $query->fetch()){
echo $people['name'];
echo $people['email'];
echo $people['country'];
}
?>

Create PHP PDO DELETE Statement To Delete Data in MySQL Database Table
To delete data from a MySQL database table, we can use PHP PDO delete statement. Here’s the PHP PDO script :

<?php
    $host = "localhost";
    $user = "root";
    $password = "";
    $database_name = "databasename";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    try{
    $query = $pdo->prepare("delete from people where email = :email");
    $query->execute(array(
    ':email' => 'robby@world.edu'
    ));
    echo "Data successfully deleted in the database table ... ";
    }catch(PDOException $e){
    echo "Failed to delete the MySQL database table ... :".$e->getMessage();
    }
?>

This tutorial is complete, because we have discussed how to connect to the MySQL database to perform data deletion. Now, you just give it a try but make sure that the website hosting and localhost server you use has support PHP PDO. Good luck …
Thanks for reading Create PHP PDO To Insert, Select, Update, Delete In MySQL Database Table

[Tutorial.World.Edu]

9 thoughts on “Create PHP PDO To Insert, Select, Update, Delete In MySQL Database Table

Leave a Reply

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