MySQLi Insert

To insert data into a MySQL table, you would have to utilize the SQL INSERT INTO command. The INSERT INTO statement is used to insert new rows in a database table.

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

Syntax

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

The field's names are separated by a comma and their values are also separated by a 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 using object-oriented way -


<?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', 'john@example.com', 5645465, '12 xyz')";
if($conn->query($insert)){
 echo 'Data inserted successfully';
}
else{
 echo 'Error '.$conn->error;  
}
?>




Procedural PHP MySQLi Insert Data

The following code insert data in 'employee' table using procedural way -

<?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', 'mary@example.com', 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 the prepared statements. Prepared statments prevent SQL Injection.



PHP MySQLi Prepared Statement

We can write the above MySQL object-oriented INSERT query using the 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 represent the datatype, the 's' represents the string value and 'i' represents the integer value.





Object Oriented PHP MySQLi Insert Multiple Data

In the above examples, we have inserted only a single record, but what if we need to insert multiple data? The MySQLi provides a 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', 'mary@abc.com', 111221212, '12 abc');";
$query .= "INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `address`)"
        ." VALUES ('', 'John', 'john@abc.com', 232323232, 'johnmiler')";
if($conn->multi_query($query)){
 echo 'Multiple Data inserted successfully';
}
else{
 echo 'Error '.$conn->error;
}
?>   
    






Read more articles


General Knowledge



Learn Popular Language