PHP CRUD Tutorial | CRUD in PHP
We have seen insertion, deletion, updation and selection of MySQL data using PHP in previous tutorials. Today we will see all the things together in one application. CRUD : Create, Read,Update and Delete. Sometimes CRUD is referred as BREAD : Browse , Read, Edit, Add and Delete.
In PHP Tutorial, we have seen one session on “How to Create Database and Table in MySQL using PHP”. We have database already created named “Employee” and table named “Emp” with following fields.
| Field Name | DataType | Description |
|---|---|---|
| empId | int | Employee Id, Not NULL, PRIMARY KEY , AUTO INCREMENT |
| firstName | varchar(30) | Employee First Name |
| lastName | varchar(30) | Employee Last Name |
| age | int | Employee Age |
| designation | varchar(30) | Employee Designation |
| salary | int | Employee Salary |
In this PHP CRUD application , we will allow users :
- Create / Add new Data
- Read /View or Retrieve existing Data
- Update/edit existing Data
- Delete existing Data
Lets see first code for Add new data
<table border="1" cellspacing="20">
<tr> <td> Enter First Name : </td> <td><input type="text" name="txtFname" size="30"></td> </tr>
<tr> <td> Enter Last Name : </td> <td><input type="text" name="txtLname" size="30"></td> </tr>
<tr> <td> Enter Age : </td> <td><input type="text" name="txtage" size="30"></td> </tr>
<tr> <td> Enter Designation : </td> <td><input type="text" name="txtdes" size="30"></td> </tr>
<tr> <td> Enter Salary : </td> <td><input type="text" name="txtsalary" size="30"></td> </tr>
<tr><td colspan="2" align="center"><Input type="submit" value="Insert" name="Insert"></td></tr>
</table>
if(isset($_REQUEST["Insert"]))
{
//Code to Insert Data
$fname=$_REQUEST["txtFname"];
$lname=$_REQUEST["txtLname"];
$age=$_REQUEST["txtage"];
$des=$_REQUEST["txtdes"];
$salary=$_REQUEST["txtsalary"];
$qry="insert into emp (firstName, lastName, age, designation,salary) values('".$fname."','".$lname."','".$age."','".$des."','".$salary."')";
$res=mysql_query($qry);
header("Refresh:0"); // This will refresh the current Page.
}
Lets see the code of Read/ Retrieve
// Code to View (Display) Data
$res=mysql_query("Select * from emp");
echo "<table border='1'>";
echo "<tr align='center'><th>Emp-Id</th><th>Emp-Name</th><th>Emp-Age</th><th>Emp-Designation</th>";
echo "<th>Emp-Salary</th><th colspan='2'>Action</th></tr>";
echo "<tr><td colspan='5'></td></tr>";
while($r=mysql_fetch_row($res))
{
echo "<tr>";
echo "<td align='center'>$r[0]</td>";
echo "<td width='200'>$r[1]" . " $r[2]</td>";
echo "<td align='center' width='40'> $r[3]</td>";
echo "<td align='center' width='200'>$r[4]</td>";
echo "<td width='100' align='center'>$r[5]</td>";
echo "<td><a href=crud_delete.php?dno=$r[0]> Delete </a></td>";
echo "<td><a href=crud_update.php?uno=$r[0]> Update </a></td>";
}
In this code I have used :
- mysql_connect() : Used to open connection in MySQL and takes three parameter i.e. SERVER,USER_NAME and PASSWORD. we take default values for these parameters i.e. localhost, root and empty string respectively. This function returns a MySQL link identifier on successful connection and return false if fails to open connection.
- mysql_error() : this returns the text of the error message from previous MySQL operation and empty string if no error occurred.
- mysql_select_db() : Sets the current active database on the server which is associated with specified link identifier returned by mysql_connect().
- mysql_query() : This sends a query to current active database on the server. It takes SQL query as parameter.
- mysql_fetch_row() : This function get a result row as an enumerated array. It returns numerical array corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array and each result column is stored in array offset which starts at offset 0.
- header() : With this function you can redirect to the another page without any click or link. Ex : Header(‘Location : https://www.freefeast.info’); But the header() function, you have to call before any of the actual output is sent may be HTML tags, blank lines.
- header(“Refresh:0”) this will refresh the current page and helps to view latest existing data.
Code For Delete : For Delete we will pass corresponding employee id when user click on Delete link and that will redirect to crud_delete.php. In that we have written the code for deleting data and from that page user will redirects to this page.
<?php
$con=mysql_connect("localhost","root","");
mysql_select_db("employee",$con);
$dnum=$_REQUEST["dno"];
$qry="delete from emp where empId='$dnum'";
$res=mysql_query($qry);
if(!$res)
{
echo "Deletion Error ".mysql_error();
}
else
{
header("location:emp_CRUD.php");
}
}
?>
Code to Update/Edit Data : This code is same as we have seen in update.php in previous session i.e. How to Update MySQL data using PHP.
Lets see Complete Code for PHP CRUD
<html>
<head>
</head>
<body>
<form method="post" action="emp_CRUD.php">
<center> <h2>Employee Management </h2>
<table border="1" cellspacing="20">
<tr> <td> Enter First Name : </td> <td><input type="text" name="txtFname" size="30"></td> </tr>
<tr> <td> Enter Last Name : </td> <td><input type="text" name="txtLname" size="30"></td> </tr>
<tr> <td> Enter Age : </td> <td><input type="text" name="txtage" size="30"></td> </tr>
<tr> <td> Enter Designation : </td> <td><input type="text" name="txtdes" size="30"></td> </tr>
<tr> <td> Enter Salary : </td> <td><input type="text" name="txtsalary" size="30"></td> </tr>
<tr><td colspan="2" align="center"><Input type="submit" value="Insert" name="Insert"></td></tr>
</table>
<br><br>
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
echo "Connection error".mysql_error();
}
else
{
$res=mysql_select_db("employee",$con);
if(!$res)
{
echo "Database not FOund...".mysql_error();
}
else
{ // Code to View (Display) Data
$res=mysql_query("Select * from emp");
echo "<table border='1'>";
echo "<tr align='center'><th>Emp-Id</th><th>Emp-Name</th><th>Emp-Age</th><th>Emp-Designation</th>";
echo "<th>Emp-Salary</th><th colspan='2'>Action</th></tr>";
echo "<tr><td colspan='5'></td></tr>";
while($r=mysql_fetch_row($res))
{
echo "<tr>";
echo "<td align='center'>$r[0]</td>";
echo "<td width='200'>$r[1]" . " $r[2]</td>";
echo "<td align='center' width='40'> $r[3]</td>";
echo "<td align='center' width='200'>$r[4]</td>";
echo "<td width='100' align='center'>$r[5]</td>";
echo "<td><a href=crud_delete.php?dno=$r[0]> Delete </a></td>";
echo "<td><a href=crud_update.php?uno=$r[0]> Update </a></td>";
echo "</tr>";
}
if(isset($_REQUEST["Insert"]))
{
//Code to Insert Data
$fname=$_REQUEST["txtFname"];
$lname=$_REQUEST["txtLname"];
$age=$_REQUEST["txtage"];
$des=$_REQUEST["txtdes"];
$salary=$_REQUEST["txtsalary"];
$qry="insert into emp (firstName, lastName, age, designation,salary) values('".$fname."','".$lname."','".$age."','".$des."','".$salary."')";
$res=mysql_query($qry);
header("Refresh:0"); // This will refresh the current page
}
}
}
?>
</table>
</center>
</form>
</body>
</html>
