Simple Items Listing with PHP, MySQL, jQuery, jQuery UI, Ajax & CSS3
Simple Ajax based item list with PHP, MySQL and jQuery. In this process, added PHP OOPS capability with jQuery UI and implemented jQuery Ajax functionality.
It is very user friendly. Any one can create, edit, delete and reorder the items list.
Demo Code
Part 1 – PHP
Step 1 – todo.class.php
<?php
/* Defining the ToDo class */
class ToDo{
/* An array that stores the todo item data: */
private $data;
/* The constructor */
public function __construct($par){
if(is_array($par))
$this->data = $par;
}
/*
This is an in-build "magic" method that is automatically called
by PHP when we output the ToDo objects with echo.
*/
public function __toString(){
// The string we return is outputted by the echo statement
return '
<li id="todo-'.$this->data['id'].'" class="todo">
<div class="text">'.$this->data['text'].'</div>
<div class="actions">
<a href="#" class="edit">Edit</a>
<a href="#" class="delete">Delete</a>
</div>
</li>';
}
/*
The following are static methods. These are available
directly, without the need of creating an object.
*/
/*
The edit method takes the ToDo item id and the new text
of the ToDo. Updates the database.
*/
public static function edit($id, $text){
$text = self::esc($text);
if(!$text) throw new Exception("Wrong update text!");
mysql_query(" UPDATE tg_todo
SET text='".$text."'
WHERE id=".$id
);
if(mysql_affected_rows($GLOBALS['link'])!=1)
throw new Exception("Couldn't update item!");
}
/*
The delete method. Takes the id of the ToDo item
and deletes it from the database.
*/
public static function delete($id){
mysql_query("DELETE FROM tg_todo WHERE id=".$id);
if(mysql_affected_rows($GLOBALS['link'])!=1)
throw new Exception("Couldn't delete item!");
}
/*
The rearrange method is called when the ordering of
the todos is changed. Takes an array parameter, which
contains the ids of the todos in the new order.
*/
public static function rearrange($key_value){
$updateVals = array();
foreach($key_value as $k=>$v)
{
$strVals[] = 'WHEN '.(int)$v.' THEN '.((int)$k+1).PHP_EOL;
}
if(!$strVals) throw new Exception("No data!");
// We are using the CASE SQL operator to update the ToDo positions en masse:
mysql_query(" UPDATE tg_todo SET position = CASE id
".join($strVals)."
ELSE position
END");
if(mysql_error($GLOBALS['link']))
throw new Exception("Error updating positions!");
}
/*
The createNew method takes only the text of the todo,
writes to the databse and outputs the new todo back to
the AJAX front-end.
*/
public static function createNew($text){
$text = self::esc($text);
if(!$text) throw new Exception("Wrong input data!");
$posResult = mysql_query("SELECT MAX(position)+1 FROM tg_todo");
if(mysql_num_rows($posResult))
list($position) = mysql_fetch_array($posResult);
if(!$position) $position = 1;
mysql_query("INSERT INTO tg_todo SET text='".$text."', position = ".$position);
if(mysql_affected_rows($GLOBALS['link'])!=1)
throw new Exception("Error inserting TODO!");
// Creating a new ToDo and outputting it directly:
echo (new ToDo(array(
'id' => mysql_insert_id($GLOBALS['link']),
'text' => $text
)));
exit;
}
/*
A helper method to sanitize a string:
*/
public static function esc($str){
if(ini_get('magic_quotes_gpc'))
$str = stripslashes($str);
return mysql_real_escape_string(strip_tags($str));
}
} // closing the class definition
?>
Step 2 – demo.php
<?php
require "connect.php";
require "todo.class.php";
// Select all the todos, ordered by position:
$query = mysql_query("SELECT * FROM `tg_todo` ORDER BY `position` ASC");
$todos = array();
// Filling the $todos array with new ToDo objects:
while($row = mysql_fetch_assoc($query)){
$todos[] = new ToDo($row);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Ajax with PHP, MySQL & jQuery | Techguru</title>
<!-- Including the jQuery UI Human Theme -->
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.0/themes/base/jquery-ui.css" type="text/css" media="all" />
<!-- Our own stylesheet -->
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<h1>Ajax List with PHP, MySQL & jQuery</h1>
<div id="main">
<ul class="todoList">
<?php
// Looping and outputting the $todos array. The __toString() method
// is used internally to convert the objects to strings:
foreach($todos as $item){
echo $item;
}
?>
</ul>
<a id="addButton" class="green-button" href="#">Add New</a>
</div>
<!-- This div is used as the base for the confirmation jQuery UI POPUP. Hidden by CSS. -->
<div id="dialog-confirm" title="Delete Item?">Are you sure you want to delete this item?</div>
<!-- Including our scripts -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.0/jquery-ui.min.js"></script>
<script type="text/javascript" src="script.js"></script>
</body>
</html>
Step 3 – ajax.php
<?php
require "connect.php";
require "todo.class.php";
$id = (int)$_GET['id'];
try{
switch($_GET['action'])
{
case 'delete':
ToDo::delete($id);
break;
case 'rearrange':
ToDo::rearrange($_GET['positions']);
break;
case 'edit':
ToDo::edit($id,$_GET['text']);
break;
case 'new':
ToDo::createNew($_GET['text']);
break;
}
}
catch(Exception $e){
// echo $e->getMessage();
die("0");
}
echo "1";
?>
Step 4 – connect.php
<?php
/* Database config */
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_database = 'techguru';
/* End config */
$link = @mysql_connect($db_host,$db_user,$db_pass) or die('Unable to establish a DB connection');
mysql_set_charset('utf8');
mysql_select_db($db_database,$link);
?>
Part 2 – MySQL
Step 1 – tg_todo.sql
CREATE TABLE IF NOT EXISTS `tg_todo` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `position` int(8) unsigned NOT NULL DEFAULT '0', `text` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `dt_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `position` (`position`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
Part 3 – CSS
Step 1 – styles.css
*{
/* Resetting the default styles of the page */
margin:0;
padding:0;
}
body{
/* Setting default text color, background and a font stack */
font-size:0.825em;
color:#666;
background-color:#fff;
font-family:Arial, Helvetica, sans-serif;
}
/* The todo items are grouped into an UL unordered list */
ul.todoList{
margin:0 auto;
width:500px;
position:relative;
}
ul.todoList li{
background-color:#F9F9F9;
border:1px solid #EEEEEE;
list-style:none;
margin:6px;
padding:6px 9px;
position:relative;
cursor:n-resize;
/* CSS3 text shadow and rounded corners: */
text-shadow:1px 1px 0 white;
-moz-border-radius:6px;
-webkit-border-radius:6px;
border-radius:6px;
}
ul.todoList li:hover{
border-color:#9be0f9;
/* CSS3 glow effect: */
-moz-box-shadow:0 0 5px #A6E5FD;
-webkit-box-shadow:0 0 5px #A6E5FD;
box-shadow:0 0 5px #A6E5FD;
}
.todo .text{
color:#777777;
font-size:1.4em;
}
/* The edit and delete buttons */
.todo .actions{
position:absolute;
right:7px;
top:6px;
}
.todo .actions a{
display:block;
width:16px;
height:16px;
overflow:hidden;
float:left;
text-indent:-9999px;
margin:3px;
}
.todo .actions a.edit{
background:url("http://www.techgurulive.info/wp-content/uploads/2014/08/tg_edit.png") no-repeat center center;
}
.todo .actions a.delete{
background:url("http://www.techgurulive.info/wp-content/uploads/2014/08/tg_delete.png") no-repeat center center;
}
/* The edit textbox */
.todo input{
border:1px solid #CCCCCC;
color:#666666;
font-family:Arial,Helvetica,sans-serif;
font-size:0.725em;
padding:3px 4px;
width:300px;
}
/* The Save and Cancel edit links: */
.editTodo{
display:inline;
font-size:0.6em;
padding-left:9px;
}
.editTodo a{
font-weight:bold;
}
a.discardChanges{
color:#C00 !important;
}
a.saveChanges{
color:#4DB209 !important;
}
/* Overwriting some of the default jQuery UI styles */
.ui-button,.ui-dialog-titlebar{
font-size:0.72em !important;
}
#dialog-confirm{
display:none;
font-size:0.9em;
padding:1em 1em 0;
}
#addButton{
margin:20px auto;
}
/* Green button class: */
a.green-button,
a.green-button:visited{
color:black;
display:block;
font-size:10px;
font-weight:bold;
height:15px;
padding:6px 5px 4px;
text-align:center;
width:60px;
text-shadow:1px 1px 1px #DDDDDD;
background:url("http://www.techgurulive.info/wp-content/uploads/2014/08/tg_button_green.png") no-repeat left top;
}
a.green-button:hover{
text-decoration:none;
background-position:left bottom;
}
/* The styles below are only necessary for the styling of the demo page: */
#main{
position:relative;
margin:0 auto;
width:960px;
}
h1{
padding:30px 0;
text-align:center;
text-shadow:0 1px 1px white;
margin-bottom:30px;
background-color:#f8f8f8;
font-size:26px;
}
h1,h2{
font-family:"Myriad Pro",Arial,Helvetica,sans-serif;
}
h2{
font-size:14px;
font-weight:normal;
text-align:center;
position:absolute;
right:40px;
top:40px;
}
.note{
font-size:12px;
font-style:italic;
padding-bottom:20px;
text-align:center;
}
a, a:visited {
color:#0196e3;
text-decoration:none;
outline:none;
}
a:hover{
text-decoration:underline;
}
a img{
border:none;
}
Part 4 – jQuery
Step 1 – script.js
$(document).ready(function(){
/* The following code is executed once the DOM is loaded */
$(".todoList").sortable({
axis : 'y', // Only vertical movements allowed
containment : 'window', // Constrained by the window
update : function(){ // The function is called after the todos are rearranged
// The toArray method returns an array with the ids of the todos
var arr = $(".todoList").sortable('toArray');
// Striping the todo- prefix of the ids:
arr = $.map(arr,function(val,key){
return val.replace('todo-','');
});
// Saving with AJAX
$.get('ajax.php',{action:'rearrange',positions:arr});
},
/* Opera fix: */
stop: function(e,ui) {
ui.item.css({'top':'0','left':'0'});
}
});
// A global variable, holding a jQuery object
// containing the current todo item:
var currentTODO;
// Configuring the delete confirmation dialog
$("#dialog-confirm").dialog({
resizable: false,
height:130,
modal: true,
autoOpen:false,
buttons: {
'Delete item': function() {
$.get("ajax.php",{"action":"delete","id":currentTODO.data('id')},function(msg){
currentTODO.fadeOut('fast');
})
$(this).dialog('close');
},
Cancel: function() {
$(this).dialog('close');
}
}
});
// When a double click occurs, just simulate a click on the edit button:
$('.todo').live('dblclick',function(){
$(this).find('a.edit').click();
});
// If any link in the todo is clicked, assign
// the todo item to the currentTODO variable for later use.
$('.todo a').live('click',function(e){
currentTODO = $(this).closest('.todo');
currentTODO.data('id',currentTODO.attr('id').replace('todo-',''));
e.preventDefault();
});
// Listening for a click on a delete button:
$('.todo a.delete').live('click',function(){
$("#dialog-confirm").dialog('open');
});
// Listening for a click on a edit button
$('.todo a.edit').live('click',function(){
var container = currentTODO.find('.text');
if(!currentTODO.data('origText'))
{
// Saving the current value of the ToDo so we can
// restore it later if the user discards the changes:
currentTODO.data('origText',container.text());
}
else
{
// This will block the edit button if the edit box is already open:
return false;
}
$('<input type="text">').val(container.text()).appendTo(container.empty());
// Appending the save and cancel links:
container.append(
'<div class="editTodo">'+
'<a class="saveChanges" href="#">Save</a> or <a class="discardChanges" href="#">Cancel</a>'+
'</div>'
);
});
// The cancel edit link:
$('.todo a.discardChanges').live('click',function(){
currentTODO.find('.text')
.text(currentTODO.data('origText'))
.end()
.removeData('origText');
});
// The save changes link:
$('.todo a.saveChanges').live('click',function(){
var text = currentTODO.find("input[type=text]").val();
$.get("ajax.php",{'action':'edit','id':currentTODO.data('id'),'text':text});
currentTODO.removeData('origText')
.find(".text")
.text(text);
});
// The Add New ToDo button:
var timestamp=0;
$('#addButton').click(function(e){
// Only one todo per 5 seconds is allowed:
if((new Date()).getTime() - timestamp<5000) return false;
$.get("ajax.php",{'action':'new','text':'TechGuru - New Item Added. Doubleclick to Edit.','rand':Math.random()},function(msg){
// Appending the new todo and fading it into view:
$(msg).hide().appendTo('.todoList').fadeIn();
});
// Updating the timestamp:
timestamp = (new Date()).getTime();
e.preventDefault();
});
}); // Closing $(document).ready()
Leave a Reply