How to Create a Database Insert Update Delete Data Using PHP and MySQL

Updated: 06-Mar-2024 / Tags: PHP and MYSQL / Views: 527 - Author: George

Introduction

Every beginner learning PHP must know how to work with databases and MySQL. It's a basic skill set that a PHP programmer must have. In this beginner-friendly tutorial you are going to learn how to create databases, create tables within the databases, insert data into the tables, delete, and update data, and lastly drop (delete) the tables and the databases.

Quick Answer

In this Quick Answer section you will see the basic code needed to create, insert, update, and delete data in a database.

CREATE DATABASE:

$mysqli = new mysqli('localhost', 'admin', 'admin_pass');
$sql = "CREATE DATABASE website";
$mysqli->query($sql);

CREATE TABLE

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");

$sql_create_table = "CREATE TABLE IF NOT EXISTS users (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL,
  email VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL,
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

$mysqli->query($sql_create_table);

INSERT DATA

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");

$username = "JohnDoe";
$email = "johndoe@example.com";
$password = "password123";

$sql = "INSERT INTO users (username, email, password) 
VALUES ('$username', '$email', '$password')";
$mysqli->query($sql);

UPDATE DATA

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");
$email = "johndoe@example.com";
$new_password = "NewPassword";

$sql = 
"UPDATE users SET password='$new_password' WHERE email='$email'";
$mysqli->query($sql);

DELETE DATA

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");
$email = "johndoe@example.com";

$sql = "DELETE FROM users WHERE email='$email'";
$mysqli->query($sql)

DROP TABLE

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");

$sql = "DROP TABLE IF EXISTS users";
$mysqli->query($sql);

DROP DATABASE

$mysqli = 
new mysqli('localhost', 'admin', 'admin_pass', "website");
$database = "website";

$sql = "DROP DATABASE IF EXISTS $database";
$mysqli->query($sql);

If the Quick Answer section is covering you that is good. But if you need a detailed explanation feel free to read the rest of the article.

Creating a database

The first thing that we will do starting this tutorial is to create the database which we will work with. In this PHP script we connect to the MySQL server and create a database named 'website'.

$mysqli = new mysqli('localhost', 'username', 'password');
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

$db_name = "website";
$sql = "SHOW DATABASES LIKE '$db_name'";
$result = $mysqli->query($sql);

if($result->num_rows > 0){
    echo "Database '$db_name' already exists";
}else{
    $sql = "CREATE DATABASE $db_name";
    $res = $mysqli->query($sql);
    if($res){
        echo "Database created successfully";
    }else{
        echo "Error creating database: " . $mysqli->error;
    }
}
$mysqli->close();

Let's explain the PHP code above.

Connect to the MySQL server:
```
$mysqli = new mysqli('localhost', 'username', 'password');
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}
			
```
To connect to the MySQL server we use the mysqli object. We pass the 'servername', MySQL 'username', and MySQL 'password' as arguments. And we store the returned mysqli object in the $mysqli variable.

Then we use an if statement to check the 'connect_errno', (connect error number), property. If the returned value is NOT zero, != 0, we stop the script using the die() function to display the error that the MySQL server returned using the $mysqli->connect_error property.
```
$mysqli = new mysqli('localhost', 'username', 'password','db-name');
```
Almost every time we connect to the MySQL server, we specify the name of the database we're connecting to as a fourth argument ('db-name').
```
$mysqli = new mysqli('localhost', 'username', 'password');
```
However, in our case, we don't have a database yet, therefore, we omit the database name from the arguments.
Setting the database name:
```
$db_name = "website";
```
We assign the name of the database we want to create to the $db_name variable.
Checking if database exists:
```
$sql = "SHOW DATABASES LIKE '$db_name'";
$result = $mysqli->query($sql);
if($result->num_rows > 0){
    echo "Database '$db_name' already exists";
}
			
```
We create an SQL query string to check if a database with the name stored in the variable $db_name already exists. This query is formed using the 'SHOW DATABASES LIKE' statement, searching for a database matching the specified name.

We then execute the query using the $mysqli object and store the result in the $result variable.

Next, we check if the query result contains any rows using the 'num_rows' method. If the number of rows returned is greater than zero, we echo a message indicating that the database with the name stored in $db_name already exists.
Execute the query:
```
else{
    $sql = "CREATE DATABASE $db_name";
    $res = $mysqli->query($sql);
    if($res){
        echo "Database created successfully";
    }else{
        echo "Error creating database: " . $mysqli->error;
    }
}
			
```
Otherwise, if the database does not already exist, we proceed to create it.

We construct an SQL query to create a new database using the name stored in the variable $db_name. This query is executed using the $mysqli object, and the result is stored in the variable $res.

Then we check if the query was executed successfully. If so we echo a success message, else we use the $mysql->error to show the error that the mysql server returned.
Closing the connection:
```
$mysqli->close();
```
Closing the connection with the MySQL server. Although it's not necessary, it's considered a good practice to do so.
Show created database:

After running the code and searching in the MySQL server, we see that a database named 'website' is created.
```
mysql> show databases like 'website';
+--------------------+
| Database (website) |
+--------------------+
| website            |
+--------------------+
1 row in set (0,00 sec)
```
If you're interested in learning more about the mysqli class, you can click on the provided link to visit php.net. There, you'll find detailed documentation on this topic. The MySQLi object

Creating a users table

In this script we'll create a table named 'users' within the 'website' database.

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

$sql_create_table = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($mysqli->query($sql_create_table) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $mysqli->error;
}
$mysqli->close();

Let's explain the above code.

Connecting to the database

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
   die($mysqli->connect_error);
}

Again, we'll be connecting to the MySQL server and to the database named 'website' that we created.

Creating the Users Table:
```
$sql_create_table = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
			
```
We define an SQL query string to create a table named 'users' if it does not already exist. The query string is stored in the $sql_create_table variable.

This table will have several columns: 'id', 'username', 'email', 'password', and 'reg_date'.

The 'id' column is defined as an integer with auto-increment and set as the primary key.

The 'username', 'email', and 'password' columns are defined as VARCHAR types, with specified lengths and constraints to ensure they are not null.

Additionally, the 'reg_date' column is defined as a TIMESTAMP type with a default value of the current timestamp.
Executing the SQL query
```
if ($mysqli->query($sql_create_table) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $mysqli->error;
}
$mysqli->close();
			
```
We execute the SQL query string wrapped in an if statement to check if the query is successful.

If the condition returns true, we echo a success message, else we echo the returned mysql error using $mysqli->error.

At the end of the script we close the mysql connection.

Show user table

After running the above script and checking our database, we see that our table is created containing the columns and their attributes that we specified.

mysql> show tables;
+-------------------+
| Tables_in_website |
+-------------------+
| users             |
+-------------------+
1 row in set (0,00 sec)

mysql> show columns from users;
+----------+--------------+------+-----+-------------------+-------------------+
| Field    | Type         | Null | Key | Default           | Extra             |
+----------+--------------+------+-----+-------------------+-------------------+
| id       | int unsigned | NO   | PRI | NULL              | auto_increment    |
| username | varchar(30)  | NO   |     | NULL              |                   |
| email    | varchar(50)  | NO   |     | NULL              |                   |
| password | varchar(255) | NO   |     | NULL              |                   |
| reg_date | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0,01 sec)

Insert data in the users table

In this script we'll be inserting a user in the users table.

$username = "JohnDoe";
$email = "johndoe@example.com";
$password = "password123";

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

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

if ($mysqli->query($sql) === TRUE) {
    echo "New user inserted successfully";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();

Let's break down the above code.

Defining the values:
```
$username = "JohnDoe";
$email = "johndoe@example.com";
$password = "password123";
			
```
We define values to the $username, $email, and $password variables to insert a user in the users table.

Connecting to the database:

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
   die($mysqli->connect_error);
}

Connecting to the database as we always do.

Writing the SQL query:
```
$sql = "INSERT INTO users (username, email, password) 
   VALUES ('$username', '$email', '$password')";
			
```
We construct an SQL query string to insert data into the 'users' table. The query specifies the columns 'username', 'email', and 'password' into which data will be inserted.

We use the variables '$username', '$email', and '$password' to represent the values that will be inserted into these columns.

NOTICE: The variables that we use has to be inside single quotes.
Executing the query:
```
if ($mysqli->query($sql) === TRUE) {
    echo "New user inserted successfully";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();
			
```
We execute the query wrapped in an if statement so we can check if the data where inserted successfully.

If the query was successful we echo a success message, else we echo the MySQL error.

And last we close the database connection.

Show inserted data

Running a select query we see the inserted data in the users table.

mysql> select * from users;
+----+----------+---------------------+-------------+---------------------+
| id | username | email               | password    | reg_date            |
+----+----------+---------------------+-------------+---------------------+
|  1 | JohnDoe  | johndoe@example.com | password123 | 2024-03-05 11:28:02 |
+----+----------+---------------------+-------------+---------------------+
1 row in set (0,00 sec)

Update the User Data

In this PHP script we will update the users password.

$email = "johndoe@example.com";
$new_password = "newPassword";

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

$sql = 
"UPDATE users SET password='$new_password' WHERE email='$email'";

if ($mysqli->query($sql) === TRUE) {
    echo "User password updated successfully";
} else {
    echo "Error updating user password: " . $mysqli->error;
}

$mysqli->close();

Let's break down the PHP script above.

Defining the values
```
$email = "johndoe@example.com";
$new_password = "newPassword";
				
```
To update data in a database, we always require a reference that points to the specific row we intend to update.

Here we are going to use the user's email to locate the corresponding record in the database to update the password.

Connecting to the database

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
   die($mysqli->connect_error);
}

Again we need to connect to the database.

Writing the SQL query
```
$sql = 
"UPDATE users SET password='$new_password' WHERE email='$email'";
```
We construct an SQL UPDATE query to modify the 'password' field in the 'users' table.

The WHERE clause ensures that the update operation is applied only to the row where the 'email' field matches the value stored in the variable '$email'.
Executing the query
```
if ($mysqli->query($sql) === TRUE) {
    echo "User password updated successfully";
} else {
    echo "Error updating user password: " . $mysqli->error;
}

$mysqli->close();
				
```
We execute the query wrapped in an if statement so we can check if the data where updated successfully.

If the query was successful we echo a success message, else we echo the MySQL error.

And last we close the database connection.

Show updated data

When we run the update script and check out the users table we will see that the password field is updated with the new password.

mysql> select * from users;
+----+----------+---------------------+-------------+---------------------+
| id | username | email               | password    | reg_date            |
+----+----------+---------------------+-------------+---------------------+
|  1 | JohnDoe  | johndoe@example.com | newPassword | 2024-03-05 11:28:02 |
+----+----------+---------------------+-------------+---------------------+
1 row in set (0,00 sec)

Delete the User

With this PHP script we are going to delete a user from the database.

$email = "johndoe@example.com";

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
    die($mysqli->connect_error);
}

$sql = "DELETE FROM users WHERE email='$email'";
if ($mysqli->query($sql) === TRUE) {
    echo "User deleted successfully";
} else {
    echo "Error deleting user: " . $mysqli->error;
}

$mysqli->close();

Setting the email value
```
$email = "johndoe@example.com";
```
In order to delete a user's record from the database, we require the user's email as a reference.

Database connection

$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
    die($mysqli->connect_error);
}

Connecting to the database.

Writing the query
```
$sql = "DELETE FROM users WHERE email='$email'";
```
Writing a DELETE query to remove the user from the database.

The WHERE clause ensures that we delete the record that matches the given email.
Executing the query
```
if ($mysqli->query($sql) === TRUE) {
    echo "User deleted successfully";
} else {
    echo "Error deleting user: " . $mysqli->error;
}
 
$mysqli->close();
			
```
As we did in the previous PHP scripts, we check if the query is successful. If so we echo a success message, else we echo the MySQL server error.

And last we close the database connection.

Drop the Users Table

In this script we drop, (delete), the users table from the database.

 // Step 1
$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

// Step 2
$sql = "DROP TABLE IF EXISTS users";

// Step 3
if ($mysqli->query($sql) === TRUE) {
    echo "Table 'users' dropped successfully";
} else {
    echo "Error dropping table: " . $mysqli->error;
}
$mysqli->close();

Let's explain the steps in the code above.

Connecting to the database

Using again the mysqli object to connect to the database.
Writing the SQL query

We construct a DROP TABLE query to drop (delete) the users table.
Executing the query

If the DROP TABLE query is successful we echo a success message, else we echo the MySQL error.

And last we close the connection.

Drop the Database

And in our last PHP script we drop (delete) the database.

// Step 1
$database = "website";

// Step 2
$mysqli = new mysqli('localhost', 'admin', 'admin_pass', "website");
if($mysqli->connect_errno != 0){
	die($mysqli->connect_error);
}

// Step 3
$sql = "DROP DATABASE IF EXISTS $database";

// Step 4
if ($mysqli->query($sql) === TRUE) {
    echo "Database '$database' dropped successfully";
} else {
    echo "Error dropping database: " . $mysqli->error;
}

// Step 5
$mysqli->close();

Defining the db name

We define the database that we want to drop in a variable named $database.
Database connection

Connecting to the database.
Creating the SQL query

We construct a DROP DATABASE query to drop (delete) the 'website' database.
Executing the query

If the DROP DATABASE query is successful we echo a success message, else we echo the MySQL error.
Closing the connection

And last we close the connection.

Summary

In this tutorial we learned how to use PHP and MySQL to work with databases. We connected to the MySQL server using the mysqli class and created a database named 'website' and within, a table named 'users'.

We saw how to insert data into the users table using an INSERT SQL query, and how to UPDATE, and DELETE data using the users email as a reference to locate the corresponding record.

When we work with databases, security is our top priority. Learning to write MySQL prepared statements minimizes the risk of SQL injection attacks and enhances the security of database interactions. Check out the article on How to Write MySQL prepared statements

Last Words

And that's it. I hope you like the article and everything is understandable.
If you find any errors, please leave a comment so i can update the code.

Source code

You can download the source code and use it in any way you like.

Times downloaded: 32

Buy me a coffee

If you like to say thanks, you can buy me a coffee.

Buy me a coffee with paypal

Comment section

You can leave a comment, it will help me a lot.

Or you can just say hi. 😉

Created By Digitalfox

thumbnail of a responsive navigation system

Responsive navigation with working login system using PHP Mysql and Ajax

Responsive navigation menu that integrates a secure login and register system, with a forgot password action, and a contact form.
The whole system is animated in slide-in sidebars and shows up on the right side of the web-design.

How to Create a Database Insert Update Delete Data Using PHP and MySQL

Quick Answer

Creating a database

Connect to the MySQL server:

Setting the database name:

Checking if database exists:

Execute the query:

Closing the connection:

Show created database:

Creating a users table

Connecting to the database

Creating the Users Table:

Executing the SQL query

Show user table

Insert data in the users table

Defining the values:

Connecting to the database:

Writing the SQL query:

Executing the query:

Show inserted data

Update the User Data

Defining the values

Connecting to the database

Writing the SQL query

Executing the query

Show updated data

Delete the User

Setting the email value

Database connection

Writing the query

Executing the query

Drop the Users Table

Connecting to the database

Writing the SQL query

Executing the query

Drop the Database

Defining the db name

Database connection

Creating the SQL query

Executing the query

Closing the connection

Summary

Last Words

Source code

Buy me a coffee

Buy me a coffee with paypal

Comment section

Created By Digitalfox