如何将这个存储在数据库mysql PHP jquery中

Question

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.

Answer 1

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.