CRUD
The Best Guide to PHP CRUD Operations You'll Ever Need
Lesson 6 of 11By Ravikiran A S
Last updated on Nov 22, 2022112723
Table of Contents
How to Create a MySQL Database Connection?How to Create Records?How to Read/View Records?How to Update Records?How to Delete Records?View More
CRUD Operations are typically performed on databases, hence, in this PHP CRUD Operations tutorial, you will implement CRUD techniques on MySQL databases with the help of PHP.
The CRUD acronym comprises all the major operations that are performed on a relational database. It stands for:
C = Create
R = Read
U = Update
D = Delete
How to Create a MySQL Database Connection?
First, create a connection between the database and your PHP code.
The following code acts as the connection between the webpage and the database where the data from the webpage will be stored.
Here, name the file as config.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
How to Create Records?
The first operation in PHP CRUD Operations, Create, is responsible for creating tables or new records into an existing table. To do that, first, you must write the code for the webpage to create an entry in the database.
Name the file as create.php.
<?php
include "config.php";
if (isset($_POST['submit'])) {
$first_name = $_POST['firstname'];
$last_name = $_POST['lastname'];
$email = $_POST['email'];
$password = $_POST['password'];
$gender = $_POST['gender'];
$sql = "INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`, `gender`) VALUES ('$first_name','$last_name','$email','$password','$gender')";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "New record created successfully.";
}else{
echo "Error:". $sql . "<br>". $conn->error;
}
$conn->close();
}
?>
<!DOCTYPE html>
<html>
<body>
<h2>Signup Form</h2>
<form action="" method="POST">
<fieldset>
<legend>Personal information:</legend>
First name:<br>
<input type="text" name="firstname">
<br>
Last name:<br>
<input type="text" name="lastname">
<br>
Email:<br>
<input type="email" name="email">
<br>
Password:<br>
<input type="password" name="password">
<br>
Gender:<br>
<input type="radio" name="gender" value="Male">Male
<input type="radio" name="gender" value="Female">Female
<br><br>
<input type="submit" name="submit" value="submit">
</fieldset>
</form>
</body>
</html>
How to Read/View Records?
The second operation, just as the name suggests, ‘Read’ is used to display or read the data that is already available in the database.
To perform the operation, you need to create a page that displays the records from the table ‘users’.
Now, name the page as view.php
<?php
include "config.php";
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>View Page</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h2>users</h2>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['gender']; ?></td>
<td><a class="btn btn-info" href="update.php?id=<?php echo $row['id']; ?>">Edit</a> <a class="btn btn-danger" href="delete.php?id=<?php echo $row['id']; ?>">Delete</a></td>
</tr>
<?php }
}
?>
</tbody>
</table>
</div>
</body>
</html>
How to Update Records?
The third operation i.e, ‘update’ is used to change or modify the already existing data present in the database.
To do this, you need to create another page to update the details in the database. Here, name the page as update.php
<?php
include "config.php";
if (isset($_POST['update'])) {
$firstname = $_POST['firstname'];
$user_id = $_POST['user_id'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$password = $_POST['password'];
$gender = $_POST['gender'];
$sql = "UPDATE `users` SET `firstname`='$firstname',`lastname`='$lastname',`email`='$email',`password`='$password',`gender`='$gender' WHERE `id`='$user_id'";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "Record updated successfully.";
}else{
echo "Error:" . $sql . "<br>" . $conn->error;
}
}
if (isset($_GET['id'])) {
$user_id = $_GET['id'];
$sql = "SELECT * FROM `users` WHERE `id`='$user_id'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$first_name = $row['firstname'];
$lastname = $row['lastname'];
$email = $row['email'];
$password = $row['password'];
$gender = $row['gender'];
$id = $row['id'];
}
?>
<h2>User Update Form</h2>
<form action="" method="post">
<fieldset>
<legend>Personal information:</legend>
First name:<br>
<input type="text" name="firstname" value="<?php echo $first_name; ?>">
<input type="hidden" name="user_id" value="<?php echo $id; ?>">
<br>
Last name:<br>
<input type="text" name="lastname" value="<?php echo $lastname; ?>">
<br>
Email:<br>
<input type="email" name="email" value="<?php echo $email; ?>">
<br>
Password:<br>
<input type="password" name="password" value="<?php echo $password; ?>">
<br>
Gender:<br>
<input type="radio" name="gender" value="Male" <?php if($gender == 'Male'){ echo "checked";} ?> >Male
<input type="radio" name="gender" value="Female" <?php if($gender == 'Female'){ echo "checked";} ?>>Female
<br><br>
<input type="submit" value="Update" name="update">
</fieldset>
</form>
</body>
</html>
<?php
} else{
header('Location: view.php');
}
}
?>
How to Delete Records?
The last operation of CRUD is Delete and just as the name suggests, it is used to delete an existing entry or table.
To perform this operation, you must create a page that would let you choose the data entry that you want to delete from the database.
Now, name the file delete.php
<?php
include "config.php";
if (isset($_GET['id'])) {
$user_id = $_GET['id'];
$sql = "DELETE FROM `users` WHERE `id`='$user_id'";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "Record deleted successfully.";
}else{
echo "Error:" . $sql . "<br>" . $conn->error;
}
}
?>