Understanding CRUD : How to Read , Update & Delete a record in the Database using PHP (The End).

This is the last part of Understanding Crud in PHP , you can go through the previous parts here and here.

This part will focus on READ , UPDATE & DELETE using PHP.

Let’s get started.

READ.

Read simply means viewing of data stored in the database.

We’ve learnt how to insert the record in the PART 2 , the next step in CRUD is how to view the data stored and present it into a readable form.

Here is the code for viewing the data :

<?php 
require_once ('config.php') ; 
$query = “SELECT * FROM customerdb” ;
$result = mysqli_query ($conn , $query) ;
while ($row = mysqli_fetch_array ($result) ) {

echo ‘<tr>’ ;
echo ‘<td>’.$row [‘Name’]. ‘</td>’ ;
echo ‘<td>’ .$row [‘Email’]. ‘</td>’ ;
echo ‘<td>’ .$row [‘Number’]. ‘</td>’ ;
echo ‘<td>’ .$row[‘Location’]. ‘</td>’ ;
echo ‘<td>’. ‘<a href=”edit.php?edit=’.$row [‘ID’].’” > Edit </a>’.’</td>’ ;
echo ‘<td>’. ‘<a href=”index.php?Delete=’.$row [‘ID’].’” > Delete </a>’.’</td>’ ;
echo ‘<tr>’ ;
}
echo ‘</tr>’ ;

?>

There are two main points worth of discussing in this part :

1. $query = “SELECT * FROM customerdb” ;

SELECT is an SQL command used for selecting columns in a table and the asterisk(*) that followed it means ALL which means that it should select all the columns in the table.

We can limit the columns to be selected by removing the * (ALL) and insert the columns we want to select.

E.g. If all you wanted is the Email column you just remove the * and put Email. You will have something like this :

  $query = “SELECT Email FROM customerdb” ;
2. $row = mysqli_fetch_array ($result)

mysqli_fetch_array is a function in PHP used to fetch the result of a row into an array. In this case $row has been converted into an array and will be used to present our records.

UPDATE.

update means to edit.

After storing a record and you made a mistake during the process the easiest thing to do next is to edit your mistake.

Here is how to update your record in mysql using PHP.

<?php
require_once (‘config.php’) ;
if (isset ($_GET [‘edit’]) ) {
//Checking If the user has submitted the form
if (isset ($_POST [‘submit’] )) {
//checking If the user has completely fill all the form field
if ( isset ($_POST [‘Name’] , $_POST [‘Email’] ,$_POST [‘Number’] ,$_POST [‘Location’] ) ) {
$ID = $_GET [‘edit’] ;
/*
mysqli_real_escape_string is for security purpose. It is used for escaping special characters inserted by the user which can sometimes be harmful to our database
*/

$Name = mysqli_real_escape_string ($conn , $_POST [‘Name’]) ;
$Email = mysqli_real_escape_string ($conn , $_POST [‘Email’]) ; 

$Number = mysqli_real_escape_string ($conn , $_POST [‘Number’]) ;
$Location = mysqli_real_escape_string ($conn , $_POST [‘Location’]) ;
//Inserting the submitted data into the database.
$sql = “UPDATE `customerdb` SET Name = ‘$Name’ , Email = ‘$Email’ , Number= ‘$Number’ , Location = ‘$Location’ WHERE (`ID` = ‘$ID’)” ;
if (mysqli_query ($conn , $sql) ) {

$msg = “Successfully edited” ;

}
else {
$msg = “oops! There is an error when editing your record. Retry again”.mysqli_error ($conn) ; 

}
}
}

This part is straight forward because you only need a single query to get the job done.

$sql = “UPDATE `customerdb` SET Name = ‘$Name’ , Email = ‘$Email’ , Number= ‘$Number’ , Location = ‘$Location’ WHERE (`ID` = ‘$ID’)” ;

The only thing you need is to SET the required column you want to edit.

Here we have Name , Email , Number & Location.

The WHERE clause is required because it gives the pointer of which record is to be edited. Since the ID is the unique column that’s why it is the most approriate to be used.

DELETE.

Delete simply means removing.

Once you delete a record you can’t recover it again.

Here is the code for deleting a record.

$query = “DELETE FROM customerdb WHERE (ID = ‘$ID’)” ;

That’s the command required to delete a record but you must be careful when deleting a record without adding the ‘WHERE’ clause because you can delete every record in a table without the clause.

That bring us to the end of UNDERSTANING CRUD in php.

You can view the Github code here

See you later. Thanks.