jQuery Sortable Portlet Save position to database

MsKazza · October 5, 2016

Hi,

I'm trying to figure out how to make a multi column jquery sortable Portlet, save the column positions to a database onChange, I know that sortable has a serialize function but i really haven't had any luck getting it to work. If someone could please help me out with this. I have spent hours on google trying to find something.

<!doctype html>
<?php include('connection.php'); ?>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>jQuery UI Sortable - Portlets</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <style>
  body {
    min-width: 520px;
  }
   .new {
    width: 300px;
    float: left;
	font-size:18px;
	color:#C95F0D;
	border:#636262 solid;
	text-align:center;
	vertical-align:middle;
	line-height:90px;
  }
  .clear {
    width: 300px;
    float: right;
    padding-bottom: 50px;
	font-size:18px;
	color:#7433D0;
	border:#636262 solid;
	margin-right:100px;
  }
  .star {
    width: 300px;
    float: right;
    padding-bottom: 50px;
	font-size:18px;
	color:#7433D0;
	border:#636262 solid;
	margin-right:100px;
  }
 .column {
    width: 170px;
    float: left;
    padding-bottom: 100px;
  }
  .portlet {
    margin: 0 1em 1em 0;
    padding: 0.3em;
  }
  .portlet-header {
    padding: 0.2em 0.3em;
    margin-bottom: 0.5em;
    position: relative;
  }
  .portlet-toggle {
    position: absolute;
    top: 50%;
    right: 0;
    margin-top: -8px;
  }
  .portlet-content {
    padding: 0.4em;
  }
  .portlet-placeholder {
    border: 1px dotted black;
    margin: 0 1em 1em 0;
    height: 50px;
  }
  </style>
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  <script>
  $( function() {
    $( ".column" ).sortable({
      connectWith: ".column",
      handle: ".portlet-header",
      cancel: ".portlet-toggle",
      placeholder: "portlet-placeholder ui-corner-all"
    });
 
    $( ".portlet" )
      .addClass( "ui-widget ui-widget-content ui-helper-clearfix ui-corner-all" )
      .find( ".portlet-header" )
        .addClass( "ui-widget-header ui-corner-all" )
        .prepend( "<span class='ui-icon ui-icon-minusthick portlet-toggle'></span>");
 
    $( ".portlet-toggle" ).on( "click", function() {
      var icon = $( this );
      icon.toggleClass( "ui-icon-minusthick ui-icon-plusthick" );
      icon.closest( ".portlet" ).find( ".portlet-content" ).toggle();
    });
	
	$("#portlet").sortable({ 
            handle : '.handle', 
            update : function () { 
                var order = $('#portlet').sortable('serialize'); 
                $("#portlet").load("save_order.php?"+order); 
            } 
        }); 
  } );
  
  </script>
</head>
<body>
 <div class="toolbar"><div class="new">Get New Jobs</div><div class="clear">Clear Job</div></div>
 
 <div style="clear:both;"></div>
 
<div class="column" id="1">
 <h3>New</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='1' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>
 
<div class="column"  id="2">
 <h3>Artwork Rec</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='2' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>
 
<div class="column" id="3">
 <h3>Approved & Ordered</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='3' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>

<div class="column" id="4">
 <h3>In Production</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='4' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>

<div class="column" id="5">
 <h3>Delivered</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='5' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>

<div class="column" id="6">
 <h3>To Be Invoiced</h3>
 <?php
 $result = mysqli_query($con,"SELECT * FROM jobs WHERE status='6' ORDER BY job_title DESC");
 
 while($row = mysqli_fetch_array($result))
  {
	  ?>
  <div class="portlet">
    <div class="portlet-header"><?php echo $row['job_title']; ?></div>
    <div class="portlet-content"><a href="pdfs/<?php echo $row['pdf_link']; ?>" target="_blank">View PDF</a></div>
  </div>
 
 <?php }  ?>
 
</div>

 
 
</body>
</html>

And this is the save_order.php file. I am unsure how to write the update statement getting the column id etc from the previous page.

<?php
include('connection.php');
    foreach ($_POST['status'] as $value) {

        // Database stuff

    }
?>

All help much appreciated.

Regards,

MsKazza

mac_gyver · October 5, 2016

I was going to ask if you would post a sample of your jobs data, in a php array format (see var_export()), so that we would have something to test with, but in looking at your code, you need to first write a single sql query that gets ALL the data you want in the order that you want it, rather than having 6 queries. then fetch all the data into an array. your code would then loop over that array to produce the output. at this point, you could use var_export() on that array to supply us with some data to test with.

mac_gyver · October 5, 2016

so, i went through your code to figure out what it is doing, in order (pun intended) to figure out how to make this work.

starting with your html markup -

1) the id='...' attribute you have for the column div's, should start with a letter, to make them valid. i choose to use id='COL_n', where n = 1, 2, ..., 6

2) the portlet div's needs an id attribute so that the serialize/toArray functions have something to use as data. i choose to use id='ID_n', where n is the job id auto-increment column value from your database table (if you don't have a job id, you need one.)

3) to allow the php code to dynamically produce the html, for the different status values 1-6, you need a way of mapping the status value to the display label - 'New' through 'To Be Invoiced'. i choose to use an array, and since you (probably) want to output each status section, regardless of if there is any data for it, you would loop over this array to produce the sections on the page, then loop over any data for each section.

the html, from the <div style="clear:both;"></div> to the end of the page should look more like the following (note: data values that you output to the browser should be passed through htmlentities(). this is not in the example code and is left up to you as a programming exercise) -

<?php
// define status value to label mapping
$status_map = array();
$status_map[1] = 'New';
$status_map[2] = 'Artwork Rec';
$status_map[3] = 'Approved & Ordered';
$status_map[4] = 'In Production';
$status_map[5] = 'Delivered';
$status_map[6] = 'To Be Invoiced';

// query for all the data you want, in the order that you want it
$query = "SELECT * FROM jobs ORDER BY status ASC, job_title DESC";
$result = mysqli_query($con,$query);
$data = array();
while($row = mysqli_fetch_assoc($result))
{
    $data[$row['status']][] = $row; // index/pivot the data using the status value - 1..6
}

// note: i used some made-up data in the $data array at this point. the above query code should work, but is untested.

// loop over the data and produce the output
foreach($status_map as $key=>$label)
{
    echo "<div class='column' id='COL_$key'>\n";
    echo "<h3>$label</h3>\n";
    if(isset($data[$key])) // is there any data from the database for this key/status
    {
        foreach($data[$key] as $row)
        {
            echo "<div class='portlet' id='ID_{$row['id']}'>\n";
            echo    "<div class='portlet-header'>{$row['job_title']}</div>\n";
            echo    "<div class='portlet-content'><a href='pdfs/{$row['pdf_link']}' target='_blank'>View PDF</a></div>\n";
            echo "</div>\n";
        }
    }
    echo "</div>\n";
}
?>
</body>
</html>

next, the jquery you have that is using an id selector - "#portlet" should be removed since this exercise is operating on a class basis, not an id. also, you would not use the update : method, since this triggers for every column that gets updated. if you move something from one column to another, it triggers two times. you need to use the stop : method. see the following javascript/jquery that i came up with -

  <script>
  $(document).ready(function(){
    $( ".column" ).sortable({
      connectWith: ".column",
      handle: ".portlet-header",
      cancel: ".portlet-toggle",
      placeholder: "portlet-placeholder ui-corner-all",
        stop: function() {
            var dat = [];
            var i = 0;
            $(".column").each(function() {
                dat[i++] = [this.id,$(this).sortable("toArray")]; // this.id is the column id, the 2nd element are the job id's in that column
            });
            
            $.ajax({
                method: "POST",
                url: "save_order.php",
                data: { data: dat }
            });
        }
    });

    $( ".portlet" )
      .addClass( "ui-widget ui-widget-content ui-helper-clearfix ui-corner-all" )
      .find( ".portlet-header" )
        .addClass( "ui-widget-header ui-corner-all" )
        .prepend( "<span class='ui-icon ui-icon-minusthick portlet-toggle'></span>");

    $( ".portlet-toggle" ).on( "click", function() {
      var icon = $( this );
      icon.toggleClass( "ui-icon-minusthick ui-icon-plusthick" );
      icon.closest( ".portlet" ).find( ".portlet-content" ).toggle();
    });
});
  </script>

this will submit an array of data to the .php file, in $_POST['data']. see the following example code that extracts the column number and job id (if any), and logs the information to log.txt -

<?php

// for some reason, the portlet toArray 'adds' an empty element to the start of each array

if(isset($_POST['data']))
{
    foreach($_POST['data'] as $arr)
    {
        //$arr[0] is the column id - COL_1, COL_2 (these are the status number 1-6 = New - To Be Invoiced)
        //$arr[1] is an array of the ids that are in the column - ID_1, ID_5
        
        // get the status (column) number
        list($not_used,$status) = explode('_',$arr[0]);
        
        // get the id's in each status/column
        $arr[1] = array_filter($arr[1]); // remove empty elements
        if(empty($arr[1]))
        {
            // an empty status/column
            $str = "Status: $status, empty";
            file_put_contents('log.txt',print_r($str,true)."\n",FILE_APPEND);
        }
        else
        {
            // non-empty status/column
            foreach($arr[1] as $element)
            {
                // get the id number
                list($not_used,$id) = explode('_',$element);
                $str = "Status: $status, Id: $id";
                file_put_contents('log.txt',print_r($str,true)."\n",FILE_APPEND);
            }
        }
    }
}

MsKazza · October 6, 2016

Thank you so much for your reply. I have edited as per your suggestions. However it doesn't ever seem to post the data to the save_order.php, am I missing something or shouldn't there be some kind of onChange call?

mac_gyver · October 6, 2016

what debugging have you done to find what the code IS doing?

the code i posted is tested and submits and logs the data. aside from the note about the actual database statements not being used and therefore not being tested, the code works.

is your data being being displayed? does your database table have an id column, that's named 'id'? what does the developer console in your browser show? is the 'log.txt' file being created on the server?

MsKazza · October 7, 2016

Thank you it is working, i have started using netbeans to help me debug. There was an extra character that was preventing the save_order from running.

Thanks again.

MsKazza

MsKazza · October 10, 2016

Thank you so much for your answers, I have been trying to create an update statement to save the info to db, however it doesn't update db. If anyone can tell me what i'm doing wrong i would very much appreciate it. It updates the text file no problem.

foreach($arr[1] as $element)
            {
                // get the id number
                list($not_used,$id) = explode('_',$element);
                $str = "Status: $status, Id: $id";
                file_put_contents('log.txt',print_r($str,true)."\n",FILE_APPEND);
                $query = mysql_query("UPDATE jobs SET status = " . $status . " WHERE id = " .$id );
            }

Thanks,

MsKazza

mac_gyver · October 10, 2016

is the query failing (from error handling logic that you should always have in your code) or are the php statements failing (form having php's error_reporting/display_errors turned fully on in the php.ini on your development system)?

if your original posted code was using the php mysqli extension, why are you now using the php mysql extension? the mysql extension has been removed from the php since the end of last year.

you should also be using a prepared query (supported by the php mysqli and pdo extensions) to supply the data values to the sql query statement and this will also make executing the query inside of a loop slightly more efficient, since you will prepare the query only once before the start of the loop.

also, if you are still asking questions in this thread, you should de-select the solved post so that people will see that the thread is not actually solved and will read it to see the new questions.

Edited October 10, 2016 by mac_gyver

Sign In

jQuery Sortable Portlet Save position to database

Recommended Posts

MsKazza

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

MsKazza

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

MsKazza

Link to comment

Share on other sites

MsKazza

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information