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

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

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

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

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

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

    5.         // Database stuff

    7.     }
    8. ?>



    All help much appreciated.

     

    Regards,

     

    MsKazza