PHP PDO insert data into MySQL Database ~ W3Laravel

PHP PDO insert data into MySQL Database

In this tutorial, you will learn how you can insert data into MySQL database using PHP PDO class. PDO stands for PHP Data Objects. PDO is not limited to only MySQL database, but also can be used with another database systems. PDO is object oriented, it means we need to create an object to access the methods of PDO. So let's start with the tutorial PHP PDO insert data into MySQL Database and learn how you can insert data into MySQL database using PHP's PDO.

PHP PDO insert data into MySQL Database: Creating Database

Here, I'm going to work with a very simple database structure. So, what we going to do first is create a database and a table. Let's take an example of users table which consists of name, email, phone and password column.

Create a users database with these columns-

PHP PDO insert data into MySQL Database: Designing a form

After creating a database, we can work a little bit on front end. The design is completely depends upon your web app template. Here I've created a simple form using bootstrap. You can work with this for testing purpose or you can completely design your own design.

Create a file named index.php into root directory of web app and paste the following code.

<!DOCTYPE html>
<html>
<head>
 <title>PHP PDO insert data into MySQL Database</title>
 <link rel="stylesheet" type="text/css" href="css/bootstrap.css">
</head>
<body>
 <div class="container">
  <div class="row">
   <div class="col-md-12">
    <div class="page-header">
     <h1>PHP PDO insert data into MySQL Database</h1>
    </div>
   </div>
  </div>

  <form class="form-horizontal" method="POST" action="insert.php">
   <div class="form-group">
    <label for="name" class="col-sm-2">Name</label>
    <div class="col-sm-10">
     <input type="text" name="name" class="form-control">
    </div>
   </div>

   <div class="form-group">
    <label for="email" class="col-sm-2">Email</label>
    <div class="col-sm-10">
     <input type="text" name="email" class="form-control">
    </div>
   </div>

   <div class="form-group">
    <label for="phone" class="col-sm-2">Phone</label>
    <div class="col-sm-10">
     <input type="text" name="phone" class="form-control">
    </div>
   </div>

   <div class="form-group">
    <label for="password" class="col-sm-2">Password</label>
    <div class="col-sm-10">
     <input type="text" name="password" class="form-control">
    </div>
   </div>

   <div class="col-sm-offset-2">
    <button type="submit" class="btn btn-primary">Save</button>
   </div>
  </form>
 </div>
</body>
</html>

PHP PDO insert data into MySQL Database: Opening a Connection with MySQL

The first file you created is just a collection of html tags. Now you can close that file. Create a new file called insert.php on your root directory and start writing the php codes. The very first thing we need to do is open a connection with MySQL database. Don't worry it's very simple and will take very few steps-

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pdo";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
}
catch(PDOException $e){
    die('Whoops, some error occured. Please try again later');
}

All the configurations are very simple-

$servername- The host name, if you are working locally, it should be localhost.

$username- The username of your MySQL database. In my case, it is root, you could have your own. If you are working locally, then by default it is root.

$password- Password of your database. If you are working locally, then by default it doen not have any password.

$dbname- The name of the database, you have created in above steps. Write your own database name.

try-catch- We have written the code for opening a connection with MySQL database in try-catch block, because any exception can be occured like- no database found or anything.

die()- The die method is used to stop the execution of PHP program after printing the string within its parameter.

PHP PDO insert data into MySQL Database: Inserting Data into MySQL Database

We already made a connection with MySQL database. Now the only thing we have to do is to insert data into database. Here is how we can insert data into database. I'll explain it in more detail below. keep reading the article PHP PDO data insert into MySQL database.

You don't have a create any other file, just append this code in update.php file.

$data = array('name' => $_POST['name'],
    'email' => $_POST['email'],
    'phone' => $_POST['phone'],
    'password' => $_POST['password']
  );
$sql = "insert into users(name, email, phone, password) values(:name, :email, :phone, :password)";

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

$statement->execute($data);

$data- I'm creating an array of data of what we are getting from the html form. The $_POST is a global variable, which is used to accept data from html form when the method is set to post.

$sql- This is the query for inserting data to a database. users is the table name, the columns of the table is (name, email, phone, password) and finally the placeholder for the columns (:name, :email, :phone, :password).

$statement- Whenever we have to execute a query using PHP PDO, first we need to prepare the statement using the prepare() method.
And finally we have to call the function execute() as well as bind the input data. We can bind the input data by passing an array of input data in execute() method as an argument.

Conclusion

That's how we can insert data into MySQL database using PHP PDO class. We can also use mysqli class, but this is limited to MySQL database only. So, PDO is helpful when you need to change the database system.

Share this article PHP PDO insert data into MySQL Database to help other newbies.

Follow us to get more latest updates.

W3Laravel

The complete Lavarel framework development tutorial

Sunday 26 February 2017