Do you need to make a dynamic website? Well, there can’t be any dynamic website without CRUD operations. CRUD stands for create, retrieve, update and delete. So if you are inserting, displaying, updating or deleting information from website database, you are using CRUD operations.
In this article, we are going to learn CRUD operations with PHP and MySQL.
Create, Retrieve, Update and delete Operations using PHP and SQL
Let’s say you need to maintain the record for your books. This isn’t a large or complicated project. I assume that only you are going to input the data, so without focusing much on front end design, we get straight into PHP code.
The first step would be to design the database ‘library’ that has one table ‘books’.
You can write queries in SQL or you can simply browse to phpMyAdmin to create database and table.
Create the Database
The table ‘books’ has four columns:
ID – This would be an integer column. It will act as a primary key and will uniquely identify the row. Select auto_increment and primary key will create this field.
Title – This is varchar(250) column and stores title of the book.
Author – This is varchar(250) column and stores the author(s) of the book.
Publisher – This is varchar(250) column and stores names of the publishers.
Fig 1. Create database ‘library’ in phpMyAdmin
Fig 2. Create table ‘books’ in phpMyAdmin
Fig 3. Create 4 columns for table ‘books’
Remember to select the auto_increment (A_I) option for book_id as we are not going to insert value in id column manually. Every time, a new record is being inserted, the value will be incremented.
Fig 4. Preview of table ‘books’ You can see the yellow key in front of book_id. It confirms that you have selected book_id as a primary key.
Now, when we have everything ready related to database and table, let’s start with code.
Create Operation
Fig 5. A form to enter your book’s detail.
If you notice there is no field for ID. Well, we are not going to enter an ID manually. It will be auto incremented each time we want to create a new record. Thanks to Auto_increment option in MySQL.
The following code block will create the form above.
<!DOCTYPE html>
<html>
<head>
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<style>
body{font-family: 'Open Sans', sans-serif; color:#333; font-size:14px;}
#book_form{padding:50px;}
label{display:inline-block; width:140px; }
</style>
</head>
<body>
<div id="book_form">
<h1>A simple Library information system</h1>
<br/>
<h3> Plese enter your book</h3>
<form action="crud_ac.php" method="post">
<p>
<label>Book Title</label>
<input type="text" name="book_title" size="30">
</p>
<p>
<label>Author Name</label>
<input type="text" name="author_name" size="30">
</p>
<p>
<label>Publisher Name</label>
<input type="text" name="pub_name" size="30">
</p>
<p>
<input type="submit" name="submit" value="Add Book">
</p>
</form>
</div>
</body>
</html>
As you see from the code above, the form will be submitted to crud_ac.php via POST method.
Now create crud_ac.php page:
<?php
// create a connection with database ‘library’.
$conn = mysqli_connect("localhost","root","","library");
//if you failed to connect to database, code should print an error and terminate the execution of rest of //page
if(!$conn){
echo "Error: Unable to connect to MySql";
die();
}
//if form is being submitted, get the values from $_POST array
if(isset($_POST['submit'])){
$title = $_POST['book_title'];
$author = $_POST['author_name'];
$publisher = $_POST['pub_name'];
//create a query to insert values into table ‘books’.
$query="insert into books( title, author, publisher)values( '$title','$author','$publisher')";
//execute query
$result = mysqli_query($conn, $query);
//if query run successfully, it will be redirected to viewbooks.php
if($result){
header('location:viewbooks.php');
}//if
}
Viewbooks.php will show a list of all books being entered.
Fig 6. When you haven’t entered any book yet
So, let’s enter some books.
Fig 7. Let’s add a book by O’Reilly Books
After we click on the ‘Add Book’ button, it will take you to viewbooks.php
Fig 8. We have successfully created a new record for the book
We miss-spelled Publisher name. No worries worry, we can change it by the update operation. Before we do so, Let’s show you the code of the page viewbooks.php
Read Operation
<!DOCTYPE html>
<html>
<head>
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<style>
body{font-family: 'Open Sans', sans-serif; color:#333; font-size:14px;}
#book_form{padding:50px;}
label{display:inline-block; width:220px; }
th, td{width:120px; text-align:left;}
</style>
</head>
<body>
<div id="book_form">
<h3> You have got the following Books</h3>
<br/>
<?php
$conn = mysqli_connect("localhost","root","","library");
if(!$conn){
echo "Error: Unable to connect to MySql";
die();
}
$query="select * from books";
$result = mysqli_query($conn, $query);
if($result){
?>
<table>
<thead>
<tr>
<th>Book ID</th>
<th>Title</th>
<th>Author</th>
<th>Publisher</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result)){
?>
<tr>
<td><?php echo $row['book_id']; ?></td>
<td><?php echo $row['title']; ?></td>
<td><?php echo $row['author']; ?></td>
<td><?php echo $row['publisher']; ?></td>
<td> <a href="editbook.php?id=<?php echo $row['book_id']; ?>">Edit</a> </td>
<td> <a href="deletebook.php?id=<?php echo $row['book_id']; ?>">Delete</a> </td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
}
?>
</div>
</body>
</html>
?>
The page above contains the code that simply retrieves the values from the table ‘books’ using simple “select * from table_name” query. To keep the code simple, I haven’t use pagination here and this would cause all records to be displayed on a single page.
To retrieve values use
$query="select * from books";
After the execution of the query, use a while loop to go through all the records row by row.
while($row = mysqli_fetch_array($result))
This will create a new row in table for each record of the database.
<tr>
<td><?php echo $row['book_id']; ?></td>
<td><?php echo $row['title']; ?></td>
<td><?php echo $row['author']; ?></td>
<td><?php echo $row['publisher']; ?></td>
<td> <a href="editbook.php?id=<?php echo $row['book_id']; ?>">Edit</a> </td>
<td> <a href="deletebook.php?id=<?php echo $row['book_id']; ?>">Delete</a> </td>
</tr>
As you see above, there are two links that lead to the ‘Edit’ and ‘Delete’ pages.
To edit or delete a specific book, you have to give an id of that book through GET.
Update Operation
Let’s create editbook.php now
Get book’s id in a variable first.
$book_id = $_GET['id'];
Now connect to the database.
$conn = mysqli_connect("localhost","root","","library");
if(!$conn){
echo "Error: Unable to connect to MySql";
die();
}
And the Query:
$query ="select * from books where book_id =".$book_id;
Execute the query:
$result = mysqli_query($conn, $query);
if($result){
$num_rows = mysqli_num_rows($result);
if($num_rows==1){
$row = mysqli_fetch_array($result);
Now let’s create the form to edit this particular book.
<form action="editbook_ac.php" method="post"> <p> <label>Book Title</label> <input type="text" name="book_title" size="30" value ="<?php echo $row['title']; ?>"> </p> <p> <label>Author Name</label> <input type="text" name="author_name" size="30" value="<?php echo $row['author'];?>"> </p> <p> <label>Publisher Name</label> <input type="text" name="pub_name" size="30" value="<?php echo $row['publisher'];?>"> </p> <p> <input type="hidden" name="book_id" value="<?php echo $row['book_id']; ?>"> <input type="submit" name="submit" value="Update Book Details"> </p> </form>
Fig 9: Update book form
After you click on ‘Update Book Details’, the following code will execute in edit_ac.php
if(isset($_POST['submit'])){
//addslashes() is a PHP built-in function. It will make sure that quote in O’Reilly doesn’t break the code.
$id = addslashes($_POST['book_id']);
$title = addslashes($_POST['book_title']);
$author = addslashes($_POST['author_name']);
$pub = addslashes($_POST['pub_name']);
$query = "update books set title ='$title', author = '$author' , publisher='$pub' where book_id ='$id'";
$result = mysqli_query($conn, $query);
if($result){
?>
<div class="msg">
<h3>Success!</h3>
<p>
Book Details Updated Successfully!<br/>
<a href="viewbooks.php">View All Books</a>
</p>
</div>
Fig 10: Successfully updated the book’s publisher’s name.
Click on View All Books to confirm the update.
Fig 11. Book detail has been updated successfully.
Delete Operation
Now, what if you want to delete the book? Well, you are going to need the id of the particular book, you want to delete, retrieve the book and delete it.
Create a new file deletebook.php and add the following code.
if(isset($_GET['id'])){
$query = "delete from books where book_id =".$_GET['id'];
$result = mysqli_query($conn, $query);
if($result){
?>
<div class="msg">
<h3>Success!</h3>
<p>
Book deleted Successfully!<br/>
<a href="viewbooks.php">View All Books</a>
</p>
</div>
<?php
}
}
?>
</div>
Related Articles
How to prevent your website from SQL Injection Attacks using PHP?
Remove characters from a string except numbers and letters using PHP
Insert Update Delete example in C# and VB.NET Using ExecuteNonQuery method
Comments