What is CRUD
CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for the database. Most applications and projects perform some kind of CRUD functionality. Once you learn about these CRUD operations, you can use them for many projects. For example, if you learn how to create a student table with multiple columns, you can use a similar approach to create an employee table or customer table.
In this session, we'll create a simple PHP application to perform all these operations on a MySQL database table in one place.
Creating the Database Table
Creating the Database Table
Execute the following SQL query to create a table named secetable inside your MySQL database. We will use this table for all of our future operations.
Example
CREATE TABLE secetable (rollno INT NOT NULL PRIMARY KEY,
name VARCHAR(100),
class VARCHAR(255)
Note: Insert Some dummy data to perform following CRUD Operations.
createFirstRecord.php
read.php
Creating the Config File
After creating the table, we need to create a PHP script in order to connect to the MySQL database server. Let's create a file named "connection.php" and put the following code inside it. We'll later include this config file in other pages using the PHP include_once( ) function.
Connection.php
Connection.php
<?php error_reporting(0); $conn = mysqli_connect("localhost","root","","learnmysql"); if($conn) { echo ""; } else { echo "Connection Failed"; }
?>
Creating the Create Page
In this section, we'll build the Create functionality of our CRUD application. Let's create a file named "createFirstRecord.php" and put the following code inside it.
createFirstRecord.php
In this section, we'll build the Create functionality of our CRUD application. Let's create a file named "createFirstRecord.php" and put the following code inside it.
<?php
include_once 'connection.php';
$query = "insert into secetable values(105,'Mohak','3E')";
$data = mysqli_query($conn,$query);
if($data){
echo "Record Inserted";
}
else{
echo "Record not Inserted";
}
?>
Note: Here the Create CRUD operation is used to insert data into the database manually ( static data). But latter we use the form to insert records dynamically.
Inserting Records using Form
insertRecordForm.php<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Insert Record</title>
</head>
<body>
<form method="POST">
<label for="">Rollno. </label>
<input type="number" name="t1"><br><br>
<label for="">Name </label>
<input type="text" name="t2"><br><br>
<label for="">Class </label>
<input type="text" name="t3" ><br><br>
<input type="submit" name="b1" value="Insert Record">
</form>
</body>
</html>
<?php
include_once 'connection.php';
if(isset($_POST['b1']))
{
$roll = $_POST['t1'];
$name = $_POST['t2'];
$class = $_POST['t3'];
if($roll!="" and $name!="" && $class!="")
{
$query = "insert into secetable values('$roll','$name','$class')";
$data = mysqli_query($conn,$query);
if($data)
{
echo "<p style='color:green'>Record Inserted Succesfully <a href='http://localhost/phpCode/02-Classes/Section-3E/05-Database/read.php'>Click this to see updated Records</a>";
}
else
{
echo "Data not Inserted";
}
}
else{
echo "<p style='color:red'> All Fields are required</p>";
}
}
?>
Creating the Read Page
Now it's time to build the Read functionality of our CRUD application. Let's create a file named "read.php" and put the following code inside it. It will simply retrieve the records from the secetable table based on the rollno attribute of the student.
read.php<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Read Records</title>
</head>
<style>
td{
padding:10px;
}
</style>
<body>
<br>
<?php include_once 'connection.php'; $query = "SELECT * FROM secetable"; $data = mysqli_query($conn,$query); $totRec = mysqli_num_rows($data); // echo $totRec; if($totRec!=0) { ?> <table><tr> <th>RollNo.</th> <th>Photo</th> <th>Name</th> <th>Class</th> <th colspan="2">Operations</th>
</tr><?php while($result = mysqli_fetch_assoc($data)) { echo " <tr> <td>".$result['rollno']."</td> <td>".$result['name']."</td> <td>".$result['class']."</td> <a href='update.php?rn=$result[rollno]&nm=$result[name]&cl=$result[class]'>Edit</a> | <a href='delete.php?rn=$result[rollno]' onclick = 'return DeleteRecord()'>Delete</a></h4></td> </tr> "; } } else { echo "No Records found"; } ?> </table>
<a href="http://localhost/phpCode/02-Classes/Section-3E/05-Database/InsertRecordForm.php">+ Add More Records</a>
<script>
function DeleteRecord()
{
return confirm("Do u want to delete");
}
</script>
</body>
</html>
Create the Update Page
Similarly, we can build the Update functionality of our CRUD application. Let's create a file named "update.php" and put the following code inside it. It will update the existing records in the secetable table based the rollno attribute of the student.
update.php <?php include_once 'connection.php' ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>Insert Record</title> </head> <body> <form action="" method="GET"> <label for="">Rollno</label> <input type="number" name="t1" value= "<?php echo $_GET['rn']; ?>"><br></br> <label for="">Name</label> <input type="text" name="t2" value= "<?php echo $_GET['nm']; ?>"><br></br> <label for="">Class</label> <input type="text" name="t3" value= "<?php echo $_GET['cl']; ?>"><br></br> <input type="submit" name="b1" Value="Update"> </form> </body> </html> <?phpif(isset($_GET['b1'])) { $roll = $_GET['t1']; $name = $_GET['t2']; $class = $_GET['t3']; if($roll!="" and $name!="" && $class!="") { $query = "UPDATE secetable SET name='$name', class='$class' where rollno='$roll'"; $data = mysqli_query($conn,$query); if($data) { echo "<p style='color:green'>Record Updated Succesfully <a href='http://localhost/phpCode/02-Classes/Section- 3E/05-Database/read.php'>Click this to see updated Records</a>"; } else { echo "Record not Updated "; } } else { echo "All Fields are required"; } } ?>Create the Delete Page
Finally, we will build the Delete functionality of our CRUD application. Let's create a file named "delete.php" and put the following code inside it. It will delete the existing records from the secetable table based the rollno attribute of the student.
delete.php<?php include_once 'connection.php'; $roll = $_GET['rn']; $query = "delete from secetable where rollno='$roll '"; $data = mysqli_query($conn, $query); if($data) { echo "<script>alert('Record Deleted')</script>"; ?> <meta http-equiv="refresh" content="0; url=http://localhost/phpCode/02-Classes/Section-3E/05-Database/read.php"> <?php } else { echo " Delete Process Fail"; } ?>