jQuery Sortable Portlet Save position to database

jQuery Sortable Portlet Save position to database

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>
    1. <?php include('connection.php'); ?>
    2. <html lang="en">
    3. <head>
    4.   <meta charset="utf-8">
    5.   <meta name="viewport" content="width=device-width, initial-scale=1">
    6.   <title>jQuery UI Sortable - Portlets</title>
    7.   <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    8.   <link rel="stylesheet" href="/resources/demos/style.css">
    9.   <style>
    10.   body {
    11.     min-width: 520px;
    12.   }
    13.    .new {
    14.     width: 300px;
    15.     float: left;
    16. font-size:18px;
    17. color:#C95F0D;
    18. border:#636262 solid;
    19. text-align:center;
    20. vertical-align:middle;
    21. line-height:90px;
    22.   }
    23.   .clear {
    24.     width: 300px;
    25.     float: right;
    26.     padding-bottom: 50px;
    27. font-size:18px;
    28. color:#7433D0;
    29. border:#636262 solid;
    30. margin-right:100px;
    31.   }
    32.   .star {
    33.     width: 300px;
    34.     float: right;
    35.     padding-bottom: 50px;
    36. font-size:18px;
    37. color:#7433D0;
    38. border:#636262 solid;
    39. margin-right:100px;
    40.   }
    41.  .column {
    42.     width: 200px;
    43.     float: left;
    44.     padding-bottom: 100px;
    45.   }
    46.   .portlet {
    47.     margin: 0 1em 1em 0;
    48.     padding: 0.3em;
    49.   }
    50.   .portlet-header {
    51.     padding: 0.2em 0.3em;
    52.     margin-bottom: 0.5em;
    53.     position: relative;
    54.   }
    55.   .portlet-toggle {
    56.     position: absolute;
    57.     top: 50%;
    58.     right: 0;
    59.     margin-top: -8px;
    60.   }
    61.   .portlet-content {
    62.     display:none; padding: 0.4em;
    63.   }
    64.   .portlet-placeholder {
    65.     border: 1px dotted black;
    66.     margin: 0 1em 1em 0;
    67.     height: 50px;
    68.   }
    69.   </style>
    70.   <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    71.   <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    72. <script>
    73.   $(document).ready(function(){
    74.     $( ".column" ).sortable({
    75.       connectWith: ".column",
    76.       handle: ".portlet-header",
    77.       cancel: ".portlet-toggle",
    78.       placeholder: "portlet-placeholder ui-corner-all",
    79.         stop: function() {
    80.             var dat = [];
    81.             var i = 0;
    82.             $(".column").each(function() {
    83.                 dat[i++] = [this.id,$(this).sortable("toArray")]; // this.id is the column id, the 2nd element are the job id's in that column
    84.             });
    85.             
    86.             $.ajax({
    87.                 method: "POST",
    88.                 url: "save_order.php",
    89.                 data: { data: dat }
    90.             });
    91.         }
    92.     });

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

    98.     $( ".portlet-toggle" ).on( "click", function() {
    99.       var icon = $( this );
    100.       icon.toggleClass( "ui-icon-minusthick ui-icon-plusthick" );
    101.       icon.closest( ".portlet" ).find( ".portlet-content" ).toggle();
    102.     });
    103. });
    104.   </script>
    105. </head>
    106. <body>
    107.  <div class="toolbar"><a href="newjobs.php"><div class="new">Get New Jobs</div></a><div class="clear">Clear Job</div></div>
    108.  
    109.  <div style="clear:both;"></div>
    110.  
    111. <?php
    112. // define status value to label mapping
    113. $status_map = array();
    114. $status_map[1] = 'New';
    115. $status_map[2] = 'Artwork Rec';
    116. $status_map[3] = 'Approved & Ordered';
    117. $status_map[4] = 'In Production';
    118. $status_map[5] = 'Delivered';
    119. $status_map[6] = 'To Be Invoiced';

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

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

    129. // loop over the data and produce the output
    130. foreach($status_map as $key=>$label)
    131. {
    132.     echo "<div class='column' id='COL_$key'>\n";
    133.     echo "<h3>$label</h3>\n";
    134.     if(isset($data[$key])) // is there any data from the database for this key/status
    135.     {
    136.         foreach($data[$key] as $row)
    137.         {
    138.             echo "<div class='portlet' id='ID_{$row['id']}'>\n";
    139.             echo    "<div class='portlet-header'>";
    140.  
    141. if ($row['urgent'] == 1) { 
    142.         echo "<p style='color:red;'>".$row['job_title']."</p>";  
    143. } else {
    144. echo "<p style='color:black;'>".$row['job_title']."</p>"; 
    145.             echo "</div>\n";
    146.             echo    "<div class='portlet-content'><a href='pdfs/{$row['pdf_link']}' target='_blank'>View PDF</a></div>\n";
    147.             echo "</div>\n";
    148.         }
    149.     }
    150.     echo "</div>\n";
    151. }
    152. ?>
    153. </body>
    154. </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
    1. include('connection.php');
    2.     foreach ($_POST['status'] as $value) {

    3.         // Database stuff

    4.     }
    5. ?>



    All help much appreciated.

     

    Regards,

     

    MsKazza