Saving State For Collapsible Drag Drop Panels

Posted in jQuery

Collapsible Drag Drop Panels

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.

You can download the source code and try it for yourself.

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.

Download the source code

Hopefully this will help solve most of the problems many of you were encountering. Special thanks to Mark Mckee for inspiring me to code the full solution.

Tags:

Share


Subscribe to Full RSS Feed

RSS FeedIf you found this article useful, then consider subscribing to our RSS Feed or e-mail updates to stay updated with latest Web Design/ Development articles. You can also follow @webdevplus on twitter for latest updates.

37 Responses (Add Your Comment)

  1. mark says:

    Hi there

    I have updated everything I have so I can use your new code but I am getting an invalid argument supplied for foreach() on this line

    foreach($data->items as $item)

    My php version is the latest php5 5.2.6.

    can you see where the problem is?

    Cheers
    Mark

  2. mark says:

    I forgot to add. Thank you for updating your tutorial, I am looking forward to getting this working.

  3. RAN says:

    Thanks for your help. Hope this will work well………

      • RAN says:

        Hi Web Developer. I tried DDpanel Saving State method. But it did’t work for me…Nothing saved in the database…… What can i do… I am new to PHP. But i found a way to store the position in database and we can easily retrieve the positions. (‘If you wish i can share here’). But i can’t store the collapse state in database… Can you help me. ((” Please give me a solution for the above program. “))… Thanks in advance………..

      • Satbir says:

        make sure you created the table widgets correctly using the provided SQL file with source code and updated the database configuration in config.php file.

      • RAN says:

        Hi. Thanks for your reply.. I created the table and inserted the values as above mentioned… But noting happens… Please check it out. Is there any changes i need to made in the above source code file…. ???

      • Satbir says:

        just download the source code, extract it, set up database table using provided SQL file, configure the DB connection in config.php file and it should work.

      • hrabizadeh says:

        Hi ، I am a asp.net developer but when found your beautiful code I decided to Install PHP and test this code .
        after installing the PHP and download your code and run the SQL code every thing work very good but when I changed the box position in the page and Refresh the page , page didn’t update.
        I think you must check the updatePanels.php page !!

        best regard

      • Swetha says:

        @hrabizadeh
        Just wanted to help you with your problem. A previous visitor/user was kind enough to post the answer to the problem that you have experienced. It was posted by Frank on November 4, 2009 at 3:02 PM. I am pasting the code below, but the actual credit goes to Frank. I am simply citing it here for your easy reference:

        ///////Change the code in updatepanels.php page:

        if (get_magic_quotes_gpc()) {
        function stripslashes_deep($value) {
        $value = is_array($value) ? array_map(’stripslashes_deep’, $value) :
        stripslashes($value);
        return $value; }
        $_POST = array_map(’stripslashes_deep’, $_POST);
        $_GET = array_map(’stripslashes_deep’, $_GET);
        $_COOKIE = array_map(’stripslashes_deep’, $_COOKIE);
        $_REQUEST = array_map(’stripslashes_deep’, $_REQUEST);
        }
        $data=json_decode($_POST['data']);

        Hope that helps you. Let us know.
        Regards.

      • LP says:

        It doesn’t work for me… :(

        The saving procedure doesn’t update the database :(

  4. Bill Ludwig says:

    Awesome post. This is something that I had already struggled with and your solution is much more elegant than mine. I’ll try it out and see how it works. Thanks again.

  5. mark says:

    I managed to fix the problem.

    Basically, the data was being sent to the php file with a ton of backslashes “\” in the json. php does not seem to be able top parse this correctly, which is why I was getting the invalid argument in the foreach.

    What I did was this:
    //decode JSON data received from AJAX POST request
    $data = $_POST['data'];
    $json = str_replace(‘\\’,”,$data);
    $newdata = json_decode($json);

    foreach($newdata->items as $item)

    We grab the data from the post, then we replace all backslashes with nothing, which results in a correct json_decode string. then we pass this to the foreach with the new variable and the rest of the script parses it and updates the database accordingly.

    I hope this helps anyone who has the same problem.

  6. mark says:

    ahh, magic_quotes. had clean forgotten about those. thank you for reminding me.

    Everything is working brilliantly now apart from one or two mods I made to the javascript. I will keep plodding along.

    Thank you

  7. Mohd Arif Khan says:

    hi, I am working in asp.net using c# so can u make the code in asp.net
    Thanks In advance

  8. Mohd Arif Khan says:

    Thanks a lot .I have used the code it works fine in Mozilla Firfox but it doesnot work with IE 8.
    I am looking forward to getting this working.

  9. RAN says:

    Ya i did the same… But it did’t work. I mean did’t save the positions…. But it retrives the datas from the storage place…

  10. unfilter says:

    this post is great, and i’ve learned a great deal. one question – for a variety of reasons, i’m trying to make the header a div instead of an h2. when i make that change, i can’t get the hover functionality adding the collapse class to work, even though the click functionality and drag functionality works. is there some reason that this is incorrect?

    $(function() {
    $(‘.dragbox’)
    .each(function() {
    $(this).hover(function() {
    $(this).find(‘.dragbox-header’).addClass(‘.collapse’);
    }, function() {
    $(this).find(‘.dragbox-header’).removeClass(‘.collapse’);
    })
    .find(‘.dragbox-header’).click(function() {
    $(this).siblings(‘.dragbox-maincontent’).toggle();
    })
    .end()
    });

    thanks

  11. unfilter says:

    i should note that i pasted slightly incorrect code up there, i’ve tried both with “.collapse” and “collapse”

    thanks

  12. RAN says:

    Hi… I learned so many things from this tutorial… Unfortunately i can’t successfully save the positions with this files. But i found an other way and succeeded … Any way thanks alot for the help.

    And also i noticed that if i put all the panels to one column it could be undroppable in column2 after refresh the page. But in my way this problem didn’t occur… Please concern it… Once again thanks for this Useful post…

  13. Frank says:

    Doesn’t save anything the config file is edit to my specs and the mysql table excist. The connection to my database is correct, but it doesn’t update or insert any data.

    Please help because it’s a nice tutorial

  14. Zombica says:

    Hi nice one just the one to whom i was looking for. Just wanna know any additional sense to the code so that it can increase number of columns say up to 4 or 5.

    Thanks in advance.

  15. zaas says:

    Hi, thanks for the script. It is really great. I’m going to use this to move sections of my website about. It doesn’t auto update at all though for some reason. I’ve used the sql file included in the source and it reads from the db fine but it doesn’t auto save on moving or collapsing modules. I’ve checked that the function updateWidgetData() is being called – and it is, and updatePanels.php is present as in your download so I’m not sure what the problem is. I’ve tested in various browsers but get the same result. No saving of updated info. My main browser is IE 7 using winXp. Anyone have any ideas on this? Kind Regards, Zaas.

  16. zaas says:

    Hi, to furher this – I’ve been testing ‘updatePanels.php’ adding echo statements. For some reason the updateWidgetData() function runs but it doesn’t actually get to call ‘updatePanels.php’ . I’m not sure why that is the case – is this a ajax problem? Thanks again. Zaas

  17. zaas says:

    Hi again, anyone figured out how to save this to a database correctly as yet? Thanks, Zaas

  18. Frank says:

    I put magic_quotes off on my local and it works very nice. But my hosting company won’t put magic_quotes off, is there an other way to make the script work?

  19. Frank says:

    I found the solution if you can’t put off the magic quotes or you have a lower version than php 6.

    updatePanels.php:

    items as $item)
    {
    $col_id=preg_replace(‘/[^\d\s]/’, ”, $item->column);
    $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”;

    ?>

    Info from php.net

  20. Frank says:

    I have the solution check this.

    Your magic_quotes_gpc = on so you have to change code.
    The code you have to change in updatepanels.php :

    if (get_magic_quotes_gpc()) {
    function stripslashes_deep($value) {
    $value = is_array($value) ? array_map(‘stripslashes_deep’, $value) :
    stripslashes($value);
    return $value; }
    $_POST = array_map(‘stripslashes_deep’, $_POST);
    $_GET = array_map(‘stripslashes_deep’, $_GET);
    $_COOKIE = array_map(‘stripslashes_deep’, $_COOKIE);
    $_REQUEST = array_map(‘stripslashes_deep’, $_REQUEST);
    }
    $data=json_decode($_POST['data']);

  21. ARCHER says:

    Great work!
    Any chance someone pack this as a wp plug-in?
    Pleaseee! :) )))

  22. kumar says:

    I have download the zip file and change config

    i inserted table which you given like,But i am getting the record display only, i am not getting the drag and drop like google,is there anything else i can update in that file

    thanks
    kumar

  23. Jordie says:

    You should really be escaping those values before entering them into an SQL query or you’re risking an SQL injection attack.

    $item->order = mysql_real_escape_string($item->order);
    $item->collapsed = mysql_real_escape_string($item->collapsed);

    But seeing as they’re numbers, it’s better to cast them to integers:

    $item->order = (int)$item->order;
    $item->collapsed = (int)$item->collapsed;

    $col_id and $widget_id should be OK, but your preg_replace is still letting spaces through for some reason? Your database fields are integer fields.

    Probably best to cast them to integers as well:

    $col_id = (int)$item->column;
    $widget_id = (int)$item->id;

    Final code should look something like:

    foreach($data->items as $item)
    {
    $item->column = (int)$item->column;
    $item->id = (int)$item->id;
    $item->order = (int)$item->order;
    $item->collapsed = (int)$item->collapsed;

    $sql="UPDATE widgets SET column_id='".$item->column."', sort_no='".$item->order."', collapsed='".$item->collapsed."' WHERE id='".$item->id."'";
    mysql_query($sql) or die('Error updating widget DB');
    }

    Cheers. =)

  24. Jordie says:

    Feel free to fix the spacing/newlines up in my previous comment, didn’t seem to come through nicely.

  25. LP says:

    hmm… After reviewing all comments I have a hard time figuring out what to do and change. I have the same problem as many with the procedure of updating the database with new positions… Is it possible to update the tutorial and / or the source files so this issue wound be questioned again?

    Really great function btw :)

  26. jun says:

    I like this post, but link demo Download the source code is not work.
    U can update Permalink this.
    Thank you.

Comments are closed for this post.