Create, Read, Update, Delete (CRUD) in PHP using mysqli
by Sam
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.
How to setup
- Download the file from github, CLICK
HERE
. - Copy and paste the extracted file into your
htdocs
folder. - Inside the folder you extracted, you can see the database
samueldb
. Import it into your mysql database. - Configure the credentials inside the
connect.php
depending on your local setting. - 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> 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.
can you add search there , imagine you have many data count like 10,000
Hello manyeree,
We added snippet on this link. Please check it out.
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.
@oliver. Yeah, I agree with you. Although this tutorial is intended for beginner or average level. I’ll be covering advanced in the future.
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.