SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    jQuery/PHP/MySQL - Reorder List, Save Results

    This is a combination of jQuery and a PHP problem so I'm not 100% certain where to post it.

    I am working on method to reoder a list that comes from a database. When the lists is displayed, it will be ordered by a "sort" column. The user can drag/drop the list to change the order, and I want the order to update the sort values in the database.

    I found a script that provides a decent starting point, but I am not getting the results I want.

    1: It is able to reorder the columns, and save the results which is good.
    2: It is saving the results as "0,1,2,3" rather than reordering the numbers from the "sort" column. (< this is what I need fixed)

    EG: Name > Sort
    Apple 3
    Orange 5
    Pear 13
    Kiwi 105

    If I reorder these in the reverse order using the script, I end up with the following in the database.
    Apple 3
    Orange 2
    Pear 1
    Kiwi 0

    Yet what I want is,
    Apple 105
    Orange 13
    Pear 5
    Kiwi 3

    Are there any suggestions how I can get it to work with the existing "sort" values, rather than serializing and starting at 0.

    Here are the script files I am currently working with for testing.

    DATABASE
    Code:
    CREATE TABLE `menu` (
      `id` int(11) NOT NULL auto_increment,
      `title` varchar(100) default NULL,
      `sort` int(2) NOT NULL,
      PRIMARY KEY  (`id`)
    );
    
    INSERT INTO `menu` (`id`, `title`, `sort`) VALUES 
    (1, 'Apple', 3),
    (2, 'Orange', 5),
    (3, 'Pear', 13),
    (4, 'Kiwi', 105);
    MAIN FILE (menu_list.php)
    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Sorting Items on the fly using jQuery UI, PHP & MySQL</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.11/jquery-ui.min.js"></script>
    <script>
    $(document).ready(
    function() {
    $("#sortme").sortable({
    update : function () {
    serial = $('#sortme').sortable('serialize');
    $.ajax({
    url: "sort_menu.php",
    type: "post",
    data: serial,
    error: function(){
    alert("theres an error with AJAX");
    }
    });
    }
    });
    }
    );
    </script>
    </head>
    <body>
    <h1>Menu List</h1>
    
    <ul id="sortme">
    <?php
    // Connecting to Database
    mysql_connect("localhost", "username", "password") or die ('Cant Connect to MySQL');
    
    // Selecting Database
    mysql_select_db("database_name") or die ('Cant select Database');
    
    // Getting menu items from DB
    $result = mysql_query("SELECT * FROM `menu` ORDER BY `sort` ASC") or die(mysql_error());
    while($row = mysql_fetch_array($result)) {
    echo '<li id="menu_' . $row['id'] . '">' . $row['title'] . "</li>\n";
    }
    ?>
    </ul>
    </body>
    </html>
    UPDATE SCRIPT (sort_menu.php)
    Code:
    <?php
    // Connecting to Database
    mysql_connect("localhost", "username", "password") or die ('Cant Connect to MySQL');
    
    // Selecting Database
    mysql_select_db("database_name") or die ('Cant select Database');
    
    $menu = $_POST['menu'];
    for ($i = 0; $i < count($menu); $i++) {
    mysql_query("UPDATE `menu` SET `sort`=" . $i . " WHERE `id`='" . $menu[$i] . "'") or die(mysql_error());
    }
    ?>

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This problem has been solved thanks to another script I found.

    I haven't been able to analyze the differences yet (same set of tools "jQuery, PHP, MySQL") but I have found a solution.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •