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.

CRUD

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