How to Insert Data into a MySQL Database using PHP

In this article, we will guide you through the steps of inserting data into a MySQL database using PHP. With the use of PHP and MySQL, it is possible to build dynamic, interactive websites and web applications.

Prerequisites

Before we start, there are a few prerequisites that you should have in place:

• A web server with PHP installed
• A MySQL database
• Access to the PHPMyAdmin interface or similar database management tool

Establishing a Connection

The first step in inserting data into a database using PHP is to establish a connection to the database. This is done by using the mysqli_connect() function, which requires the following parameters:

• The name of the database server
• The username used to access the database
• The password for the database user
• The name of the database to connect to

Here is an example of how to establish a connection to a database:

PHP example of how to establish a connection to a database

<‌?php

$server = "localhost";
$username = "root";
$password = "password";
$database = "database_name";

$conn = mysqli_connect($server, $username, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

?>

Executing the Query

With the connection ready, you can now run the INSERT query using mysqli_query(). This function takes the database connection and the SQL string as arguments.

PHP example of executing the query

<‌?php
// Basic input sanitization
$first_name = mysqli_real_escape_string($conn, "John");
$last_name = mysqli_real_escape_string($conn, "Doe");
$email = mysqli_real_escape_string($conn, "[email protected]");

// Ensure the 'users' table exists with columns: first_name, last_name, email
$sql = "INSERT INTO users (first_name, last_name, email)
        VALUES ('$first_name', '$last_name', '$email')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

Note: In production environments, always sanitize user input and use prepared statements to prevent SQL injection.

Closing the Connection

After the data has been inserted, it is good practice to close the database connection to free up resources. This is done using the mysqli_close() function, passing the connection variable as its only argument.

Here is an example of how to close the connection to the database:

PHP example of how to close the connection to the database

<‌?php

mysqli_close($conn);

?>

Conclusion

In this article, we have shown you how to insert data into a MySQL database using PHP. By following these steps, you should be able to insert data into your database with ease. If you have any questions or need further assistance, please do not hesitate to ask.

Practice

What are the steps involved in inserting data into MySQL using PHP?

Create a connection to the MySQL database server.Specify the SQL statement to insert data.Disconnect the MySQL database server.Execute the SQL statement.Display the ID of the inserted record.