CODEWITHSUNDEEP
phpmysqljquery-ui
Nrc
Nrc

Reputation: 9797

Save the order of a sortable list with php and MySql

I have a list that is sortable with jQuery ui. I was able to safe the changes that the uses do and store it in a var $order. My problem is that I do not know how to save this in a MySql database. How can I safe the order in the MySql table? (Please explain from the most basic, I am just learning php)

HTML. index.html:

<ul id="sortable">
    <li id="1">item1</li>
    <li id="2">item2</li>
    <li id="3">item3</li>
</ul>

JQUERY. index.html:

$('#sortable').sortable({ 
    update: function(event, ui) { 
        var newOrder = $(this).sortable('toArray').toString(); 
        $.get('6.php', {order:newOrder};
        }); 
    }
});

PHP: 6.php:

$order = $_GET['order'];
// echo $order; // this gives something like 2,3,1

How can I safe $order in the table below using php and Mysql?
Should I change the id to give the new order? is it better to store the order in a new column? How do I pick every element in order and put in the right place of the table?

something like:

mysqli_query($con,"UPDATE MyTable 
                   SET ...
                   WHERE ... ");

MYSQL Table MyTable:

id    concept    order
-----------------------
1     item1
2     item2
3     item3

Upvotes: 2

Views: 3085

Answers (3)

Martin Andreev
Martin Andreev

Reputation: 153

You can explode the order if its only string. And when you insert it you can user the array key as a sort number.

$order = $_GET['order'];
$items = explode(',', $order);

foreach($items as $key => $item) {
// Do insert where order = $key
}

Hope this helps you :)

Upvotes: 0

Uvadzucumi
Uvadzucumi

Reputation: 11

$order=explode(',',$_GET['order']);
foreach($order as $num=>$id){
    if((int)$id!=0){
        mysqli_query("UPDATE `MyTable` SET `order`='".$num."' WHERE `id`='".(int)($id)."'");
    }
}

0) $order=explode(',',$_GET['order']); - after this $order=array(2,3,1);

1) in sql query can be used order field. need take in "`".

2) special for stop sql injections - better(and faster) method - convert coming variables to integer format (see my answer in comments for detail).

3) in foreach write in $num - variable - order number of array element. 0, 1, 2, - can be used for database records order.

Upvotes: 1

user3849602
user3849602

Reputation:

Update your database with the right order for each item :

$order = $_GET['order'];
    // echo $order; // this gives something like 2,3,1


$itemIds = explode(',', $_GET['order']);

foreach($itemIds as $priority => $itemId) {
    // Here you have to update your table, something like UPDATE items SET priority = $priority WHERE id = $itemId
}

Then the query to get all your items in the right order :

SELECT * FROM items ORDER BY priority

('order' is a reserved keyword in SQL so you should not call your field 'order', here I chose 'priority' instead.)

Upvotes: 2

Related Questions