Select Insert Update Delete in MySQL using PHP tutorial

In the previous tutorial we saw that How to connect MySQL database with PHP detailed. In this tutorial we will learn that how to Select Insert Update Delete database operations in MySQL using PHP.

Select data from database, Insert data in database, Update and Delete data are common process for every web-application [Normally its called CRUD]. You can do many operations with MySQL database using PHP but here we learn about this basic processes. So let’s start step by step… spotify takipçi satın al

Database Connection

Create config.php file to connect mysql database with PHP. Check config.php file code below.

config.php

<?php
 
$db_hostname = 'localhost'; // Database hostname 
$db_username = 'root'; // Database username 
$db_password = ''; // Database password 
$db_name = 'test'; // Database name 
$conn = mysqli_connect($db_hostname, $db_username, $db_password, $db_name); 
if(!$conn) { 
    echo "Unable to connect database".mysqli_error($conn);die; 
} else { 
    echo "Database connected successfully"; 
}

?>

Insert Data Into MySQL In PHP

Here i will insert data in MySQL database using MySQL INSERT statement.

MySQL Insert Syntax:

INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);

Now, Create a new file named insert.php in project folder and copy – paste below code inside it.

insert.php

<?php 

include 'config.php';	//Database configuration file
mysqli_select_db($conn, $db_name);
$sql = "INSERT INTO users (id, firstname, lastname, email) VALUES (NULL,'Om','Dayal','[email protected]')";
$query = mysqli_query($conn, $sql);
if (!$query) {
    echo "User does not inserted. Error : " . mysqli_error($conn);
} else {
    echo "User inserted successfully.";
}

?>

Now open your browser and locate this link http://localhost/project/insert.php. If you found message like User inserted successfully. then data successfully inserted in database.

Description:
In the above example,

• First we connect MySQL with PHP using config.php file.
• If you are working with many database then you can use mysqli_select_db() function for select database for particular operation.
• Then we use “INSERT INTO” syntax query of MySQL language for insert data into database. After that we use mysqli_query() function for run MySQL syntax query.
• Finally use if…else… condition to check data is inserted or not.

Select Data From MySQL In PHP

Let’s get back recently inserted data. To select data from database using MySQL and PHP, we will use MySQL select statement.

MySQL SELECT Syntax:

SELECT field1, field2,... FROM table_name

Let’s create one new file called select.php . Copy the below code and paste it inside select.php file.

select.php

<?php 
include 'config.php';
mysqli_select_db($conn,$db_name);
$sql = "SELECT * FROM users";
$query = mysqli_query($conn,$sql);

if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}

while($data = mysqli_fetch_array($query))
{
    echo "Id = ".$data['id']."<br>";
    echo "Firstname = ".$data['firstname']."<br>";
    echo "Lastname = ".$data['lastname']."<br>";
    echo "Email = ".$data['email']."<br><hr>";
}
?>

Result:

Id = 1
Firstname = Om
Lastname = Dayal
Email = [email protected]

Now open browser and navigate path name http://localhost/project/select.php. You will find out users name and detail.

Update Data In MySQL Using PHP

Let’s change or update database record info by using MySQL “UPDATE “ query. You can check more about MySQL UPDATE query here.  Here we use table ID field as reference field to update the record. See the below example. First of all lets check MySQL Update syntax.

MySQL UPDATE Syntax

UPDATE table_name SET field = new-value WHERE field = condition;

Create update.php and copy the below code and paste it inside update.php file.

Remember that you can not update single record without any reference field otherwise whole table data will updated.

update.php

<?php 

include 'config.php';
mysqli_select_db($conn, $db_name);
$sql = "UPDATE users SET firstname = 'OM_new',lastname = 'Dayal_new',email = '[email protected]' WHERE id=1 ";
$query = mysqli_query($conn,$sql);

if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}
else
{
    echo "Data successfully updated";
}

?>

Now open your browser and locate path http://localhost/product/update.php. If you find message like this Data successfully updated, then your record is update successfully otherwise you have error in MySQL syntax or something else.

Delete Data In MySQL Using PHP

To delete data from database, you can use DELETE MySQL syntax. Here also you want any reference field to delete data. See the below example for more description.

MySQL DELETE Syntax

DELETE FROM table_name [WHERE Clause]

Create a delete.php file, copy the below code and paste it inside delete.php file.

delete.php

<?php 

include 'config.php';
mysqli_select_db($conn,$db_name);
$sql = "DELETE FROM users WHERE id = 1";
$query = mysqli_query($conn,$sql);

if(!$query)
{
    echo "Query does not work.".mysqli_error($conn);die;
}
else
{
    echo "Data successfully delete.";
}

?>

Now open your browser and locate the path http://localhost/project/delete.php. If you find message like this Data successfully delete. Then you have done it. Your data is deleted from database. Check Users table in PhpMyAdmin.

I hope that you like this Select Insert Update Delete database operations in MySQL using PHP code tutorial. Please share it with your friends and circle. If you have any questions while performing this tutorials then please let me know in comment. I will surely help you. Thank you very much 🙂