question about sorting

[imi_99]

Hi,

 

I am bit stuck with php sorting.

 

I have category list which are in order form like 1,2,3,4.....9,10,11 and is stored into datbase.

 

what i want to do is change any number's position. e.g if i change number 9 to number 3 into database then result will be like 1,2,3,3,4.....9,10,11

 

how can i sort this to that new number 3 would become 3 and already 3 number change into 4, then next 4 into 5 and so on till 11. mean it should save into datbase with this new order

 

how can i sort this with new order and then save into database. thanks

Edited March 26, 2013 by imi_99

[trq]

We do this all the time using some form of client side sorting mechanism.

 

jQuery UI has a decent implementation, see http://jqueryui.com/sortable.

 

With this type of UI you can simple name your elements foo[1], foo[2], foo[3] etc etc

 

Whenever the order is changed, you send all elements in the (new) order in which they appear on the screen to a php script which updates each element's "sequence" column as required.

[fife]

I have just solved this for myself with Jquery UI.  If you need a hand let me know.  I can send you a copy of my files so you can see for yourself how its done

[imi_99]

thanks for your replies,

 

i am not a good coder in jquery. i realy want to do it server side to update database.

 

i wonder there should be query who can update the database e.g

 

if i switch 3 into new position on number 7 then new result will be like  1,2,4....7(new), 7(old), 8, .9,10,11 . is n't any command to rewrite again this from 1 to 11. thanks

[trq]

You need some method of telling the database what order things are going to be in.

 

Not being a very good "coder" in jQuery doesn't really cut it I'm afraid. Do you want to get this done or not?

[imi_99]

hmm, you probably right . thanks trq.

 

can fife you paste the code here. thanks

[fife]

bare with me ill do it now for you

[fife]

ok so download the jquery UI but you only want the sortable plugin and its extensions. 

 

 

sample database structure,

 

customerID,

name,

custOrder

 

 

 

on your main page create something like below

 

//so in your header link out to your jquery UI files
 <script src="/js/sort/js/jquery-ui-1.9.2.custom.js" type="text/javascript"></script><script src="/js/sort/js/touch.js" type="text/javascript"></script><script type="text/javascript">    $(function() {        $("#sortable").sortable({opacity:0.6,            stop: function(i) {                $.ajax({                    type: "GET",                    url: "sortable.php",                    data: $("#sortable").sortable("serialize"),success: $("#successorder").show().delay(1000).fadeOut('slow')});             }                                                               });        $("#sortable").disableSelection();    });</script>
 
 
 
 
//first your query for the customers
 mysql_select_db($database_dbconnet, $dbconnet);$query_rs_page = sprintf("SELECT * FROM customers ORDER BY custOrder ASC");$rs_page = mysql_query($query_rs_page, $dbconnet) or die(mysql_error());$row_rs_page = mysql_fetch_assoc($rs_page);
 
 
// then on the page
 
  <ul id="sortable">          <?php   while($row = mysql_fetch_array($rs_page)){         echo "<li id='item_{$row['customerID']}'>{$row['name']}</li>";    }?>         </ul>
 
 
 

 

thats you main page.  see how you echo the customer id into the item array?  Thats what you want to parse to the next page.  Create your self a new blank page with a link to your database.  Call this page sortable.php to match what is in your header and make sure its in the same folder. On this page you wan to create a query which updates the custOrder in the customer table.

 

 foreach($_GET['item'] as $key=>$value) {mysql_select_db($database_dbconnet, $dbconnet);    mysql_query("UPDATE customers SET `custOrder`='".intval($key)."' WHERE `customerID`='".intval($value)."'", $dbconnet) or die(mysql_error());}

 

Thats basically it.  JqueryUI handles the rest for you.  When you have a list of customers you will now be able to select one and drag it into a new order.  Jquery UI will then call the sortable.php and update the custOrder.  As the query on the main page pulls results in order of custOrder ASC each time you load the page the new order will be saved.

 

 

I hope that helps.  Not the best coder myself but I managed to stumble through the UI tuts.  PM me if you do understand anything and good luck

[fife]

dont understand i mean

[imi_99]

Thanks fife,

 

I will let you know in case of any issue.

[davidannis]

I have implemented the function using jquery but I understand the desire to do it server side, both as a learning exercise and because I've heard that it is good to provide an alternative to those who are not willing/able to run javascript.

 

FWIW here is the code I used in my project:

        <script type='text/javascript' src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
        <script type='text/javascript'>//<![CDATA[ 

            $(document).ready(function(e) {
                 $("#sortable").sortable({
                    'containment': 'parent',
                    'opacity': 0.6,
                    update: function(event, ui) {
                        var info = $(this).sortable("serialize");
                        $.ajax({
                            type: 'GET',
                            url: 'process_sort_competitors.php',
                            async: false,
                            data: info,
                            success: function(data) {
                                alert(data);
                            }
                        });
                    }
                });
            });
            //]]>  

        </script>

server side:

$table_name='competitors';
foreach ($_GET['listItem'] as $position => $item)
{
    $position=  mysqli_real_escape_string($link, $position);
    $item=mysqli_real_escape_string($link, $item);
    $query = "UPDATE `$table_name` SET `print_order` = $position WHERE `competitor_id` = $item AND `company_id`='".$_SESSION['company_id']."'";
    $result=(mysqli_query($link, $query));
    //if (!$result) $message .= "$item not updated.\n.$query\n" ; else $message.="$item is now in position $position\n";
    if (!$result) $message .= "$item not updated.\n" ; else $message.="$item is now in position $position\n";
}

    echo 'Competitor Print Order Updated'."\n".$message; 

I can provide a link to the working code if you want it.

[imi_99]

that looks fantastic, thanks davidannis , i am not good in object oriented php but will try to figure this out with my coding. thanks

[Barand]

To avoid running queries in a loop

 

function moveCustomer ($db, $cid, $newpos)
    /**
    * move customer x to position N
    */
{
    // get current position
    $sql = "SELECT custOrder 
            FROM customer 
            WHERE custid=$cid";
    $res = $db->query($sql);
    if ($row = $res->fetch_row()) {
        $oldpos = $row[0];
    }
    else return;
    if ($newpos==$oldpos) return;
    
    if ($newpos < $oldpos) {
        $sql = "UPDATE customer
                SET custOrder = custOrder+1
                WHERE custOrder BETWEEN $newpos AND $oldpos";
        $db->query($sql);
    } else {
        $sql = "UPDATE customer
                SET custOrder = custOrder-1
                WHERE custOrder BETWEEN $oldpos AND $newpos";
        $db->query($sql);       
    }

    $sql = "UPDATE customer 
            SET custOrder = $newpos
            WHERE custid = $cid";
    $db->query($sql);
   
}

moveCustomer($db, 9, 3);   // move customer with id=9 to position 3

[davidannis]

Thank you Barand. That is an elegant way to make the code more efficient.

[davidannis]

Barand,

 

I've been thinking about your solution and I think that it would also require rewriting the jQuery code to not use the sortable / serialize methodology. Here's why:

jQuery's serialize gives us an array of id's sorted by order in the list. So, if we start with a list of id's 0 through 10 in order and we move #9 to #3 we get back an array in $_POST['listitems'] that looks like this (0=>0, 1=>1, 2=>2, 3=>9, 4=>3, 5=>4, 6=>5, 7=>6, 8=>7 9 =>8, 10=>10)

 

Now in my script to process the results I have no way to know that 9 was moved to 3 short of going through the entire array. The resulting array I received is indistinguishable from a starting array of (0=>0, 1=>1, 2=>2, 3=>9, 4=>4, 5=>3, 6=>5, 7=>6, 8=>7 9 =>8, 10=>10) where id=3 was moved up one notch for example.