MYSQL Insert Select Update and Delete Queries Examples

MySql Insert Update Delete CRUD MySQLi

This tutorial will explain how to write MYSQL Insert Select Update and Delete Queries Examples and how to access MYSQL database using PHP. PHP has the built-in functions to connect the MYSQL database and to retrieve, update and delete the data from MYSQL database. Accessing MYSQL database from PHP is very easy using the following functions:

  • mysqli_connect – To connect the database by using the specified configuration.
  • mysqli_query – To execute a query to get resource id.
  • mysqli_fetch_row – To read row data with the reference of the resource id. There are many MySQL fetch functions in PHP to read table rows.

I have added an example code for accessing MySQL database using PHP. I have used the database name as “examples” and table named “cars” on which the create, read, update and delete (CRUD) functionalities are going to be performed.

Create New Record
The below example code shows the INSERT query to add a new row to the database table cars by mapping values to its columns car_name and car_color. Since the column values are string data, those are enclosed with single quotes. The mysqli_query() function is used to execute the query.


<?php
mysqli_query($conn,"INSERT INTO cars (car_name, car_color) VALUES ('Jaguar', 'Brown')");
mysqli_close($conn); 
?>

Read/Retrieve Data from Table
The above SELECT query is used to read database table rows. The following code shows how to read/retrieve all rows from the cars table. I used mysqli_fetch_assoc() function to get the associative array column fields and values for each row. The code will return a list of car name and its color in an associative array.


<?php
$result = mysqli_query($conn,"SELECT * FROM cars");
while($row=mysqli_fetch_assoc($result)) {
     $tblEntries[] = $row;
} 
mysqli_close($conn); 
?>

We can read filtered data by using the WHERE clause. The following SELECT query is used to read the list of cars that are in ‘Brown’ color.


<?php
SELECT * FROM cars WHERE car_color='Brown';
?>

Update Table Row
The UPDATE query is used to change the value of the column by setting a new value. It uses the WHERE clause to apply the condition for updating the row data. The following code is used to change the car_name as ‘Jaguar’ for the rows which have the value Brown in the car_color.


<?php
mysqli_query($conn,"UPDATE cars SET car_name='Jaguar' WHERE car_color='Brown'");
mysqli_close($conn);
?>

Deleting the selected row
The DELETE query shown below is to delete all the rows containing car_color as ‘Brown’


<?php
mysqli_query($con,"DELETE FROM cars WHERE car_color='Grey'");
mysqli_close($conn);
?>

You may also like...