Sunday, April 3, 2011

9:51 AM
I earlier posted about Collapsible Drag and Drop Panels using jQuery where many of you asked about how to save the state of panels and retrieve them when user loads the page again. Here’s the complete solution to that problem using MySQL database to store the state of panels. 


Also, there were some browser dependent problems which i have tried to remove, let me know if you still encounter any problem implementing.

Creating the Database

The database table widgets consists of 5 columns:



  • id stores the id of panel.
  • column_id is the column number to which panel belongs.
  • sort_no is the order of panel within column.
  • collapsed stores information about whether the panel is collapsed or not.
  • title is the title of the widget.

You can of course add more columns to this database but first four are critical to working of the script. You can add more columns like RSS feed URL that you want to display in that panel or its content etc. To keep things simple and easy to understand, i have skipped that.



Here’s the SQL code:



CREATE TABLE IF NOT EXISTS `widgets` (
  `id` int(11) NOT NULL auto_increment,
  `column_id` int(11) NOT NULL,
  `sort_no` int(11) NOT NULL,
  `collapsed` tinyint(4) NOT NULL,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `widgets`
--

INSERT INTO `widgets` (`id`, `column_id`, `sort_no`, `collapsed`, `title`) VALUES
(1, 2, 0, 1, 'Widget 1'),
(2, 1, 0, 0, 'Widget 2'),
(3, 2, 1, 0, 'Widget 3'),
(4, 2, 2, 0, 'Widget 4'),
(5, 1, 1, 1, 'Widget 5');


As you can see, I have also added some default data to widgets table, but if you are using this in production environment, you will have to create an interface for adding new panels which will add new rows to widgets table.


Displaying Panels Using Database

Since I added some default data to widgets table, here’s how to display the widgets keeping their saved state intact.


Remember to update the Database configuration detail in config.php file.

This code is within index.php file.


<div id="console" ></div>

<?php
$dummy="Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec vestibulum velit ultricies orci pharetra elementum. In massa mauris, varius sed tempus a, iaculis sed erat. Ut sollicitudin tellus mollis arcu laoreet semper. Suspendisse ut felis odio. Aliquam auctor, tortor sit amet suscipit elementum, nunc ante dictum lectus, ac accumsan justo nunc sed velit. Sed sollicitudin varius tortor vitae varius..";

include('./config.php');

$columns=mysql_query('SELECT DISTINCT column_id FROM widgets ORDER BY column_id');
while($column=mysql_fetch_array($columns))
{
echo '<div class="column" id="column'.$column['column_id'].'" >';
$items=mysql_query("SELECT * FROM widgets WHERE column_id='".$column['column_id']."' ORDER BY sort_no");
while($widget=mysql_fetch_array($items))
{
echo '
<div class="dragbox" id="item'.$widget['id'].'">
<h2>'.$widget['title'].'</h2>
<div class="dragbox-content" ';
if($widget['collapsed']==1)
echo 'style="display:none;" ';
echo '>
'.substr($dummy, 0, rand(120, strlen($dummy))).'
</div>
</div>';
}
echo '</div>';
}
?>



Explanation 

First of all, fetch the unique column_id‘s from widgets table and then for each column, their panels in the sort order and then display them.

I’m using a variable $dummy to produce dummy content for widgets.


The JavaScript Code

In earlier article, i wrote about a variable sortorder that was used to pass information to server side script for processing. But it only stored information about order of panels and their column but not the state of each panel i.e. whether panel is collapsed or not was not passed to server side script.

So, I worked out the JavaScript code so that it saves all the information about state of each panel that is its column, its order in the column and its collapse state.

For that I have used an array of JavaScript objects with each object storing information about each panel.



And to pass the array of objects, I used  jQuery JSON plugin (mere 2KB) that will make it easy to pass data to server side script in JSON format.

Moreover, since state needs to be saved not only when panels are changed by dragging but also when a panel is collapsed or opened. That is why I created a function updateWidgetData(that will be called whenever the state needs to be saved.

Here’s the JavaScript code, it resides within <head> tag inside index.php file.


$(function(){
$('.dragbox')
.each(function(){
$(this).hover(function(){
$(this).find('h2').addClass('collapse');
}, function(){
$(this).find('h2').removeClass('collapse');
})
.find('h2').hover(function(){
$(this).find('.configure').css('visibility', 'visible');
}, function(){
$(this).find('.configure').css('visibility', 'hidden');
})
.click(function(){
$(this).siblings('.dragbox-content').toggle();
//Save state on change of collapse state of panel
updateWidgetData();
})
.end()
.find('.configure').css('visibility', 'hidden');
});

$('.column').sortable({
connectWith: '.column',
handle: 'h2',
cursor: 'move',
placeholder: 'placeholder',
forcePlaceholderSize: true,
opacity: 0.4,
start: function(event, ui){
//Firefox, Safari/Chrome fire click event after drag is complete, fix for that
if($.browser.mozilla || $.browser.safari)
$(ui.item).find('.dragbox-content').toggle();
},
stop: function(event, ui){
ui.item.css({'top':'0','left':'0'}); //Opera fix
if(!$.browser.mozilla && !$.browser.safari)
updateWidgetData();
}
})
.disableSelection();
});

function updateWidgetData(){
var items=[];
$('.column').each(function(){
var columnId=$(this).attr('id');
$('.dragbox', this).each(function(i){
var collapsed=0;
if($(this).find('.dragbox-content').css('display')=="none")
collapsed=1;
//Create Item object for current panel
var item={
id: $(this).attr('id'),
collapsed: collapsed,
order : i,
column: columnId
};
//Push item object into items array
items.push(item);
});
});
//Assign items array to sortorder JSON variable
var sortorder={ items: items };

//Pass sortorder variable to server using ajax to save state
$.post('updatePanels.php', 'data='+$.toJSON(sortorder), function(response){
if(response=="success")
$("#console").html('<div class="success">Saved</div>').hide().fadeIn(1000);
setTimeout(function(){
$('#console').fadeOut(1000);
}, 2000);
});
}

There were some browser inconsistencies like Firefox/Safari/Chrome fire click event on drag completion while IE/Opera do not fire which leads to inconsistent state which i have fixed as mentioned within the code.

PHP Code To Save State


In the above JavaScript code, I used updatePanels.php in the AJAX call to save state.

Here’s the code for updatePanels.php



<?php
if(!$_POST["data"]){
echo "Invalid data";
exit;
}
//Include DB config file
include('./config.php');

//decode JSON data received from AJAX POST request
$data=json_decode($_POST["data"]);

foreach($data->items as $item)
{
//Extract column number for panel
$col_id=preg_replace('/[^\d\s]/', '', $item->column);
//Extract id of the panel
$widget_id=preg_replace('/[^\d\s]/', '', $item->id);
$sql="UPDATE widgets SET column_id='$col_id', sort_no='".$item->order."', collapsed='".$item->collapsed."' WHERE id='".$widget_id."'";
mysql_query($sql) or die('Error updating widget DB');
}
echo "success";

?>


The code is pretty self explanatory, just decode the JSON string received from AJAX call and loop through items to save their state to database.

0 comments: