Write and Delete data in MySql with Jquery and PHP

This tutorial i will explain how to write and delete comments without having to load/refresh a new page after, something like Facebook. To achieve this we will use PHP, jQuery and MySql.

Create Database table

You can do this easy PHPMyAdmin, or you can use this code to execute commands in MySql , so whole table and fields will automatic create.

CREATE TABLE IF NOT EXISTS `commentstable` (
	  `id` INT(10) NOT NULL AUTO_INCREMENT,
	  `comment` text NOT NULL,
	  PRIMARY KEY (`id`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Create your index/page file.

It can be php or html file. We will include Google Jquery library, some JavaScript code and html structure.


1. Include Jquery

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>

2. Create HTML structure.

<body>
	<div id="main">
	<h3>What are you doing?</h3>
 
	<form method="post" name="form">
	<textarea name="comment" id="comment"></textarea>
	<input type="submit" id="submit" name="submit" value="Update" />
	</form>
 
 
 
	</div>
</body>

3. Include JavaScript code

$(function() {
	    $("#submit").click(function() { // If you click on button Submit
 
	        // var comtext get our value in comment textfield
	        var comtext = $("#comment").val();
 
	        if (comtext == '') { // if textfield is emptry
	            // We add red border and focus
 
$("#comment").css("border-color", "#C33").focus();
	        } else {
 
	            $.ajax({
	                type: "POST",
	                url: "update.php",
                data: 'comment='+ comtext,
                cache: false,
	                success: function(html) { // If is everything ok
 
	                    // We send our values to update.php
	                    $("#timeline").prepend(html);
	                    $("#timeline li:first").hide().slideDown();
$("#comment").val('').css("border-color", "#BBB");
	                }
	        });
	        }
	        return false;
	    });
 
	    // If you click on delete button
	    $('.delete').live("click", function() {
	        // We get id value for this comment (from delete button ID)
              var id = $(this).attr("id");
 
	        $.ajax({
	            type: "POST",
	            url: "update.php",
	            data: 'id=' + id,
	            cache: false,
	            success: function(html){
	                $(".k-" + id).slideUp(function() { $(this).remove(); });
	            }
	        });
	        return false;
	    });
 
	});

4. Finally create update.php file

<?php
 
	require_once('db.class.php' ); 
	$db = new DB('localhost', 'user', 'password', 'db_name');
 
	if (isset($_POST['comment'])) { 
 
 
	    $comment = htmlentities($_POST['comment'], ENT_QUOTES, 'UTF-8');
	    $comment = stripslashes($comment); 
	    $comment = nl2br($comment);
 
 
	    $db->execute("INSERT INTO commentstable SET comment = '$comment'");
 
 
	    $query = $db->query("SELECT id, comment FROM commentstable ORDER BY id DESC");
	    $row = $db->fetchObject($query);
	?>
	    <li class="k-<?php echo $row->id; ?>">
	    <span class="comment"><?php echo $row->comment; ?></span>
	    <span class="del"><a href="#" title="Delete" id="<?php echo $row->id; ?>" class="delete">X</a></span>
	    </li>
	<?php
	}
 
	if ($_POST['id']) { 
	    $id = (int)($_POST['id']);
	    $db->execute("DELETE FROM commentstable WHERE id = $id LIMIT 1");
	}
 
	?>

db.class.php file code.

<?php
class db {
 
	private $result;
 
 
	public function __construct($server, $user, $password, $db) {
		mysql_connect($server, $user, $password) or $this->debug('Error.');
		mysql_select_db($db) or $this->debug('Error.');
		mysql_query("SET NAMES 'utf8'");
    }
 
 
	public function query($query) {
		$this->result = mysql_query($query) or $this->debug();
		return $this->result;
	}
 
 
	public function execute($query) {
		mysql_query($query) or $this->debug();
	}
 
 
	public function fetchObject($query = '') {
		if (empty($query)) {
			return mysql_fetch_object($this->result);
		} else {
			return mysql_fetch_object($query);
		}
    }
 
 
	public function fetchArray($query = '') {
		if (empty($query)) {
			return mysql_fetch_array($this->result);
		} else {
			return mysql_fetch_array($query);
		}
	}
 
 
    public function numRows($query = '') {
		if (empty($query)) {
			return mysql_num_rows($this->result);
		} else {
			return mysql_num_rows($query);
		}
	}
 
 
	public function debug($error = '') {
		if (defined('DEBUG')) {
			if (empty($error)) { $error = mysql_error(); }
			die('<div><p><strong>Error</strong><span>' . $error . '</span></p></div>');
		}
	}
 
 
	public function free_result($query = '') {
		if (!empty($query)) {
			mysql_free_result($query);
			$this->result = '';
		}
	}
}
 
?>

You need to add some css styles

Tags: ,

5 Responses

  1. From baris

    hi there,

    What is there in db.class.php? I have noticed that you are including in your update.php

    • From Admin

      i make update to my post. Thanks anyway!

      • From lungie

        how to delete into the database using jquery

        • From Admin

          Well, ill tell you how that works, but try searching on google.

          1. You need to send parameters to some of your pages like delete.php. You can do that with JQuery. Easiest think is to post some query string like delete.php?id=5, then read query string in delete.php, delete your post and send back “Success msg”. Care about security, since anybody can just type yourdomain.com/delete.php?id-22

          :D

  2. From driedoezoe

    if i’m correct, the html misses a to output the submitted text + del button.

    Nice code, but the explanation is very minimal.

Leave a Reply

You must be logged in to post a comment.