PHP | Inserting into MySQL database

Prerequisites : MySQL introduction, Creating MySQL database
INSERT INTO statement is used to insert new rows in a database table. Let’s see the syntax how to insert into table, considering database already exists.

SYNTAX :

INSERT INTO TABLE_NAME (column1, column2, column3, ... columnN) 
VALUES (value1, value2, value3, ...valueN);

Here, column1, column2, column3, …columnN are the names of the columns in the table into which you want to insert the data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

So to create a SQL query using the INSERT INTO statement with appropriate values, here’s an example, which will insert a new row to the newDB table by specifying values for the first_name, last_name and email fields.

1. Creating table using MySQLi Object-oriented Procedure :

   <?php
    $mysqli = new mysqli("localhost", "root", "", "newdb");
   
   if ($mysqli == = false) {
       die("ERROR: Could not connect. ".$mysqli->connect_error);
   }
   
   $sql = "INSERT INTO mytable (first_name, last_name, age)
                 VALUES('ram', 'singh', '25') ";
       if ($mysqli->query($sql) == = true)
   {
       echo "Records inserted successfully.";
   }
   else
   {
       echo "ERROR: Could not able to execute $sql. "
              .$mysqli->error;
   }
   
   // Close connection
   $mysqli->close();
   ? >

   Output :
   

2. Creating table using MySQLi Procedural Procedure :

   <?php
   $link = mysqli_connect("localhost", "root", "", "newdb");
   
   if ($link == = false) {
       die("ERROR: Could not connect. ".mysqli_connect_error());
   }
   
   $sql = "INSERT INTO mytable (first_name, last_name, age)
             VALUES('ram', 'singh', '25') ";
       if (mysqli_query($link, $sql))
   {
       echo "Records inserted successfully.";
   }
   else
   {
       echo "ERROR: Could not able to execute $sql. "
           .mysqli_error($link);
   }
   
   mysqli_close($link);
   ? >

   Output :
   

3. Creating table using MySQLi PDO Procedure :

   <?php
   try {
      $pdo = new PDO("mysql:host=localhost;dbname=newdb",
                        "root", "");
      $pdo->setAttribute(PDO::ATTR_ERRMODE,
                           PDO::ERRMODE_EXCEPTION);
   }
   catch (PDOException $e) {
       die("ERROR: Could not connect. ".$e->getMessage());
   }
   
   try {
       $sql = "INSERT INTO mytable (first_name, last_name, age)
              VALUES('ram', 'singh', '25') ";
              $pdo->exec($sql);
       echo "Records inserted successfully.";
   }
   catch (PDOException $e) {
       die("ERROR: Could not able to execute $sql. "
               .$e->getMessage());
   }
   
   // Close connection
   unset($pdo);
   ? >

   Output :
   

The values of the table can be viewed in MySql database as –

Inserting Multiple Rows into a Table

One can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.

1. Creating table using MySQLi Object-oriented Procedure :

   <?php
   $mysqli = new mysqli("localhost", "root", "", "newdb");
   
   if ($mysqli == = false) {
       die("ERROR: Could not connect. ".$mysqli->connect_error);
   }
   
   $sql = "INSERT INTO mytable (first_name, last_name, age)
              VALUES('raj', 'sharma', '15'),
       ('kapil', 'verma', '42'),
       ('monty', 'singh', '29'),
       ('arjun', 'patel', '32') ";
       if ($mysqli->query($sql) == = true)
   {
       echo "Records inserted successfully.";
   }
   else
   {
       echo "ERROR: Could not able to execute $sql. "
           .$mysqli->error;
   }
   
   $mysqli->close();
   ? >

   Output :
   

2. Creating table using MySQLi Procedural Procedure :

   <?php
   $link = mysqli_connect("localhost", "root", "", "newdb");
   
   if ($link == = false) {
       die("ERROR: Could not connect. ".mysqli_connect_error());
   }
   
   $sql = "INSERT INTO mytable (first_name, last_name, age)
             VALUES('raj', 'sharma', '15'),
       ('kapil', 'verma', '42'),
       ('monty', 'singh', '29'),
       ('arjun', 'patel', '32') ";
       if (mysqli_query($link, $sql))
   {
       echo "Records added successfully.";
   }
   else
   {
       echo "ERROR: Could not able to execute $sql. "
           .mysqli_error($link);
   }
   
   // Close connection
   mysqli_close($link);
   ? >

   Output :
   

3. Creating table using MySQLi PDO Procedure :

   <?php
   try {
       $pdo = new PDO("mysql:host=localhost;dbname="newdb",
                         "root", "");
       $pdo->setAttribute(PDO::ATTR_ERRMODE,
                             PDO::ERRMODE_EXCEPTION);
   }
   catch (PDOException $e) {
       die("ERROR: Could not connect. ".$e->getMessage());
   }
   
   try {
       $sql = "INSERT INTO mytable (first_name, last_name, age)
            VALUES('raj', 'sharma', '15'),
                  ('kapil', 'verma', '42'),
                  ('monty', 'singh', '29'),
                  ('arjun', 'patel', '32') ";
        $pdo->exec($sql);
       echo "Records inserted successfully.";
   }
   catch (PDOException $e) {
       die("ERROR: Could not able to execute $sql. "
                .$e->getMessage());
   }
   unset($pdo);
   ? >

Output :

The values of the table can be viewed in MySql database as –