Using MySQLi to Insert Data: MySQL Database Management

Since newer versions of PHP no longer support the ability to insert data into a database using PHP extensions, users will need to make use of an extension such as MySQLi to insert data into their databases. Using MySQLi to insert data can be done directly in the command line or via PHP script. In this article we will discuss using MySQLi to insert data into MySQL databases.

Using MySQLi to INSERT Data

First, ensure that your database has been created and can be accessed.
Next, connect to your database server via SSH. Please note that this will require the use of command-line operations. It is only recommended to perform these steps if you are comfortable using command-line interfaces.
Once you’ve connected to your database server you will want to log into MySQL.
Once logged in, you can use the mysql prompt to execute the following query and insert data into your database.
```
$sql = "INSERT INTO data_product1 (size, color, price) VALUES ('M', 'Blue', '39.99')";
```
In our example, data_product represents the database table being modified. The size, color, and price all represent separate columns in the database structure. The values represent the data being stored, in this case the size, color, and price of the product in question. For a typical eCommerce website, a database will contain thousands of tables storing a variety of data ranging from contact information to product descriptions and specifications.

Creating a PHP script to INSERT Data using MySQLi

While it is not possible to use PHP extensions to insert data, you can still write a PHP script that uses the MySQLi extension to insert the data.

The first portion of the script will involve connecting to the database. The dbhost is the hostname of the database server, usually localhost. The dbuser is the database username, dbpass is the password for the database user, and the dbname is the name of the database itself:

<html>
   <head>
      <title>Adding Product Data</title>
   </head>
   <body>
      <?php
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'securepassword123';
            $dbname = 'eCommercedata';
            $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

The next portion of the script will contain the INSERT query with the same information we used in the previous section.

$sql = "INSERT INTO data_product1 ".
               "(size, color, price) "."VALUES ".
               "('M','Blue','39.99$')";
      ?>  
   </body>
</html>

Save this file as mysqli_insertexample.php for use with your web server.

Congratulations, you now know how to insert data using MySQLi!

Trust your business to an industry leader in fast and reliable website hosting solutions. InMotion Hosting offers secure web hosting with 99.99% uptime, 24/7 expert human support, and 100% money-back guarantee.

Share this Article

Need More Help?