Webistrate - Draw Your Own Conclusions

CakePHP: Using the jQuery Sortable Plugin and Saving to the Database

Posted by Jamie Munro | September 26, 2011 | Tags: ,

Share

It’s time to permanently remove all “manual” sorting from the Internet.  You know the one I mean where it has the up and down arrows – or even worse, the text box that accepts a numerical order input.  By implementing the jQuery Sortable Plugin, you will be able to provide a simple, but effective drag-and-drop ordering solution for just about any type of data!

In a recent article, I described the required HTML and Javascript code need to implement the jQuery Sortable Plugin on a gallery of images.  If you haven’t already done so, please begin by reading this article because this one will gloss over those features and focus on how to implement this with CakePHP.

Assumptions

  • You have an existing CakePHP website and it is connecting to a database
  • You have some basic database experience to create new tables

In the previous article about setting up the core Javascript and HTML, you will notice that I glossed over the database design.  In this article, that is exactly where will start.  To not limit the end-user, I will create both an albums table and a photos table that relate to each other – allowing a user to create more than one photo album.

First, create the albums table with the following SQL:

CREATE TABLE `albums` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_id` INT NOT NULL ,
`title` VARCHAR( 150 ) NOT NULL ,
`created` DATETIME NULL ,
`modified` DATETIME NULL
) ENGINE = MYISAM ;

As you can see in the above example, I’ve included a foreign key to the users table through the user_id column.  If you haven’t already done so, you might want to check out this article on creating a CakePHP Login System with the Authentication Component.  Once you have followed the instructions in this article, you will have a fully functioning registration and login system that will allow you to ensure users are managing their own albums only.

Once the album is created, the photos table must be created.  This table doesn’t contain a user_id because it’s associated directly to the albums table which has the association.

CREATE TABLE `photos` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`album_id` INT NOT NULL ,
`filename` VARCHAR( 150 ) NOT NULL ,
`order` INT NOT NULL,
`created` DATETIME NULL ,
`modified` DATETIME NULL
) ENGINE = MYISAM ;

Once you have created these tables, you will need to scaffold them using CakePHP’s Bakery.  Here is another article to get you started with that: CakePHP: Scaffolding a new database table.

Now that the scaffolding is complete, a few adjustments must be made to the controllers and views.  I’ll start with the albums controller, because the user must create an album before photos can be uploaded.  The key changes that must be made is, ensure the user is logged in and all of the views must be updated to remove the user id as a field because we’ll automatically set it based on the currently logged in user.

Below is an updated albums_controller.php:

<?php
class AlbumsController extends AppController {

var $name = 'Albums';
var $components = array('Auth');

function beforeFilter() {
parent::beforeFilter();
// all actions must have logged in user
$this->Auth->allow('');
}

function index() {
$this->Album->recursive = -1;

// only display albums for the logged in user
$this->paginate['conditions'] = array('Album.user_id' => $this->Auth->user('id'));

$this->set('albums', $this->paginate());
}

function add() {
if (!empty($this->data)) {
$this->Album->create();

// set the user id to logged in user
$this->data['Album']['user_id'] = $this->Auth->user('id');

if ($this->Album->save($this->data)) {
$this->Session->setFlash(__('The album has been saved', true));
$this->redirect(array('action' => 'index'));
} else {
$this->Session->setFlash(__('The album could not be saved. Please, try again.', true));
}
}
}

function edit($id = null) {
if (!$id && empty($this->data)) {
$this->Session->setFlash(__('Invalid album', true));
$this->redirect(array('action' => 'index'));
}

if (!empty($this->data)) {
if ($this->Album->save($this->data)) {
$this->Session->setFlash(__('The album has been saved', true));
$this->redirect(array('action' => 'index'));
} else {
$this->Session->setFlash(__('The album could not be saved. Please, try again.', true));
}
}
if (empty($this->data)) {
$this->data = $this->Album->read(null, $id);
}
}

function delete($id = null) {
if (!$id) {
$this->Session->setFlash(__('Invalid id for album', true));
$this->redirect(array('action'=>'index'));
}

if ($this->Album->delete($id)) {
$this->Session->setFlash(__('Album deleted', true));
$this->redirect(array('action'=>'index'));
}
$this->Session->setFlash(__('Album was not deleted', true));
$this->redirect(array('action' => 'index'));
}
}

Next the views for the albums need to be updated.  As I mentioned above, the add and edit views shouldn’t let a user be selected.  Also, in the index view I’m going to update the view link and make it go to the photos page.

albums/add.ctp:

<div>
<?php echo $this->Form->create('Album');?>
<fieldset>
<legend><?php __('Add Album'); ?></legend>
<?php
echo $this->Form->input('title');
?>
</fieldset>
<?php echo $this->Form->end(__('Submit', true));?>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('List Albums', true), array('action' => 'index'));?></li>
</ul>
</div>

albums/edit.ctp:

<div>
<?php echo $this->Form->create('Album');?>
<fieldset>
<legend><?php __('Edit Album'); ?></legend>
<?php
echo $this->Form->input('id');
echo $this->Form->input('title');
?>
</fieldset>
<?php echo $this->Form->end(__('Submit', true));?>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('Delete', true), array('action' => 'delete', $this->Form->value('Album.id')), null, sprintf(__('Are you sure you want to delete # %s?', true), $this->Form->value('Album.id'))); ?></li>
<li><?php echo $this->Html->link(__('List Albums', true), array('action' => 'index'));?></li>
</ul>
</div>

albums/index.ctp:

<div>
<h2><?php __('Albums');?></h2>
<table cellpadding="0" cellspacing="0">
<tr>
<th><?php echo $this->Paginator->sort('id');?></th>
<th><?php echo $this->Paginator->sort('title');?></th>
<th><?php echo $this->Paginator->sort('created');?></th>
<th><?php echo $this->Paginator->sort('modified');?></th>
<th class="actions"><?php __('Actions');?></th>
</tr>
<?php
$i = 0;
foreach ($albums as $album):
$class = null;
if ($i++ % 2 == 0) {
$class = '';
}
?>
<tr<?php echo $class;?>>
<td><?php echo $album['Album']['id']; ?>&nbsp;</td>
<td><?php echo $album['Album']['title']; ?>&nbsp;</td>
<td><?php echo $album['Album']['created']; ?>&nbsp;</td>
<td><?php echo $album['Album']['modified']; ?>&nbsp;</td>
<td class="actions">
<?php echo $this->Html->link(__('View', true), array('action' => 'index', 'controller' => 'photos', $album['Album']['id'])); ?>
<?php echo $this->Html->link(__('Edit', true), array('action' => 'edit', $album['Album']['id'])); ?>
<?php echo $this->Html->link(__('Delete', true), array('action' => 'delete', $album['Album']['id']), null, sprintf(__('Are you sure you want to delete # %s?', true), $album['Album']['id'])); ?>
</td>
</tr>
<?php endforeach; ?>
</table>

<p>
<?php
echo $this->Paginator->counter(array(
'format' => __('Page %page% of %pages%, showing %current% records out of %count% total, starting on record %start%, ending on %end%', true)
));
?>           </p>

<div class="paging">
<?php echo $this->Paginator->prev('<< ' . __('previous', true), array(), null, array('class'=>'disabled'));?>
|            <?php echo $this->Paginator->numbers();?> |
<?php echo $this->Paginator->next(__('next', true) . ' >>', array(), null, array('class' => 'disabled'));?>
</div>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('New Album', true), array('action' => 'add')); ?></li>
</ul>
</div>

The albums/view.ctp can be removed as it is not needed.  Next, the photos_controller.php requires updating as well.  Instead of having a select list of albums, I am going to update all of the views to pass an album id through the URL.  The scope of this article won’t cover file uploading and resizing, perhaps that is a good candidate for a future article.

Below is an updated photos_controller.php:

<?php
class PhotosController extends AppController {
var $name = 'Photos';
var $components = array('Auth');

function beforeFilter() {
parent::beforeFilter();
// all actions must have logged in user
$this->Auth->allow('');
}

function index($album_id = null) {
if (!$album_id) {
$this->Session->setFlash(__('Invalid album', true));
$this->redirect(array('action' => 'index', 'controller' => 'albums'));
}

$this->Photo->recursive = 0;
$this->paginate['conditions'] = array('Photo.album_id' => $album_id);
$this->paginate['order'] = 'Photo.order';
$this->set('photos', $this->paginate());
$this->set('album_id', $album_id);
}

function update_order() {
foreach ($_GET['photo'] as $order => $id) {
$this->Photo->id = $id;
$this->Photo->saveField('order', $order);
}
$this->render(false);
}

function add($album_id = null) {
if (!$album_id && empty($this->data)) {
$this->Session->setFlash(__('Invalid album', true));
$this->redirect(array('action' => 'index', 'controller' => 'albums'));
}

if (!empty($this->data)) {
$this->Photo->create();

// PLACE LOGIC HERE TO UPLOAD NEW PHOTO

if ($this->Photo->save($this->data)) {
$this->Session->setFlash(__('The photo has been saved', true));
$this->redirect(array('action' => 'index', $this->data['Photo']['album_id']));
} else {
$this->Session->setFlash(__('The photo could not be saved. Please, try again.', true));
}
}
if (empty($this->data)) {
$this->data['Photo']['album_id'] = $album_id;
}
}

function edit($id = null) {
if (!$id && empty($this->data)) {
$this->Session->setFlash(__('Invalid photo', true));
$this->redirect(array('action' => 'index'));
}

if (!empty($this->data)) {

// PLACE LOGIC HERE TO DELETE OLD PHOTO

// PLACE LOGIC HERE TO UPLOAD NEW PHOTO

if ($this->Photo->save($this->data)) {
$this->Session->setFlash(__('The photo has been saved', true));
$this->redirect(array('action' => 'index', $this->data['Photo']['album_id']));
} else {
$this->Session->setFlash(__('The photo could not be saved. Please, try again.', true));
}
}

if (empty($this->data)) {
$this->data = $this->Photo->read(null, $id);
}
}

function delete($album_id, $id = null) {
if (!$id) {
$this->Session->setFlash(__('Invalid id for photo', true));
$this->redirect(array('action'=>'index', $album_id));
}

// PLACE LOGIC HERE TO DELETE OLD PHOTO

if ($this->Photo->delete($id)) {
$this->Session->setFlash(__('Photo deleted', true));
$this->redirect(array('action'=>'index', $album_id));
}
$this->Session->setFlash(__('Photo was not deleted', true));
$this->redirect(array('action' => 'index', $album_id));
}
}

A new function has been added to the photos_controller.php called update_order.  This function loops through an array of photos updating the order field of the photo to its newly sorted position.  This function will be called view AJAX in the index.ctp view shown below.

The process is almost complete, but before I can implement the jQuery Sortable Plugin I must allow photos to be uploaded.  To do this, the add.ctp and edit.ctp require some minor edits, specifically the enctype must be set for file uploads; otherwise, the controller will never receive the file to save.

photos/add.ctp:

<div>
<?php echo $this->Form->create('Photo', array('enctype' => 'multipart/form-data'));?>
<fieldset>
<legend><?php __('Add Photo'); ?></legend>
<?php
echo $this->Form->input('album_id', array('type' => 'hidden'));
echo $this->Form->input('filename', array('type' => 'file'));
?>
</fieldset>
<?php echo $this->Form->end(__('Submit', true));?>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('List Photos', true), array('action' => 'index', $this->data['Photo']['album_id']));?></li>
<li><?php echo $this->Html->link(__('List Albums', true), array('controller' => 'albums', 'action' => 'index')); ?> </li>
<li><?php echo $this->Html->link(__('New Album', true), array('controller' => 'albums', 'action' => 'add')); ?> </li>
</ul>
</div>

photos/edit.ctp:

<div>
<?php echo $this->Form->create('Photo', array('enctype' => 'multipart/form-data'));?>
<fieldset>
<legend><?php __('Edit Photo'); ?></legend>
<?php
echo $this->Form->input('id');
echo $this->Form->input('album_id', array('type' => 'hidden'));
echo $this->Form->input('filename', array('type' => 'file'));
?>
</fieldset>
<?php echo $this->Form->end(__('Submit', true));?>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('Delete', true), array('action' => 'delete', $this->data['Photo']['album_id'], $this->Form->value('Photo.id')), null, sprintf(__('Are you sure you want to delete # %s?', true), $this->Form->value('Photo.id'))); ?></li>
<li><?php echo $this->Html->link(__('List Photos', true), array('action' => 'index', $this->data['Photo']['album_id']));?></li>
<li><?php echo $this->Html->link(__('List Albums', true), array('controller' => 'albums', 'action' => 'index')); ?> </li>
<li><?php echo $this->Html->link(__('New Album', true), array('controller' => 'albums', 'action' => 'add')); ?> </li>
</ul>
</div>

Finally, it’s time for the jQuery Sortable Plugin to be added to the photos/index.ctp view.  This will display a list of thumbnail photos ordered by the current sort order.  When a user drags and drops a photo, an AJAX call will be performed to save the new order.

<div>
<h2><?php __('Photos');?></h2>
<div id="photos">
<?php foreach ($photos as $photo):?>
<img id="photo_<?php echo $photo['Photo']['id'];?>" src="<?php echo $photo['Photo']['filename']; ?>" alt="" />
<?php endforeach; ?>
<div class="clear"></div>
</div>
</div>

<div>
<h3><?php __('Actions'); ?></h3>

<ul>
<li><?php echo $this->Html->link(__('New Photo', true), array('action' => 'add', $album_id)); ?></li>
<li><?php echo $this->Html->link(__('List Albums', true), array('controller' => 'albums', 'action' => 'index')); ?> </li>
<li><?php echo $this->Html->link(__('New Album', true), array('controller' => 'albums', 'action' => 'add')); ?> </li>
</ul>
</div>

 

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<script>
$(document).ready(function() {
$("#photos").sortable({
items: 'img',
update: function(event, ui) {
var result = $('#photos').sortable('serialize');
$.get('/photos/update_order?' + result);
}
});
});
</script>

<style>
#photos {
border: 1px solid #000;
padding: 1em;
width: 465px;
}
#photos img {
border: 1px solid #000;
float: left;
margin: 0.5em;
height: 50px;
width: 50px;
}
.clear {clear: both;}
</style>

As I described in the original implementation article of the jQuery sortable, by setting the id property of the img tag to photo_<dynamic_id> when the sortable elements are serialized after re-ordering, this will be sent to the AJAX function to use this id to set the sort order based on the order of the ids being passed in.

Summary

By leveraging several previous articles, you should be able to effectively build a new album and photo gallery management tool.  With the help of the jQuery Sortable Plugin, your users will have drag-and-drop capability to organize their albums quickly and easily.

Share


Related Posts

One Response to “CakePHP: Using the jQuery Sortable Plugin and Saving to the Database”

  1. frankie says:

    could you help me?i tried your script but i get an error when i try to sort the items,firebug says “NetworkError: 404 Not Found – http://localhost/photos/update_order?photo=1&photo=3&photo=2

Leave a Reply

You must be logged in to post a comment.

Buy one of my books