PHP MySQL CRUD Application
Create, edit, update and delete content on a website makes a dynamic site. That's what we are going to learn here.
What is CRUD?
CRUD stands for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for database management system. In this article we are going to create a simple PHP application to perform all these operations with MySQL database table through single appication.
Well, let's begin by creating the database table named "jokes" table which we'll use in all of our example:
Create Table:
CREATE TABLE `jokes` (
`id` int(11) NOT NULL,
`joketext` text,
`jokedate` date NOT NULL
)
Set Primarykey and auto increment:
ALTER TABLE `jokes`
ADD PRIMARY KEY (`id`);
ALTER TABLE `jokes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
InsertForm.php
A simple form where we will insert our jokes with its added date.
<!doctype html>
<html>
<head>
<title>Insert Form </title>
</head>
<body>
<form action="insert.php" method="post">
Joke Text : <input type="text" name="joketext"> <br>
Joke Date : <input type="text" name="jokedate"><br>
<input type="submit" value="Submit Jokes" >
</form>
</body>
</html>
Insert.php
The core php code to insert the data from the form after submission of the form is done here. Here we create database connection, after database is successfully connected, then we take the values which are posted from insertform.php. Then we perform database insert operation. after successful insertation the page redirected to display.php and the conection is closed at the end.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname ="jokes";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$joketext=$_POST['joketext'];
$jokedate=$_POST['jokedate'];
// Create database
$sql = "insert into jokes(joketext,jokedate) values ('$joketext','$jokedate')";
if (mysqli_query($conn, $sql)) {
echo "Data insert successfully";
//header(locaton:"insertform.php");
header("location:display.php");
} else {
echo "Error creating database: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
display.php
The php code to display the data from the database are done here. Here we create database connection, after database is successfully connected, then we will display all the records sored in the database . Then we perform all the other database operation like, Insert operation, Update operation and Delete operation.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "jokes";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id,joketext,jokedate FROM jokes";
$result = mysqli_query($conn, $sql);
echo ("
<table border='1'>
<tr>
<th>id</th> <th>Joke Text</th> <th>jok Date</th> <th> </th> <th> </th>
</tr>
");
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>".$row["id"]."</td>"."<td>".$row["joketext"]."</td>". "<td>".$row["jokedate"]."</td>";
echo "<td>";
?>
<a href="delete.php?id=<?php echo $row['id']?>"> Delete</a>
<?php
echo "</td>";
echo "<td>";
?>
<a href="updateform.php?id=<?php echo $row['id']?>"> Edit</a>
<?php
echo "</td> </tr>";
}
echo ("
</table>
<a href='insertform.php'> Insert New Record</a>
");
} else {
echo "0 results";
}
mysqli_close($conn);
?>
updateform.php
when user click on the update button in Display.php it redirects here. Here we can edit the specified record according to its id and update the changes.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "jokes";
$jid=$_GET['id'];
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id,joketext,jokedate FROM jokes where id='$jid'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0)
// output data of each row
$row = mysqli_fetch_assoc($result);
else
echo "No Record Found";
?>
<form action="update.php" method="post">
Joke Text : <input type="text" name="joketext" value="<?php echo $row['joketext']?>"> <br>
Joke Date : <input type="text" name="jokedate" value="<?php echo $row['jokedate']?>"><br>
<input type="hidden" name="jid" value="<?php echo $row['id']?>"><br>
<input type="submit" value="Update"> </form>
<?php
mysqli_close($conn);
?>
update.php
core php logic to update record is done here.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "jokes";
$jid=$_POST['jid'];
$joketext=$_POST['joketext'];
$jokedate=$_POST['jokedate'];
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE jokes SET joketext='$joketext',jokedate='$jokedate' WHERE id=$jid";
if (mysqli_query($conn, $sql)) {
// echo "Record updated successfully";
header("location:display.php");
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
delete.php
when user click delete button on display.php it redirect to here. we can perform delete operation from here.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "jokes";
$jid=$_GET['id'];
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// sql to delete a record
$sql = "DELETE FROM jokes WHERE id='$jid'";
if (mysqli_query($conn, $sql)) {
// echo "Record deleted successfully";
header("location:display.php");
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Conclusion:
Thence, With the above procedure we can perform a simple crud operation on PHP. Hope this Article is useful anyone who are new to PHP.
