In this tutorial, we create a basic commenting system consisting of a form with two input fields: name and comment. A user can add a comment on the page without the page reloading (using Ajax) and this comment is stored in the comments table in the database.
All comments in the database are displayed on the page and the user can delete a comment by simply hovering over it and clicking on the 'x' button that appears on the comment. This deletes the comment without reloading the page also.
This application requires only one database table. In this case, let's just call the table comments.
So, create a database called ajax_crud. In that database, create a table with the following fields in it:
- id - int(11)
- name - varchar(255)
- comment - text
Next, we create the files of the project. We are going to create four files namely:
- An index.php file which displays the comments and the commenting from
- A server.php which contains the PHP code responsible for communicating with the database through queries
- A scripst.js file which is where all Ajax and JQuery code is found. This is where the ajax calls that are done without page reload are made.
- A styles.css file to hold the CSS styles to add some beauty to our page
Now before we move on, there's one more file we need to add. Ajax is a JQuery technology and so as you may have already guessed, we are going to need JQuery. Download JQuery from the JQuery website and add it in the root of your application.
Okay, so now in our application we have five files. They're still empty, though.
Let's code now, shall we!
Open the index.php file and place this code in it:
<?php include('server.php'); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Insert and Retrieve data from MySQL database with ajax</title>
<link rel="stylesheet" href="styles.css">
</head>
<body>
<div class="wrapper">
<?php echo $comments; ?>
<form class="comment_form">
<div>
<label for="name">Name:</label>
<input type="text" name="name" id="name">
</div>
<div>
<label for="comment">Comment:</label>
<textarea name="comment" id="comment" cols="30" rows="5"></textarea>
</div>
<button type="button" id="submit_btn">POST</button>
<button type="button" id="update_btn" style="display: none;">UPDATE</button>
</form>
</div>
</body>
</html>
<!-- Add JQuery -->
<script src="jquery.min.js"></script>
<script src="scripts.js"></script>
Nothing out of the ordinary right?
Just take note that we have included links to our styles.css, jquery.min.js (which was downloaded) and our scripts.js file.
Now open the styles.css file and paste this CSS code in it:
.wrapper {
width: 45%;
height: auto;
margin: 10px auto;
border: 1px solid #cbcbcb;
background: white;
}
/*
* COMMENT FORM
**/
.comment_form {
width: 80%;
margin: 100px auto;
border: 1px solid green;
background: #fafcfc;
padding: 20px;
}
.comment_form label {
display: block;
margin: 5px 0px 5px 0px;
}
.comment_form input, textarea {
padding: 5px;
width: 95%;
}
#submit_btn, #update_btn {
padding: 8px 15px;
color: white;
background: #339;
border: none;
border-radius: 4px;
margin-top: 10px;
}
#update_btn {
background: #1c7600;
}
/*
* COMMENT DISPLAY AREA
**/
#display_area {
width: 90%;
padding-top: 15px;
margin: 10px auto;
}
.comment_box {
cursor: default;
margin: 5px;
border: 1px solid #cbcbcb;
padding: 5px 10px;
position: relative;
}
.delete {
position: absolute;
top: 0px;
right: 3px;
color: red;
display: none;
cursor: pointer;
}
.edit {
position: absolute;
top: 0px;
right: 45px;
color: green;
display: none;
cursor: pointer;
}
.comment_box:hover .edit, .comment_box:hover .delete {
display: block;
}
.comment_text {
text-align: justify;
}
.display_name {
color: blue;
padding: 0px;
margin: 0px 0px 5px 0px;
}
That's it for the CSS. Now the scripts.
vli_adOpen scripts.js and paste this code inside it:
$(document).ready(function(){
// save comment to database
$(document).on('click', '#submit_btn', function(){
var name = $('#name').val();
var comment = $('#comment').val();
$.ajax({
url: 'server.php',
type: 'POST',
data: {
'save': 1,
'name': name,
'comment': comment,
},
success: function(response){
$('#name').val('');
$('#comment').val('');
$('#display_area').append(response);
}
});
});
// delete from database
$(document).on('click', '.delete', function(){
var id = $(this).data('id');
$clicked_btn = $(this);
$.ajax({
url: 'server.php',
type: 'GET',
data: {
'delete': 1,
'id': id,
},
success: function(response){
// remove the deleted comment
$clicked_btn.parent().remove();
$('#name').val('');
$('#comment').val('');
}
});
});
var edit_id;
var $edit_comment;
$(document).on('click', '.edit', function(){
edit_id = $(this).data('id');
$edit_comment = $(this).parent();
// grab the comment to be editted
var name = $(this).siblings('.display_name').text();
var comment = $(this).siblings('.comment_text').text();
// place comment in form
$('#name').val(name);
$('#comment').val(comment);
$('#submit_btn').hide();
$('#update_btn').show();
});
$(document).on('click', '#update_btn', function(){
var id = edit_id;
var name = $('#name').val();
var comment = $('#comment').val();
$.ajax({
url: 'server.php',
type: 'POST',
data: {
'update': 1,
'id': id,
'name': name,
'comment': comment,
},
success: function(response){
$('#name').val('');
$('#comment').val('');
$('#submit_btn').show();
$('#update_btn').hide();
$edit_comment.replaceWith(response);
}
});
});
});
The code in scripst.js above as earlier mentioned is the ajax calls that are made to the server.php file. The first piece of script sends a request with data to the server when a user clicks on the post button. The second sends a request having a comment_id so as to identify the specific comment in the database by that id and delete it.
vli_adFor the server code, open server.php and paste the following code in it:
<?php
$conn = mysqli_connect('localhost', 'root', '', 'ajax');
if (!$conn) {
die('Connection failed ' . mysqli_error($conn));
}
if (isset($_POST['save'])) {
$name = $_POST['name'];
$comment = $_POST['comment'];
$sql = "INSERT INTO comments (name, comment) VALUES ('{$name}', '{$comment}')";
if (mysqli_query($conn, $sql)) {
$id = mysqli_insert_id($conn);
$saved_comment = '<div class="comment_box">
<span class="delete" data-id="' . $id . '" >delete</span>
<span class="edit" data-id="' . $id . '">edit</span>
<div class="display_name">'. $name .'</div>
<div class="comment_text">'. $comment .'</div>
</div>';
echo $saved_comment;
}else {
echo "Error: ". mysqli_error($conn);
}
exit();
}
// delete comment fromd database
if (isset($_GET['delete'])) {
$id = $_GET['id'];
$sql = "DELETE FROM comments WHERE id=" . $id;
mysqli_query($conn, $sql);
exit();
}
if (isset($_POST['update'])) {
$id = $_POST['id'];
$name = $_POST['name'];
$comment = $_POST['comment'];
$sql = "UPDATE comments SET name='{$name}', comment='{$comment}' WHERE id=".$id;
if (mysqli_query($conn, $sql)) {
$id = mysqli_insert_id($conn);
$saved_comment = '<div class="comment_box">
<span class="delete" data-id="' . $id . '" >delete</span>
<span class="edit" data-id="' . $id . '">edit</span>
<div class="display_name">'. $name .'</div>
<div class="comment_text">'. $comment .'</div>
</div>';
echo $saved_comment;
}else {
echo "Error: ". mysqli_error($conn);
}
exit();
}
// Retrieve comments from database
$sql = "SELECT * FROM comments";
$result = mysqli_query($conn, $sql);
$comments = '<div id="display_area">';
while ($row = mysqli_fetch_array($result)) {
$comments .= '<div class="comment_box">
<span class="delete" data-id="' . $row['id'] . '" >delete</span>
<span class="edit" data-id="' . $row['id'] . '">edit</span>
<div class="display_name">'. $row['name'] .'</div>
<div class="comment_text">'. $row['comment'] .'</div>
</div>';
}
$comments .= '</div>';
?>
This brings us to the end of this tutorial.
Thank you for following this post. I hope it helped you. If you have any worries or comments, just drop them in the comments section below.
Enjoy your day!
