Select Insert Update Delete in MySQL using PHP tutorial

Select Insert Update Delete in PHP MySQL

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. 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…

Important for you : Login and logout example using session in PHP

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

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','Om@xmail.com')";
$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. Check config.php file code also
  • 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 by above code. To select data from database using MySQL and PHP. Let’s create one new file called “select.php”. Copy the below code and paste it inside “select.php” file.

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

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']."&lt;br&gt;";
    echo "Firstname = ".$data['firstname']."&lt;br&gt;";
    echo "Lastname = ".$data['lastname']."&lt;br&gt;";
    echo "Email = ".$data['email']."&lt;br&gt;&lt;hr&gt;";
}
?>;

Result:

Id = 1
Firstname = Om
Lastname = Dayal
Email = Om@xmail.com

Update data in mysql using php

Let’s change or update database record info by using “UPDATE DATA” query syntax of MySQL. Remember that you can not update single record without any reference field otherwise whole table data will updated. Here we use table ID field as reference field to update the record. See the below example. Create “update.php” and copy the below code and paste it inside “update.php” file.

update.php

<?php 
include 'config.php';
mysqli_select_db($conn, $db_name);
$sql = "UPDATE users SET firstname = 'OM_new',lastname = 'Dayal_new',email = 'om@new.com' 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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *