MySQLi Insert

The INSERT keyword is used to insert values in a created table or existing table.

Syntax

INSERT INTO Tablename (field1, field2, filed3) VALUES ('value1', 'value2', 'value3');  

Fields name are separated by comma and their values are also separated by comma. These are the following ways to insert data in a table.

Object Oriented PHP MySQLi Insert Table

The following code insert data in Employee table

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$insert = "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`) VALUES
          ('', 'John', This email address is being protected from spambots. You need JavaScript enabled to view it.', 5645465, '12 xyz')";
if($conn->query($insert)){
 echo 'Data inserted successfully';
}
else{
 echo 'Error '.$conn->error;  
}
?>

Procedural PHP MySQLi Insert Data

<?php
$conn = mysqli_connect('hostname', 'username', 'password', 'databasename');
//Check for connection error
if(mysqli_connect_error()){
  die("Error in DB connection: ".mysqli_connect_errno()." - ".mysqli_connect_error());
}
$insert = "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`) VALUES
          ('', 'Mary', This email address is being protected from spambots. You need JavaScript enabled to view it.', 5645465, '13 abc')";
if(mysqli_query($conn, $insert)){
 echo 'Data inserted successfully';
}
else{
 echo 'Error: '.mysqli_error($conn);
}
?>   
    

In the above examples, we have inserted employee credentials with employee data. We can make the insertion process more secure by using Prepared Statements. Prepared statments prevent SQL Injection.

PHP MySQLi Prepared Statement

We can write the above MySQL Object Oriented INSERT code using Prepared Statements as -

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$empname = 'Sohn';
$email = 'sohn@example';
$phone = '1212121212';
$address ='44 xyz';
$query = "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`) VALUES ('', ?, ?, ?, ?)";
$insert = $conn->prepare($query);
//bind parameters
$insert->bind_param('i', $empname, $email, $phone, $address);
if($insert->execute()){
 echo 'Data inserted successfully';
}
else{
 echo 'Error: '.$conn->error;
}
?>
    

In the above example, the first parameter (ississ) in bind_param represant datatype, 's' represants string value, 'i' represants integer value.

Object Oriented PHP MySQLi Insert Multiple Data

MySQLi has multi_query function to insert multiple records in a single statement.

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$query = "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`)"
        ." VALUES ('', 'Mary', This email address is being protected from spambots. You need JavaScript enabled to view it.', 111221212, '12 abc');";
$query .= "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`)"
        ." VALUES ('', 'John', This email address is being protected from spambots. You need JavaScript enabled to view it.', 232323232, 'johnmiler')";
if($conn->multi_query($query)){
 echo 'Multiple Data inserted successfully';
}
else{
 echo 'Error '.$conn->error;
}
?>