HOW TO CREATE 'CRUD' APPLICATION IN PHP & MYSQL ?
This is simple crud Application in PHP & MySQL. CRUD stands for create, read, update and delete. Create means inserting data into database. Read means reading data from database. Update means updating records, Delete means deleting data from database. Here is a step-by-step guide on creating a CRUD system using PHP & MySQL.
STEPS TO CREATE 'CRUD' APPLICATION IN PHP & MYSQL:
Step 1: Create Database and table
First we create a Database in mysql. Database name is user.In the user database create a table called user_data. In this table there are three columns:
- Column Name: id, This column is primary field with auto-increment functionality.
- Column Name: name, This column has varchar datatype with length 30.
- Column Name: email, This column has varchar datatype with length 70.
Step 2: For Connecting Database table, Create a connect file in php.
Now, Write a connection code in PHP as shown below in the file that name is 'connect.php' and stores in your root directory of Application to do the connection with database.
FILE NAME: connect.php
<?php
$connection=mysqli_connect("localhost","root","","user");
if(!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
?>
Explanation of above code:
- In above code use mysqli_connect function to connect with database. First parameter is server name (localhost),second is username, third is password(“”), Fourth is database name.
- mysqli_connect_error function give error if not connect to database.
NOTE: Insert some dummy data in table.
Step 3: Create a index page for listing data from table using php and Create a CSS file.
First Create index.php file and write following code in that file.
FILE NAME: index.php
<?php
include("connect.php");
$query="select * from user_data";
?>
<html>
<head>
<title>Crud Example</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<h2 align="center"> View Data</h2>
<div class="getmsg">
<?php
if(isset($_GET['msg'])) {
echo $_GET['msg'];
}
?>
</div>
<button>
<a href="insert.php">Insert New Record</a>
</button>
<table class="tb">
<tr>
<th>No.</th>
<th>Name</th>
<th>Email</th>
<th colspan="2">Action</th>
</tr>
<?php
if($record=mysqli_query($connection,$query)) {
$num=1;
while($rowData=mysqli_fetch_array($record)) {
?>
<tr>
<td><?php echo $num; ?></td>
<td><?php echo $rowData['name']; ?></td>
<td><?php echo $rowData['email']; ?></td>
<td ><a href="insert.php?cid=<?php echo $rowData['id']; ?>">EDIT</a></td>
<td ><a href="delete.php?cid=<?php echo $rowData['id']; ?>">DELETE</a></td>
</tr>
<?php
$num ++;
)
}
?>
</table>
</body>
</html>
Explanation of above code:
- first add the connect.php file by include() function.
- MySQL select query which will be used to fetch data from user_data table. This query will be executed by using mysqli_query function and return id which store in $record variable and will be use for fetch data.
- before displaying data, we fetched data one by one by using mysqli_fetch_array() function.
After creating 'index.php' file, create CSS file 'style.css' to give proper design to your index page.
FILE NAME: style.css
body {
font-family: Helvetica;
}
.content {
border-radius: 5px;
text-align: center;
padding: 10px;
}
.row {
padding: 10px;
}
button {
background-color: #03a9f4;
float: right;
padding: 10px 15px 10px 15px;
}
.tb {
font-family:sans-serif;
border-collapse: collapse;
width: 100%;
padding: 10px;
}
.tb td {
border: 1px solid #ddd;
padding: 8px;
text-align: center;
}
.tb tr:nth-child(even){
background-color: #f2f2f2;
}
.tb tr:hover {
background-color: #ddd;
}
.tb th {
padding-top: 12px;
padding-bottom: 12px;
background-color: #337ab7;
color: white;
text-align: center;
}
.getmsg {
text-align: center;
font-size: 20px;
padding: 5px;
}
.errormsg{
padding: 10px;
border-radius: 10px;
text-align: center;
}
Step 4: Create a HTML form to insert new record.
Click on insert new record Button, we will be redirect to HTML form. It's name is 'insert.php', which will be used for both insert and edit process.
FILE NAME: insert.php
<html>
<head>
<title>Crud Example</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<h1 align="center">Insert Form</h1>
<div class="content">
<form action="insert_back.php" method="POST">
<div class="row">
<label>Name</label>
<input type="text" name="name" id="name" value="<?php if(isset($row['name'])){echo $row['name'];}?>">
</div>
<div class="row">
<label>Email</label>
<input type="text" name="email" id="email" value="<?php if(isset($row['email'])){echo $row['email'];}?>">
</div>
<div class="row">
<div class="cols">
<input type="submit" name="submit" value="submit">
</div>
<div class="cols">
<input type="hidden" name="cid" value="<?php if(isset($_GET['cid'])){echo $_GET['cid'];} ?>">
</div>
</div>
</form>
</div>
</body>
</html>
Explanation of above code:
- In this form give action on insert_back.php and write 'POST' method in method attribute of form. Here on click the submit button the form data are submitted and will available into insert_back.php page inside a super global variable $_POST.
Step 5: Inserting data into the database.
Create a new php file named insert_back.php because we have set in action attribute of form element. So click on submit button, posted data will be available on this page inside $_POST super global variable.
FILE NAME: insert_back.php
<?php
include("connect.php");
if(isset($_POST['submit'])) {
$name=$_POST['name'];
$email=$_POST['email'];
if(empty($_POST['cid'])) {
$query="insert into user_data (name,email) values ('".$name."','".$email."')";
$msg="Insert successful";
}
$result=mysqli_query($connection,$query);
if($result) {
header("Location:index.php?msg=".$msg);
} else {
$msg="Problem with Insert";
header("Location:index.php?msg=".$msg);
}
}else {
$msg="Please enter the user data";
header("Location:index.php");
}
?>
Explanation of above code:
- In above code if(isset($_POST[‘submit’])) and in next line if(empty($_POST[‘cid’])” conditions to check that POST data set or not using isset() function and “cid” hidden form element is empty or not using empty() function.
- the query run successfully we redirect to index page using header. If data inserted properly, mysqli_query will return true which we will store into on variable $result. value of $result we will redirect to user once again index.php page with proper message.
Step 6: Edit data in database using same Insert form.
If submit the form after update value then data inserted into database.
Add following code on top in 'insert.php' file.
FILE NAME: insert.php
<?php
include("connect.php");
if(isset($_GET['cid']) && !empty($_GET['cid'])) {
$cid=$_GET['cid'];
$edit_query="select * from user_data where id='".$cid."'";
$result=mysqli_query($connection,$edit_query);
$row=mysqli_fetch_array($result);
}
?>
Explanation of above code:
- In above code we are checking if id is set and it is not empty.
- If both conditions are satisfied then we are executing query and stores it in variable $edit_query.
- By using mysqli_query, we will store data in variable $result. Now we fetch array from $result.
- Then add values in the HTML form. and we will also add hidden input which consists id, we will get 'cid' from the database in the listing page. This 'cid' value we will use for update process.
After this step we will update the code in insert_back.php file.
NOTE: Update code at below is in blue color.
FILE NAME: insert_back.php
<?php
include("connect.php");
if(isset($_POST['submit'])) {
$name=$_POST['name'];
$email=$_POST['email'];
if(empty($_POST['cid'])) {
$query="insert into user_data (name,email) values ('".$name."','".$email."')";
$msg="Insert successful";
}else {
$query="update user_data set name='".$name."',email='".$email."' where id='".$_POST['cid']."'";
$msg="Update successful";
}
$result=mysqli_query($connection,$query);
if($result) {
header("Location:index.php?msg=".$msg);
} else {
$msg="Problem with Insert";
header("Location:index.php?msg=".$msg);
}
}else {
$msg="Please enter the user data";
header("Location:index.php");
}
?>
Explanation of above code:
- We have checked “cid” hidden element.
- If “cid” hidden element is not empty then update query based on that id.
- After execute query and send message in index page with update data & message.
Step 7: Delete Records from Database using php.
We will create a page and named is delete.php file.
When we are clicking on delete link of index.php page, user will be redirected to delete.php file with id in URL. The script executes and data gets deleted.
FILE NAME: delete.php
<?php
include("connect.php");
if(isset($_GET['cid']) && !empty($_GET['cid'])) {
$query="DELETE FROM user_data where id='".$_GET['cid']."'";
$result= mysqli_query($connection,$query);
if($result){
$msg="Data deleted successfully";
header("Location:index.php?msg=".$msg);
}
}
?>
Explanation of above code:
- In above code execute query using mysqli_query php function.
- Store in $result variable. If the record is successfully deleted then we will redirect index.php page.