PHP + jQuery, sorting and save of list using jQuery Sortable

February 22, 2017 71 Yehor Rykhnov

Example of sorting list in PHP using jQuery Sortable. Make your project more convenient.

Preparing the database with test data for example
Display data
Connect jQuery Sortable
Download the source code

The article is not a demo (but you can download the source code), so here's gif with the end result::

Liked? Then proceed.

Preparing the database with test data for example

Structure of table with test data:

1	id	int(11)
2	name	varchar(255)
3	position	int(11)

SQL dump of table:

CREATE TABLE IF NOT EXISTS `sortable` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL,
  `position` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL query to populate the table with test data:

INSERT INTO `sortable` (`id`, `name`, `position`) VALUES
(1, 'Information note 1', 0),
(2, 'Information note 2', 1),
(3, 'Information note 3', 2),
(4, 'Information note 4', 3),
(5, 'Information note 5', 4),
(6, 'Information note 6', 5),
(7, 'Information note 7', 6),
(8, 'Information note 8', 7),
(9, 'Information note 9', 8),
(10, 'Information note 10', 9);

Data prepared for the test, now we can start.

Display data

Before proceeding to the sorting of the data we needed display data. To do this, create a file index.php with the standard html markup:

<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <title>PHP + jQuery, sorting and save of list using jQuery Sortable - devreadvritw.com</title>
</head>
<body>
</body>
</html>

To begin connect jQuery and jQuery UI to the project. Their need to download or connect from the CDN:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>

Also for beauty display of data we will use the Bootstrap. Download it (http://getbootstrap.com/getting-started/) and connect. Or connect Bootstrap from CDN:

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>

As a result, we obtain:

<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <title>PHP + jQuery, sorting and save of list using jQuery Sortable</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
</head>
<body>
</body>
</html>

Before the display data of the list you need to make a connection to the database in a separate file, db_connection.php:

<?php

$link = mysqli_connect(
    'localhost',
    'root',
    '',
    'db_name');

if (!$link) {
    printf("Unable to connect to the database. Error code: %s\n", mysqli_connect_error());
    exit;
}

Now we can proceed directly to the display data of list, and design of appearance:

<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <title>PHP + jQuery, sorting and save of list using jQuery Sortable</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
</head>
<body>

<?php
include('db_connection.php');
?>

<div class="container">
    <div class="jumbotron">
        <h1>PHP + jQuery</h1>
        <p>Sort the list by using the jQuery Sortable</p>
    </div>

    <?php
    if ($result = mysqli_query($link, 'SELECT * FROM sortable ORDER BY position')) {
        ?>
        <ul class="list-group">
            <?php
            while ($row = mysqli_fetch_assoc($result)) {
                echo '<li class="list-group-item">' . $row['name'] . '</li>';
            }
            ?>
        </ul>
        <?php
        mysqli_free_result($result);
    }

    mysqli_close($link);
    ?>

</div>
</body>
</html>

That's what we get:

Connect jQuery Sortable

Now initialize the sortable plugin, add button the "Save sort" to send and store data in the database, sending data about sorting in a file save.php to stored in DB and informer of the state of affairs.

Add button the "Save sort order":

<button class="save btn btn-success">Save sort order</button>

Add a block with information about saving:

<div class="alert alert-success" id="response" role="alert">Sort and save :)</div>

Before initiating jQuery Sortable add the tag <ul> class "sortable":

<ul class="list-group sortable">
//...
</ul>

And to list <li> add the record id to identify it:

<?php
//...
echo '<li class="list-group-item" id=item-' . $row['id'] .'">' . $row['name'] . '</li>';

Now we can execute jQuery Sortable initialization. Sending about sorting data to be stored in save.php file and output the information in the response unit:

<script type="text/javascript">
    var ul_sortable = $('.sortable');

    ul_sortable.sortable({
        revert: 100,
        placeholder: 'placeholder'
    });

    ul_sortable.disableSelection();

    var btn_save = $('button.save'),
        div_response = $('#response');

    btn_save.on('click', function(e) {
        e.preventDefault();
        var sortable_data = ul_sortable.sortable('serialize');
        div_response.text('Save');
        $.ajax({
            data: sortable_data,
            type: 'POST',
            url: 'save.php',
            success:function(result) {
                div_response.text(result);
            }
        });
    });
</script>

To improve the appearance of sorting we need add and connect style.css file with the following content:

ul.sortable {
    width: 100%; 
    float: left; 
    margin: 20px 0; 
    list-style: none; 
    position: relative !important;
}

ul.sortable li {
    cursor: move;
}

ul.sortable li.ui-sortable-helper {
    border-color: #3498db;
}

ul.sortable li.placeholder {
    height: 50px; 
    background: #eee; 
    border: 2px dashed #bbb; 
    display: block; 
    opacity: 0.6;
    border-radius: 2px;
    -moz-border-radius: 2px;
    -webkit-border-radius: 2px;
}

As a result, the index.php file will be:

<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <title>PHP + jQuery, sorting and save of list using jQuery Sortable</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.0/jquery-ui.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
    <link href="css/style.css" rel="stylesheet">
</head>
<body>

<?php
include('db_connection.php');
?>

<div class="container">
    <div class="jumbotron">
        <h1>PHP + jQuery</h1>
        <p>Sort the list by using the jQuery Sortable</p>
    </div>
    <button class="save btn btn-success">Save sort order</button>
    <br />
    <br />
    <br />
    <div class="alert alert-success" id="response" role="alert">Sort and save :)</div>

    <?php
    if ($result = mysqli_query($link, 'SELECT * FROM sortable ORDER BY position')) {
        ?>
        <ul class="list-group sortable">
            <?php
            while ($row = mysqli_fetch_assoc($result)) {
                echo '<li class="list-group-item" id=item-' . $row['id'] .'">' . $row['name'] . '</li>';
            }
            ?>
        </ul>
        <?php
        mysqli_free_result($result);
    }
    mysqli_close($link);
    ?>

</div>

<script type="text/javascript">
    var ul_sortable = $('.sortable');

    ul_sortable.sortable({
        revert: 100,
        placeholder: 'placeholder'
    });

    ul_sortable.disableSelection();

    var btn_save = $('button.save'),
        div_response = $('#response');

    btn_save.on('click', function(e) {
        e.preventDefault();
        var sortable_data = ul_sortable.sortable('serialize');
        div_response.text('Сохраняем');
        $.ajax({
            data: sortable_data,
            type: 'POST',
            url: 'save.php',
            success:function(result) {
                div_response.text(result);
            }
        });
    });
</script>
</body>
</html>

It remains to add save.php file which is responsible for save data about the record position in the list:

<?php

if (isset($_POST)) {
    include('db_connection.php');
    $arrayItems = $_POST['item'];
    $order = 0;

        foreach ($arrayItems as $item) {
            $sql = "UPDATE sortable SET position='$order' WHERE id='$item'";
            mysqli_query($link, $sql);
            $order++;
        }

    echo 'Сохранено!';
    mysqli_close($link);
}

That's what we get in the end:

I think of the code is clear. If you are not using Bootstrap, you can change the style to his, still the code will work. Enjoy your use!

PHP + jQuery, sorting and save of list using jQuery Sortable

Preparing the database with test data for example

Display data

Connect jQuery Sortable

Download the source code