Insert data into MySQL database with PHP in XAMPP

Once we have created a MySQL database and table, we can start inserting the data (ie records) into it. In this tutorial, we are going to learn how to insert data into MySQL database using PHP in XAMPP stack.

Requirements

Make sure you have set up the XAMPP stack in your system. The following guide explains how to set up the XAMPP stack in Linux.

Setting up XAMPP is much easier than LAMP and LEMP stacks. So we will be using XAMPP stack in this guide.

After you set up the XAMPP stack, you need to create a MySQL database and table in the database. Refer to the following guide to know how to create MySQL database and table in XAMPP stack.

For demonstration purposes, I’m going to create a table called “sale” in a database called “my company” with the schedule below.

Inserting data into MySQL database using PHP

We can insert a single record or multiple records at once in a MySQL table. First we’ll see how to insert a single record.

Inserting a single record into a MySQL table with PHP

We will use a SQL Query statement in our PHP code to insert a record into the table. We can insert a single record/row into a table using the INSERT assignment.

Query syntax:

INSERT INTO table_name(column1,column2,………….) VALUES (value1,value2,………….)  

(Or)

INSERT INTO table_name VALUES (value1,value2,………….)

Where table name is the name of the table and columns are the column name and values ​​are inserted into the table.

To step

1. Enter the server name, MySQL username, password and database name in the PHP code.

Here is the server name localhostusername is root and password is blank. And the database name is my_company, we create a table called “sales” in this database.

2. Connect using the information above.

By using my_sqli_connect() function, we will establish a connection. Three parameters are needed. The first is the server name, the second is the username, and the last is the password. It also requires a database name which is optional here as we are just establishing the connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password,$database_name)

3. Check the connection.

We can check the connection using the mysqli_connect_error() function specified in a if condition. This function displays an error if the connection has failed.

4. Specify the SQL query to insert one record into the table.

In this step, we specify the SQL query to insert values ​​into the database. Let the database name be “my_company” and we store it in a variable called question† The table name is ‘sales’ with three columns.

Code:

$query = "INSERT INTO sales (id,name,count) VALUES (1,'Cooking-Gas',40)";

5. Check the creation.

If we want to check the entered values ​​or not, we can use the . use mysqli_query() function.

Code:

mysqli_query($connection, $query)

Two parameters are needed.

• The $connection parameter specifies the connection details.
• The $query parameter is the sql query we use to insert a record/row into the table

If this condition is True, a row is inserted. Otherwise, an error is returned. We can check the error using the mysqli_error($connection) function.

6. Close the connection.

This is the last step where we need to close the connection with the mysqli_close() function.

Code:

mysqli_close($connection);

Now let’s write a sample PHP code based on the steps above.

PHP code to insert one record in MySQL table

Create a new file named insert.php below the /htdocs folder with the following contents in it.

NB: If you are using Linux, the htdocs folder will be under /opt/lampp/ folder. If you are using Windows, the htdocs will usually be inside C:xampp folder.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to insert a row into sales table
$query = "INSERT INTO sales (id,name,count) VALUES (1,'Cooking-Gas',40)";
if (mysqli_query($connection, $query)) {
  echo "record Inserted Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and go to http://localhost/insert.php† You will see such output in your browser window.

You can also check if the record/row has been inserted from phpMyAdmin. To do this, open the phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL of the browser.

Go to my_company -> sales and click Browsing to see the inserted record.

As you can see, we have successfully inserted one record into the table named “sales” in the my_company database.

Insert multiple records in a MySQL table with PHP

We can insert multiple records/rows into a table with the INSERT assignment.

Query syntax:

INSERT INTO table_name(column1,column2,………….) VALUES (value1,value2,………….)

(Or)

INSERT INTO sales VALUES (value1,value2,………….)

Where table name represents the name of the MySQL table and columns, insert the column name and values ​​into the table.

To step

1. Enter the server name, MySQL username, password and database name.

Here is the server name localhostusername is root and password is blank. And the database name is “my_company”, and we create a table in this database.

2. Connect using the information above.

By using mysqli_connect() function, we will establish a connection. Three parameters are needed. The first is the server name, the second is the username, and the last is the password. It also requires a database name which is optional here as we are just establishing the connection.

Code:

$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

3. Check the connection.

We can check the connection with: mysqli_connect_error() function specified in a if condition. This function displays an error if the connection has failed.

4. Specify the SQL query to insert multiple records into the table.

In this step, we specify the SQL query to insert values ​​into a variable in the database. Let the database name be my company and we store in a variable called question† The table name is sale which has three columns.

Code:

$query = "INSERT INTO sales (id,name,count) VALUES (5,'Shampoo',10);";
$query .= "INSERT INTO sales (id,name,count) VALUES (2,'Milk',20);";
$query .= "INSERT INTO sales (id,name,count) VALUES (3,'Books',14);";
$query .= "INSERT INTO sales (id,name,count) VALUES (4,'Chocos',45);";
$query .= "INSERT INTO sales (id,name,count) VALUES (6,'Eggs',12)";

Note the period (.) after the first record. You must put a period (.) before the equl sigh of the second query.

5. Check the creation.

If we want to check whether the entered values ​​or not, we can use mysqli_query() function.

Code:

mysqli_query($connection, $query)

As you can see, two parameters are needed.

• The $connection parameter specifies the connection details.
• The $query parameter is the sql query we use to insert multiple rows in the table

If this condition is True, the rows are inserted. Otherwise, an error is returned. We can check the error using the mysqli_error($connection) function.

6. Close the connection.

This is the last step where we need to close the connection with the mysqli_close() function.

Code:

mysqli_close($connection);

Now let’s write a sample PHP code to insert 5 records based on the above steps.

PHP code to insert multiple records in MySQL table

Create a new file named insert_multiple.php below the /htdocs folder with the following contents in it.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name - "my_company"
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//sql query to insert 5 rows into sales table
$query = "INSERT INTO sales (id,name,count) VALUES (5,'Shampoo',10);";
$query .= "INSERT INTO sales (id,name,count) VALUES (2,'Milk',20);";
$query .= "INSERT INTO sales (id,name,count) VALUES (3,'Books',14);";
$query .= "INSERT INTO sales (id,name,count) VALUES (4,'Chocos',45);";
$query .= "INSERT INTO sales (id,name,count) VALUES (6,'Eggs',12)";

if (mysqli_multi_query($connection, $query)) {
  echo "records Inserted Successfully";
} else {
  echo "Error:" . mysqli_error($connection);
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and go to http://localhost/insert_multiple.php† You will see such output in your browser window.

Let’s check if the records are actually inserted or not from the phpMyAdmin dashboard.

Open the phpMyAdmin dashboard by navigating to http://localhost/phpmyadmin URL of the browser. Go to my_company-> sales and click Browsing to see the inserted records.

Conclusion

In this tutorial, we discussed how to insert data into the MySQL database using PHP code in the XAMPP stack. We have provided two sample PHP codes to show you how to insert single and multiple records into a MySQL table.

Change the code according to your requirements and test it on your system. If you have any questions, let us know in the comment section below.

We will learn other PHP MySQL related topics in our upcoming tutorials.