How to Insert Record into a Database using PHP

How to Insert Record into a Database using PHP

Inserting records into a database is one of the most typical processes involved in web development. Using the mysqli extension, PHP offers a quick and effective approach to complete this activity. This article offers a thorough tutorial on using PHP to add records to a database.

Overview

Before we dive into the details, let's take a look at the steps involved in inserting records into a database using PHP:

Connect to the database

Prepare the SQL statement

Execute the SQL statement

Verify if the record was inserted successfully

Close the database connection

Connecting to the Database

The first step in inserting records into a database is to connect to it. PHP provides the mysqli extension, which is used to connect to a MySQL database. The mysqli extension has a mysqli_connect function, which is used to establish a connection to a database. The function takes four arguments:

mysqli_connect(host, username, password, dbname);

host: 

   the name of the host where the database is located

username:

  the username used to connect to the database

password: 

   the password associated with the username

dbname: 

   the name of the database to connect to

Once you have established a connection to the database, you can use it to insert records into the database.

Preparing the SQL Statement

The preparation of the SQL statement comes next. A record is added to the database using a SQL command. The following is the fundamental syntax for adding a record to a database:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

table_name: the name of the table where the record will be inserted

column1, column2, column3, ...: the names of the columns in the table

value1, value2, value3, ...: the values to be inserted into the columns

Executing the SQL Statement

After running the SQL command, you must check to see if the record was successfully added. The mysqli affected rows function can be used to check how many rows the SQL query has affected. The record was successfully inserted if there are just 1 impacted rows.

Closing the Database Connection

Finally, you need to close the database connection after you are done with it. You can use the mysqli_close function to close the connection. The function takes one argument:

mysqli_close(connection);

connection: the database connection to be closed

example demonstrates how to insert a record into a database table named users with columns username, email, and password.

<?php

// Connect to the database

$connection = mysqli_connect("hostname", "username", "password", "dbname");

// Check the connection

if (!$connection) {

    die("Connection failed: " . mysqli_connect_error());

}

// Prepare the SQL statement

$sql = "INSERT INTO users (username, email, password)

VALUES ('john', 'john@example.com', 'password123')";

// Execute the SQL statement

if (mysqli_query($connection, $sql)) {

    echo "Record inserted successfully";

} else {

    echo "Error inserting record: " . mysqli_error($connection);

}

// Close the connection

mysqli_close($connection);

?>

In this example, the first step is to connect to the database using mysqli_connect. Then, the SQL statement is prepared and executed using mysqli_query. Finally, the connection is closed using mysqli_close.