PHP insert data into MySQL Database

0
23
views
Featured image of PHP insert data into MySQL Database

Learn how to insert data into MySQL database Using PHP.

PHP Insert data in various ways – In this tutorial we’ll inserting data into the MySQL Database in different ways using PHP. And we will also create connections with the databases in various ways. Let’s see all those ways step by step.

MySQL Database and table creation

Before we inserting data into the MySQL database we’ll see our MySQL Database and table creation.

Database Name – As you wish.
Table Name – users
Structure of the users table.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

All ways of PHP insert data into MySQL Database

#1 PHP MySQLi insert with procedural method.

<?php
// Database connection
$database_connection= mysqli_connect("localhost","root","","database_name");

// Check database connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

// Insert query (Insert data into users table)
$insert_query = mysqli_query($database_connection, "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')");

// Check if data inserted
if($insert_query){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

#2 PHP OOP(Object Oriented Programming) method.

<?php
// Database connection
$database_connection = new mysqli("localhost", "root", "", "database_name");

// Check database connection
if ($database_connection === false){
  echo "Failed to connect to MySQL: " . $database_connection->connect_error;
}

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

// Insert query (Insert data into users table)
$insert_query = $database_connection->query("INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')");

// Check if data inserted
if($insert_query === true){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

#3 PHP PDO(PHP Data Objects) method.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "database_name";

// CREATE DATABASE CONNECTION USING PDO METHOD
try {
    $database_connection = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
    // Set the PDO error mode to exception
    $database_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Failed to connect to MySQL: " . $e->getMessage();
    }


// INSERT DATA INTO THE DATABASE
try {
    // PUT DATA INTO THE VARIABLES
    $first_name = 'John';
    $last_name = 'Doe';
    $email = '[email protected]';
    
    $sql = "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
    $database_connection->exec($sql);
    echo "Data inserted successfully";
    
    }
catch(PDOException $e)
    {
    echo "data not inserted";
    }
?>

PHP Insert data into MySQL Database with Prepared Statements.

A prepared statement is a feature that is used to execute the same (or similar) SQL statements repeatedly with high efficiency.

#1 Prepared Statements in Procedural style.

<?php
// Database connection
$database_connection= mysqli_connect("localhost","root","","database_name");

// Check database connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$stmt = mysqli_prepare($database_connection, "INSERT INTO `users` (first_name,last_name,email) VALUES (?,?,?)");

// Data binding
// "sss" is data type (Read note)
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

// execute and if execute prepared statement
if(mysqli_stmt_execute($stmt)){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

Note:
Here we added three data $first_name, $last_name, $email. The type of all the three data is string for this reason we added "sss".

if $first_name data type is an integer and $last_name and $email data type is string then our syntax is "iss"

Type specification chars

Character Description
i If corresponding variable type is integer
d If corresponding variable type is double
s Corresponding variable type is string
b Corresponding variable is a blob and will be sent in packets

#2 Prepared Statements in OOP(Object Oriented Programming) style.

<?php
// Database connection
$database_connection = new mysqli("localhost", "root", "", "database_name");

// Check database connection
if ($database_connection === false){
  echo "Failed to connect to MySQL: " . $database_connection->connect_error;
}

// Insert query (Insert data into users table)
$stmt = $database_connection->prepare("INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)");

$stmt->bind_param("sss", $first_name, $last_name, $email);

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

if($stmt->execute()){
    echo "Data inserted successfully."; 
}else{
    echo "Something error occurred.";
}
?>

#3 Prepared Statements in PDO(PHP Data Objects) style.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "database_name";

// CREATE DATABASE CONNECTION USING PDO METHOD
try {
    $database_connection = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
    // Set the PDO error mode to exception
    $database_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection Failed" . $e->getMessage();
    }


// INSERT DATA INTO THE DATABASE
try {
    
    
    $sql = "INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)";
    $stmt = $database_connection->prepare($sql);
    
    $stmt->bindParam(1, $first_name, PDO::PARAM_STR);
    $stmt->bindParam(2, $last_name, PDO::PARAM_STR);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);
    
    // PUT DATA INTO THE VARIABLES
    $first_name = 'John';
    $last_name = 'Doe';
    $email = '[email protected]';
    $stmt->execute();
    
    echo "Data inserted successfully";
    
    }
catch(PDOException $e)
    {
    echo "data not inserted";
    }
?>

Tip:
Use colon(:) before text to replace the question marks.

VALUES (:first_name, :last_name, :email)
$stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR);
$stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);

PHP insert multiple rows at once

Inserting multiple data into the users table at once in various way.

#1 Common method

$sql = "INSERT INTO `users` (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'), 
('Tony', 'Stark', '[email protected]'), 
('Bruce', 'Banner', '[email protected]')";

#2 Using Prepared Statements

Procedural style

// Data binding
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

// insert first row
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
mysqli_stmt_execute($stmt);

// insert second row
$first_name = 'Tony';
$last_name = 'Stark';
$email = '[email protected]';
mysqli_stmt_execute($stmt);

OOP(Object Oriented Programming) style

// Data binding
$stmt->bind_param("sss", $first_name, $last_name, $email);

// insert first row
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
$stmt->execute();

// insert second row
$first_name = 'Tony';
$last_name = 'Stark';
$email = '[email protected]';
$stmt->execute();

PDO(PHP Data Objects) style

//Data binding
$stmt->bindParam(1, $first_name, PDO::PARAM_STR);
$stmt->bindParam(2, $last_name, PDO::PARAM_STR);
$stmt->bindParam(3, $email, PDO::PARAM_STR);

// insert first row
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
$stmt->execute();

// insert second row
$first_name = 'Tony';
$last_name = 'Stark';
$email = '[email protected]';
$stmt->execute();

LEAVE A REPLY

Please enter your comment!
Please enter your name here