PHP CRUD Create, edit, update and delete posts with MySQL database

PHP CRUD Create, edit, update and delete posts with MySQL database. Creating, editing, updating and deleting content on a website is what makes the site dynamic.

  PHP   Oct 8, 2022   2  Add to Reading List
PHP CRUD Create, edit, update and delete posts with MySQL database

The content that is created, edited, updated, and deleted on a website is what makes it dynamic. In this post, we will carry out that action. 

Before you start reading this post you should have a basic idea about PHP.

Our website will allow visitors to create posts, store them in a MySQL database, retrieve them from the database, and display them on the page. The user will be able to edit and delete each post by clicking edit or delete button on each post.

First, let us create a database with the name crud. In this database, let us create a table called info. The info table should have the following columns:

  • id - int(11)

  • name - varchar(100)

  • address - varchar(100)

Yes, we will be using only two fields here. I'm trying to keep things simple here. so, move on to the next step.

 

Create a file called index.php and copy & paste in it the following code:

<!DOCTYPE html>
<html>
<head>
	<title>CRUD: CReate, Update, Delete PHP MySQL</title>
</head>
<body>
	<form method="post" action="server.php" >
		<div class="input-group">
			<label>Name</label>
			<input type="text" name="name" value="">
		</div>
		<div class="input-group">
			<label>Address</label>
			<input type="text" name="address" value="">
		</div>
		<div class="input-group">
			<button class="btn" type="submit" name="save" >Save</button>
		</div>
	</form>
</body>
</html>

If you save and open the site on your browser, you get something like this:

                              

it doesn't look like the best form in the world, right? Let's fix that. copy this below line to your file, directly below the <title> tag in the head section of your index.php file you just created:

<link rel="stylesheet" type="text/css" href="style.css">

It is the link to load styling from the stylesheet file. Let's create the styles.css file and add this styling code to it.

body {
    font-size: 19px;
}
table{
    width: 50%;
    margin: 30px auto;
    border-collapse: collapse;
    text-align: left;
}
tr {
    border-bottom: 1px solid #cbcbcb;
}
th, td{
    border: none;
    height: 30px;
    padding: 2px;
}
tr:hover {
    background: #F5F5F5;
}

form {
    width: 45%;
    margin: 50px auto;
    text-align: left;
    padding: 20px; 
    border: 1px solid #bbbbbb; 
    border-radius: 5px;
}

.input-group {
    margin: 10px 0px 10px 0px;
}
.input-group label {
    display: block;
    text-align: left;
    margin: 3px;
}
.input-group input {
    height: 30px;
    width: 93%;
    padding: 5px 10px;
    font-size: 16px;
    border-radius: 5px;
    border: 1px solid gray;
}
.btn {
    padding: 10px;
    font-size: 15px;
    color: white;
    background: #5F9EA0;
    border: none;
    border-radius: 5px;
}
.edit_btn {
    text-decoration: none;
    padding: 2px 5px;
    background: #2E8B57;
    color: white;
    border-radius: 3px;
}

.del_btn {
    text-decoration: none;
    padding: 2px 5px;
    color: white;
    border-radius: 3px;
    background: #800000;
}
.msg {
    margin: 30px auto; 
    padding: 10px; 
    border-radius: 5px; 
    color: #3c763d; 
    background: #dff0d8; 
    border: 1px solid #3c763d;
    width: 50%;
    text-align: center;
}

Now let's check our form out in the browser again:

Now it looks better! 

It is best practice to separate my HTML code from my PHP code as much as possible. It helps code more readble. On that note, let us create another file called php_code.php where we implement all php functionalities like connecting to the database, query the database and the like.

So open php_code.php and paste the following code in it:

<?php 
	session_start();
	$db = mysqli_connect('localhost', 'root', '', 'crud');

	// initialize variables
	$name = "";
	$address = "";
	$id = 0;
	$update = false;

	if (isset($_POST['save'])) {
		$name = $_POST['name'];
		$address = $_POST['address'];

		mysqli_query($db, "INSERT INTO info (name, address) VALUES ('$name', '$address')"); 
		$_SESSION['message'] = "Address saved"; 
		header('location: index.php');
	}

// ...

Now include this file at the top (the very first line) of your index.php file. Like so:

<?php  include('server.php'); ?>

At this point, all that this code does is connect to the database, initialize some variables and saves submitted data from the form to the database in the info we created earlier. That's only the CReate part of CRUD. Let's proceed with the others.

Now visit again your index.php file and add this code right under the <body> tag:

// ...
<body>
<?php if (isset($_SESSION['message'])): ?>
	<div class="msg">
		<?php 
			echo $_SESSION['message']; 
			unset($_SESSION['message']);
		?>
	</div>
<?php endif ?>

This code displays a confirmation message to tell the user that a new record has been created in the database. 

To retrieve the database records and display them on the page, add this code immediately above the input form:

<?php $results = mysqli_query($db, "SELECT * FROM info"); ?>

<table>
	<thead>
		<tr>
			<th>Name</th>
			<th>Address</th>
			<th colspan="2">Action</th>
		</tr>
	</thead>
	
	<?php while ($row = mysqli_fetch_array($results)) { ?>
		<tr>
			<td><?php echo $row['name']; ?></td>
			<td><?php echo $row['address']; ?></td>
			<td>
				<a href="index.php?edit=<?php echo $row['id']; ?>" class="edit_btn" >Edit</a>
			</td>
			<td>
				<a href="server.php?del=<?php echo $row['id']; ?>" class="del_btn">Delete</a>
			</td>
		</tr>
	<?php } ?>
</table>

<form>
    // ...

Let's create a new record and see if this stuff works:

..and Wao!! It is working perfectly !

Now we move on to editing. At the top of your index.php file (immediately after the include statement) add the following code:

<?php 
	if (isset($_GET['edit'])) {
		$id = $_GET['edit'];
		$update = true;
		$record = mysqli_query($db, "SELECT * FROM info WHERE id=$id");

		if (count($record) == 1 ) {
			$n = mysqli_fetch_array($record);
			$name = $n['name'];
			$address = $n['address'];
		}
	}
?>

When editing a database record, we need to put the old values in the form so that they can be modified. To do so, let's modify our input fields on the form and set those values taken from the database ($name, $address) as values to the value attribute of the form fields.

Also, add a hidden field to hold the id of the record we will be updating so that it can be recognized in the database uniquely by it's id. This explains it better:

// newly added field
<input type="hidden" name="id" value="<?php echo $id; ?>">

// modified form fields
<input type="text" name="name" value="<?php echo $name; ?>">
<input type="text" name="address" value="<?php echo $address; ?>">

Remember all of that is in the input <form>.

Now if we click on the edit button on a particular record from the database, the values will be filled in the form and we will be able to edit them. Since we are editing on the same form as when we are creating, we have to put a condition that determines the appropriate button to be displayed. For example, when editing, we display the update button on the form and when creating, we display the save button. We do this using the update variable which is boolean. When update is true, the update button is displayed and if false, the save button is displayed.

Replace your save button on the form like this:

Replace ..

<button class="btn" type="submit" name="save" >Save</button>

 with...

<?php if ($update == true): ?>
	<button class="btn" type="submit" name="update" style="background: #556B2F;" >update</button>
<?php else: ?>
	<button class="btn" type="submit" name="save" >Save</button>
<?php endif ?>

Now if we run this on the browser and click the edit button, we get this:

Now you can see it is the update button that is displayed. Let's add the code that will be executed when this button is clicked.

Open php_code.php file and add this code at the button:

// ... 

if (isset($_POST['update'])) {
	$id = $_POST['id'];
	$name = $_POST['name'];
	$address = $_POST['address'];

	mysqli_query($db, "UPDATE info SET name='$name', address='$address' WHERE id=$id");
	$_SESSION['message'] = "Address updated!"; 
	header('location: index.php');
}

Now change the values in the form and click the update button. 

Great!

One last thing: deleting records. Just add this code at the end of you php_code.php file and you're good to go:

if (isset($_GET['del'])) {
	$id = $_GET['del'];
	mysqli_query($db, "DELETE FROM info WHERE id=$id");
	$_SESSION['message'] = "Address deleted!"; 
	header('location: index.php');
}

If you click the delete button, it deletes the record from the database and displays the message just like the other actions.

Conclusion

This tutorial has come to an end with this. I hope you found it useful and well-worth your time. It means a lot to me that you were patient and followed through with this. Share this tutorial with your friends by clicking on any of the icons below if you enjoyed it. Check out my other tutorials on this website as well.

Thank you so much for being patient.

What's Your Reaction?

like

dislike

love

funny

angry

sad

wow