How Can i save jquery sortable connected lists in database mysql?-CodePudding

Hey guys I have two connected lists and I don't know how to pass or get values correctly

I have Two list

The left list: the value I want to add is "0" which mean it's inactive

The Right list: the value that I want to add is "1" which mean it's active also with a value position

this is my HTML code:

<h5>inactive fruit</h5>
<ul id="sortable1" >

        <?php foreach($rows as $row){
                        if($row['active'] === '0'){ ?> 
        
            <li ><?php echo $row['name']?></li>
        
        <?php   }}?>
</ul>


<h5>Active Market</h5>
<ul id="sortable2" >

    <?php foreach($rows as $row){
                        if($row['active'] === '1'){ ?> 
        
            <li ><?php echo $row['name']?></li>
        
        <?php   }}?>
</ul>

This is js code:

  <script>
   $( function() {
    $( "#sortable1, #sortable2" ).sortable({
      connectWith: ".connectedSortable",
        }).disableSelection();
   } );
  </script>

i want to do it with ajax

The Database structure is:

ID (int),

name (varchar),

active (varchar) number 1 means active number 2 means inactive,

posistion(varchar) for the posistion rank

CodePudding user response：

Consider the following example.

jQuery(function($) {
  function sendData(api, data) {
    $.post(api, data, function(results) {
      console.log(results);
    });
  }
  $(".connectedSortable").sortable({
    connectWith: ".connectedSortable",
    update: function(event, ui) {
      // This event is triggered when the user stopped sorting and the DOM position has changed.
      var inactive = $("#sortable1").sortable("serialize");
      var active = $("#sortable2").sortable("serialize");
      console.log(inactive, active);
      sendData("updateFruit.php", {
        inactive: inactive,
        active: active
      });
    }
  }).disableSelection();
});

.column {
  margin: 0;
  padding: 0;
  width: 200px;
  text-align: center;
  float: left;
}

.column h5 {
  padding: 0.4em;
  margin: : 0;
}

.column ul {
  margin: 0;
  padding: 0;
  list-style: none;
}

.column ul li {
  padding: 0.4em;
}

<link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
<div >
  <h5>Inactive fruit</h5>
  <ul id="sortable1" >
    <li  id="fruit-1">Mango</li>
    <li  id="fruit-2">Orange</li>
    <li  id="fruit-3">Kewi</li>
  </ul>
</div>
<div >
  <h5>Active Market</h5>
  <ul id="sortable2" >
    <li  id="fruit-4">Banana</li>
  </ul>
</div>

This makes use of a few different options:

Your PHP Script will have to handle the posted data. Something like:

<?php

$active = array();
$inactive = array();

if(isset($_POST['active'])){
  $active = json_decode($_POST['active']);
}
if(isset($_POST['active'])){
  $inactive = json_decode($_POST['inactive']);
}

$rows = array();
$count = 0;

foreach($active as $name){
  array_push($rows, array(
    "name" => $name,
    "active" => 1,
    "position": $count  
  ));
}

$count = 0;

foreach($inactive as $name){
  array_push($rows, array(
    "name" => $name,
    "active" => 0,
    "position" => $count  
  ));
}

// Connect to MySQL DB, stored to $mysqli
// Generate Update Code, either unique Statements or one large statement
foreach($rows as $row){
  $mysqli->query("UPDATE fruits SET active = {$row.active}, position = {$row.position} WHERE name = '{$row.name}';);
}

$mysqli->close();
?>

Reference:

This is a basic example and your code will likely be different. This example also does not sanitize the inputs or protect again SQL Injection and should only be used as an basic example. Production code should use all the proper SQL techniques and best practices.