Inserting Data Into a MySQL Database Using PHP and MySQLi

by

This tutorial will show you how to insert data into a MySQL database using the latest MySQLi PHP libraries. This tutorial will replace our older tutorial on MySQL libraries and that post will redirect here in the future.

Assumptions

First, we will assume you have PHP, MySQL, and Apache running. I use MAMP for my local development environment in this case. You can download it for free on both Windows and Mac. We also need a SQL client to connect to the database, I’m using Sequel Pro on Mac. You may want to use Heidi SQL on Windows.

To set up this tutorial I’ve created a database with a table called users. The schema is as follows:

Inserting Data into the MySQL Database Using PHP

First, we’re going to set up our connection to the MySQL database. We’re going to set our server name, user name, password, and database name. Then we’re going to make a connection to the server. If there is a connection error we will output the error message. Otherwise, we are not going to do anything else for now.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
   die("MySQL Connection failed: " . $conn->connect_error);
}
?>

Now we will create and execute our insert query. As you see below we create a query variable and write our insert query. Then we execute the query. If there is an error we display it otherwise we display that the database has been updated.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
   die("MySQL Connection failed: " . $conn->connect_error);
}

$query = "INSERT INTO users (name, username) VALUES ('John','spyderman4g63')";

if (!$conn->query($query)){
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}else{
    echo "The database has been updated";
}

$conn->close();
?>

Viewing the Data in the Database

I’m going to look at the table in my SQL tool, which is Sequel Pro on my Mac. Below we can see that the application has inserted one row for me.

That’s all there is to insert data into a MySQL database using the MySQLi libraries in PHP. Check out this tutorial if you want to display MySQL data on a page using MySQLi in PHP.