CRUD

Create, Read, Update, Delete (CRUD) in PHP using mysqli

We usually encounter CRUD whenever we create a database-driven website. We insert data, display, update and delete if necessary. PHP provides different ways/drivers in doing so. For example, you may want to use PDO rather than mysqli. Now, it depends on your convenience.

This script will serve as a guide for those developers who want to use mysqli to access the functionality provided by MySQL 4.1 and above.

CRUD


How to setup

  1. Download the file from github, CLICK HERE.
  2. Copy and paste the extracted file into your htdocs folder.
  3. Inside the folder you extracted, you can see the database samueldb. Import it into your mysql database.
  4. Configure the credentials inside the connect.php depending on your local setting.
  5. Finally, check the output on your browser.

Let’s explore the code

Perhaps, you might need some explainations how I did this code. Alright, let’s take a look at some necessary files below.


connect.php


<?php
$localhost = "localhost"; //your host name
$username = "root"; // your database name
$password = ""; // your database password
$dbname = "samueldb";

$con = new mysqli($localhost, $username, $password, $dbname);

if($con->connect_error) {
die("connection failed : " . $con->connect_error);
}

/* end of file */
?>

It is essential to connect to the database first before you can execute the CRUD operation. So, we need to configure the credentials in order to make it happen.


insert.php


<?php
require_once 'connect.php';
require_once 'header.php';
?>
<div class="container">
<?php
if(isset($_POST['addnew'])){
if( empty($_POST['firstname']) || empty($_POST['lastname'])
|| empty($_POST['address']) || empty($_POST['contact']) )
{
echo "Please fillout all required fields";
}else{
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$contact = $_POST['contact'];
$sql = "INSERT INTO users(firstname,lastname,address,contact)
VALUES('$firstname','$lastname','$address','$contact')";

if( $con->query($sql) === TRUE){
echo "<div class='alert alert-success'>Successfully added new user</div>";
}else{
echo "<div class='alert alert-danger'>Error: There was an error while adding new user</div>";
}
}
}
?>
<div class="row">
<div class="col-md-6 col-md-offset-3">
<div class="box">
<h3><i class="glyphicon glyphicon-plus"></i>&nbsp;Add New User</h3>
<form action="" method="POST">
<label for="firstname">Firstname</label>
<input type="text" id="firstname" name="firstname" class="form-control"><br>
<label for="lastname">Lastname</label>
<input type="text" name="lastname" id="lastname" class="form-control"><br>
<label for="address">Address</label>
<textarea rows="4" name="address" class="form-control"></textarea><br>
<label for="contact">Contact</label>
<input type="text" name="contact" id="contact" class="form-control"><br>
<br>
<input type="submit" name="addnew" class="btn btn-success" value="Add New">
</form>
</div>
</div>
</div>
</div>
<?php
require_once 'footer.php';

Before we insert data into the database, we need to validate it first using if statement:


if( empty($_POST['firstname']) || empty($_POST['lastname']) || empty($_POST['address']) || empty($_POST['contact']) ){
echo "Please fillout all required fields";
}

After we validate the data, we need to make a sql query.


$sql = "INSERT INTO users(firstname,lastname,address,contact)VALUES('$firstname','$lastname','$address','$contact')";

After setting up the query, we need to execute it using $con->query($sql)


users.php


<?php

require_once 'connect.php';
require_once 'header.php';

echo "<div class='container'>";

if( isset($_POST['delete'])){
$sql = "DELETE FROM users WHERE user_id=" . $_POST['userid'];
if($con->query($sql) === TRUE){
echo "<div class='alert alert-success'>Successfully delete user</div>";
}
}

$sql = "SELECT * FROM users";
$result = $con->query($sql);

if( $result->num_rows > 0)
{
?>
<h2>List of all Users</h2>
<table class="table table-bordered table-striped">
<tr>
<td>Firstname</td>
<td>Lastname</td>
<td>Address</td>
<td>Contact</td>
<td width="70px">Delete</td>
<td width="70px">EDIT</td>
</tr>
<?php
while( $row = $result->fetch_assoc()){
echo "<form action='' method='POST'>"; //added
echo "<input type='hidden' value='". $row['user_id']."' name='userid' />"; //added
echo "<tr>";
echo "<td>".$row['firstname'] . "</td>";
echo "<td>".$row['lastname'] . "</td>";
echo "<td>".$row['address'] . "</td>";
echo "<td>".$row['contact'] . "</td>";
echo "<td><input type='submit' name='delete' value='Delete' class='btn btn-danger' /></td>";
echo "<td><a href='edit.php?id=".$row['user_id']."' class='btn btn-info'>Edit</a></td>";
echo "</tr>";
echo "</form>"; //added
}
?>
</table>
<?php
}
else
{
echo "<br><br>No Record Found";
}
?>
</div>
<?php
require_once 'footer.php';

The code above will display all of the records of students and will execute delete on the row selected by the user. Before we will display the records on our browser, we need to fetch those data from the database.


$sql = "SELECT * FROM users";
$result = $con->query($sql);

Once we retrieved all the data from the database, we need to check if there are records/row:

if( $result->num_rows > 0)

On the other hand, if the user wants to delete a record, we need to execute it using the lines below:


if( isset($_POST['delete'])){
$sql = "DELETE FROM users WHERE user_id=" . $_POST['userid'];
if($con->query($sql) === TRUE){
echo "<div class='alert alert-success'>Successfully delete user</div>";
}
}

In line #9, how did we get $_POST['userid']? This is the value of the user id that we need to delete from the hidden input.


echo "<input type='hidden' value='". $row['user_id']."' name='userid' />";

Insights

This PHP scripts contain basic CRUD operation. However, I did not put thorough security into it. If you want to deploy it during production, you must implement additional security.

If you have comments and suggestions in order to improve this code, don’t hesitate to comment below.

5 Comments

  1. can you add search there , imagine you have many data count like 10,000

  2. Hello manyeree,

    We added snippet on this link. Please check it out.

  3. I would recommend using prepared statements to execute crud operations in PHP. This is because prepared statements execute single query multiple times, making the safest and more efficient method of doing it.

  4. @oliver. Yeah, I agree with you. Although this tutorial is intended for beginner or average level. I’ll be covering advanced in the future.

  5. Thanks for this post. I am a sixty year old just starting out in PHP. Could you add a login page that integrates with the above code.

Leave a Reply

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