Jquery Ui Sortable, Then Write Order into a Database

jQuery UI Sortable, then write order into a database

The jQuery UI sortable feature includes a serialize method to do this. It's quite simple, really. Here's a quick example that sends the data to the specified URL as soon as an element has changes position.

$('#element').sortable({
axis: 'y',
update: function (event, ui) {
var data = $(this).sortable('serialize');

// POST to server using $.post or $.ajax
$.ajax({
data: data,
type: 'POST',
url: '/your/url/here'
});
}
});

What this does is that it creates an array of the elements using the elements id. So, I usually do something like this:

<ul id="sortable">
<li id="item-1"></li>
<li id="item-2"></li>
...
</ul>

When you use the serialize option, it will create a POST query string like this: item[]=1&item[]=2 etc. So if you make use - for example - your database IDs in the id attribute, you can then simply iterate through the POSTed array and update the elements' positions accordingly.

For example, in PHP:

$i = 0;

foreach ($_POST['item'] as $value) {
// Execute statement:
// UPDATE [Table] SET [Position] = $i WHERE [EntityId] = $value
$i++;
}

Example on jsFiddle.

Jquery UI sortable, write order into a MySql database

change data-id='article_<?php echo $i;?>' to id='<?php echo $i;?>'

add this to your sortable(),

update: function (event, ui) {
var serial=$(this).sortable('serialize');
save_sortable(serial);
}

so here on update, you are calling save_sortable() function, from ajax , update your db in the order returned from sortable()

function save_sortable(serial)
{
$.ajax({
url: "path to your file to update in db.",
type: 'POST',
data: serial,
success: function (data) {
//alert(data);
}
});
}

jQuery UI Sortable - saving order when button clicked

There is a builtin method for doing this. Refer serialize or toArray

Demo: http://jsfiddle.net/lotusgodkk/GCu2D/539/

JS:

$(function () {
$("#sortable").sortable({
update: function (event, ui) {
var order = $(this).sortable('serialize');

$(document).on("click", "button", function () { //that doesn't work
$.ajax({
data: order,
type: 'POST',
url: 'saverank.php'
});
});
}
}).disableSelection();
$('button').on('click', function () {
var r = $("#sortable").sortable("toArray");
var a = $("#sortable").sortable("serialize", {
attribute: "id"
});
console.log(r, a);
});
});

jQuery Sortable - save order when new element dragged into list

Is there a simple way to do this or is it just a matter of coding up a
decent amount of jQuery and backend functions to manage this? I have
been hunting around the net for solutions but can't find anything all
that definitive.

Pretty much yes, but if you already know how to handle Ajax calls for sortables then mixing two lists is not an issue. I am using a modified version of the coding on this page. And I am using the receive event & the stop event to show how you can hook simple function into the basic sortable coding:

<!doctype html>

<html lang="en">
<head>
<meta charset="utf-8" />
<title>jQuery UI Sortable - Connect lists</title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.0/themes/base/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.8.3.js"></script>
<script src="http://code.jquery.com/ui/1.10.0/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css" />
<style>
#sortable1, #sortable2 { list-style-type: none; margin: 0; padding: 0 0 2.5em; float: left; margin-right: 10px; }
#sortable1 li, #sortable2 li { margin: 0 5px 5px 5px; padding: 5px; font-size: 1.2em; width: 120px; }
</style>
<script>
$(function() {
$( "#sortable1, #sortable2" ).sortable({
connectWith: ".connectedSortable",
receive: function( event, ui ) { alert('Receive!'); },
stop: function( event, ui ) { alert('Stop!'); }
}).disableSelection();
});
</script>
</head>
<body>

<ul id="sortable1" class="connectedSortable">
<li class="ui-state-default">Item 1</li>
<li class="ui-state-default">Item 2</li>
<li class="ui-state-default">Item 3</li>
<li class="ui-state-default">Item 4</li>
<li class="ui-state-default">Item 5</li>
</ul>

<ul id="sortable2" class="connectedSortable">
<li class="ui-state-highlight">Item 1</li>
<li class="ui-state-highlight">Item 2</li>
<li class="ui-state-highlight">Item 3</li>
<li class="ui-state-highlight">Item 4</li>
<li class="ui-state-highlight">Item 5</li>
</ul>


</body>
</html>

So when an item is sorted and the sorting stops, a 'Stop!' alert box pops up. And when a new item is dragged into one list from another a 'Receive!' alert box pops up in addition to the stop. You might be able to just use the stop event to trigger an Ajax call, but the receive even can be useful to do something else if/when a new item is added to a list.

Store li order in Mysql DB with Jquery sortable

OK I see. Seems a bit unwieldy for a user to manually drag and drop items in a list of over 1000 items. But I don't know how/why you are doing this so will assume it works for your application.

The below solution is actually from this SO question jQuery UI Sortable, then write order into a database

jQuery Sortable has a serialize function that takes care getting the order for you. Use it like below:

$('#sortable').sortable({
axis: 'y',
stop: function (event, ui) {
var data = $(this).sortable('serialize');

// POST to server using $.post or $.ajax
$.ajax({
data: data,
type: 'POST',
url: 'save_order.php'
});
}
});

And in the save_order.php file, just check what the posted data looks like and loop though it, should be able to do something like:

<?php
foreach ($_POST['each_item_whatever...'] as $value) {

// Database stuff

}
?>

jquery sortable saving to database not working properly

In case someone lands on here, here is what worked in my case...

NOTE: I did not create prepared statements in the index.php select function. But you probably should.

index.php file:

<div id="container">
<?php
require_once 'conn.php';
$q = ' SELECT * FROM items WHERE groupId = 3 ORDER BY position ';
$result = mysqli_query($db, $q);

if ($result->num_rows > 0) {

while( $items = $result->fetch_assoc() ){
?>
<div id='sort_<?php echo $items['id'] ?>' class='items'>
<span>☰</span> <?php echo $items['description'] ?>
</div>
<?php
}
}
?>
</div>

jquery sortable file:

var ul_sortable = $('#container');

ul_sortable.sortable({
opacity: 0.325,
tolerance: 'pointer',
cursor: 'move',
update: function(event, ui) {
var post = ul_sortable.sortable('serialize');

$.ajax({
type: 'POST',
url: 'save.php',
data: post,
dataType: 'json',
cache: false,
success: function(output) {
console.log('success -> ' + output);
},
error: function(output) {
console.log('fail -> ' + output);
}
});

}
});
ul_sortable.disableSelection();

update php file:

$isNum = false;

foreach( $_POST['sort'] as $key => $value ) {
if ( ctype_digit($value) ) {
$isNum = true;
} else {
$isNum = false;
}
}

if( isset($_POST) && $isNum == true ){
require_once('conn.php');
$orderArr = $_POST['sort'];
$order = 0;
if ($stmt = $db->prepare(" UPDATE items SET position = ? WHERE id=? ")) {
foreach ( $orderArr as $item) {
$stmt->bind_param("ii", $order, $item);
$stmt->execute();
$order++;
}
$stmt->close();
}
echo json_encode( $orderArr );
$db->close();
}


Related Topics



Leave a reply



Submit