PHP MySQL Insert

Previous Page Next Page

Inserting Data into a MySQL Database Table

Now that you've learned how to create a database and tables in MySQL, let's move on to inserting records into a table using SQL queries.

The INSERT INTO statement is essential for adding new rows into a database table.

To insert data, we construct an SQL query using the INSERT INTO statement. After crafting the query with the appropriate values, we execute it using the PHP mysqli_query() function. Below is an example demonstrating the insertion of a new record into the persons table, specifying values for the first_name, last_name, and email fields.

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
if(mysqli_query($link, $sql)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
if($mysqli->query($sql) === true){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt insert query execution
try{
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";    
$pdo->exec($sql);
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

If you recall from the previous chapter, the id field was configured with the AUTO_INCREMENT attribute. This setting instructs MySQL to automatically generate a value for this field if it's not explicitly provided, incrementing the previous value by 1.

Inserting Multiple Rows into a Table

You can also insert multiple rows into a table with a single insert query. To achieve this, include multiple sets of column values in the INSERT INTO statement. Each set should be enclosed in parentheses and separated by commas.

Let's add several more rows to the persons table, as shown below:

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";
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);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";
if($mysqli->query($sql) === true){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt insert query execution
try{
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES
('John', 'Rambo', 'johnrambo@mail.com'),
('Clark', 'Kent', 'clarkkent@mail.com'),
('John', 'Carter', 'johncarter@mail.com'),
('Harry', 'Potter', 'harrypotter@mail.com')";   
$pdo->exec($sql);
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

Now, navigate to phpMyAdmin (http://localhost/phpmyadmin/) and review the data in the persons table within the demo database. You'll notice that the id column values are automatically assigned by incrementing the previous id value by 1.

Note: You can include line breaks within a SQL statement, as long as they don't separate keywords, values, or expressions.

Insert Data into a Database from an HTML Form

In the previous section, we learned how to add data to a database using PHP. Now, let's explore how to insert data into a database using information from an HTML form. Below is an HTML form designed to add new records to the persons table.

Step 1: Creating the HTML Form

Here's a straightforward HTML form featuring three text <input> fields and a submit button.

Example

Download

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Record Form</title>
</head>
<body>
<form action="insert.php" method="post">
<p>
<label for="firstName">First Name:</label>
<input type="text" name="first_name" id="firstName">
</p>
<p>
<label for="lastName">Last Name:</label>
<input type="text" name="last_name" id="lastName">
</p>
<p>
<label for="emailAddress">Email Address:</label>
<input type="text" name="email" id="emailAddress">
</p>
<input type="submit" value="Submit">
</form>
</body>
</html>

Step 2: Retrieving and Inserting the Form Data

When a user clicks the submit button in the example above, the form data is sent to the 'insert.php' file. Inside 'insert.php', the script connects to the MySQL database server, retrieves the form fields using PHP $_REQUEST variables, and executes an insert query to add the records. Below is the complete code for our 'insert.php' file:

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Escape user inputs for security
$first_name = mysqli_real_escape_string($link, $_REQUEST['first_name']);
$last_name = mysqli_real_escape_string($link, $_REQUEST['last_name']);
$email = mysqli_real_escape_string($link, $_REQUEST['email']);

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
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);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Escape user inputs for security
$first_name = $mysqli->real_escape_string($_REQUEST['first_name']);
$last_name = $mysqli->real_escape_string($_REQUEST['last_name']);
$email = $mysqli->real_escape_string($_REQUEST['email']);

// Attempt insert query execution
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
if($mysqli->query($sql) === true){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt insert query execution
try{
// Create prepared statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);

// Bind parameters to statement
$stmt->bindParam(':first_name', $_REQUEST['first_name']);
$stmt->bindParam(':last_name', $_REQUEST['last_name']);
$stmt->bindParam(':email', $_REQUEST['email']);

// Execute the prepared statement
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

In the following chapter, we'll expand on the insert query example by implementing a prepared statement for enhanced security and performance.

Note: The mysqli_real_escape_string() function escapes special characters in a string to create a valid SQL string, offering protection against SQL injection.

This example demonstrates the fundamental process of inserting form data into a MySQL database table. You can enhance this example by incorporating validations to ensure user inputs are sanitized before insertion into the database. Explore the PHP form validation tutorial to learn about sanitizing and validating user inputs effectively with PHP.

Previous Page Next Page