I have an ul & li and I've made this sortable with jquery. I am able to get the array of it by doing the following:
update: function () {
var data = $(this).sortable('toArray');
console.log(data);
}
In Firebug, I see the following output:
["experience", "skills", "about-yourself", "teaching-experience", "education", "languages", "publications", "patents", "talks", "certifications", "awards"]
Can I store this as it is in the mysql, or how shall this exactly be stored?
Shall the DB Table be like this:
sort_order_table
id AUTOINCREMENT,
Order_array,
userID,
Any help is appreciated, thanks a lot.
EDIT: I PLAN TO RELOAD THE STORED ORDER AND RENDER THE LIST FOR THE USER.
You could store it as JSON in the database, but that has a few drawbacks. For example, if you'd want to get the count of the JSON Array, you'll need to get the row, parse the JSON and then count the elements. I would usually insert the values into a database table.
Table example:
CREATE TABLE my_sort (
id integer NOT NULL AUTO_INCREMENT,
user_id integer, -- is this order associated with an user?
value varchar(255)
);
When you send the request to save sorted data, in the php scrip you'd have something like this:
INSERT INTO my_sort(user_id, value) VALUES(1, 'experience');
INSERT INTO my_sort(user_id, value) VALUES(1, 'skills');
-- and so on
Afterwards, when selecting the data to output it in your UI, you would simply do:
SELECT value FROM my_sort ORDER BY id ASC;
And then convert it into a JSON object if necessary.
In your javascript, you would need to have something similar to this:
$.ajax({
url: 'http://link.com/to/your/script.php',
type: 'POST',
data: {
sortables: $('#sortable').sortable('toArray')
}
})
In the script.php you would have something similar to this:
<?php
// this will contain an array with all of your sortables in the order they were
$sortables = $_POST['sortables'];
// delete all elements saved for the current user. I assume your user_id is stored in the session - if it's not, change $_SESSION['user_id'] to the variable that holds the user_id
// DELETE FROM my_sort WHERE user_id = $_SESSION['user_id'];
foreach($sortables as $sortable) {
// here you would execute the INSERT.
// INSERT INTO my_sort(user_id, value) VALUES($_SESSION['user_id'], $sortable);
}
?>
The code above is not tested, but I hope you get the idea.
Updating my answer based on our discussion.
You have:
Pseudo-code db schema:
values table: `value_id`, `value_name`
userSort table: `user_id`, `value_id`, `sort_order`
Sample query to load the data:
select value_id, value_name, sort_order from values
join userSort on userSort.value_id = values.value_id
order by sort_order, value_name
Example html (using the results of the above query to populate):
<ul>
<li>Item 1</li>
<li>Item 2</li>
<li>Item 3</li>
</ul>
Example javascript:
// when you detect the user has sorted, post the values to the server:
$.post('yourScript.php', { data: $(this).sortable('toArray') } );
i.e. in pseudo code:
query('delete from userSorts where user_id = ' . (int) $user_id);
int i = 0;
for( item in data ){
query('insert into userSorts (user_id,value_id,sort_order) VALUES ( $user_id, "item.value_id", i ) );
i++
}
You'll need to adapt this for you chosen database and server-side language, and you should ensure your javascript is sending the value_id as well as the value, otherwise you'll need to do some sort of lookup on the server end.