Page 1 of 1

Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 6:47 am
by fatherBoard
Hi. I have a problem with dynamic table sorting.
So, I have a table like this:
*******************
id | name | position
-----|---------|---------
1 | record1 | 1
24 | record | 2
45 | record | 3
122 | record | 4
*******************

I need to change the field values with places, for example
id 122 to position 3 and id 45 to position 4.

I tried to do so
-------------------------

Code: Select all

mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` < ". $id);
------------------------

but it's not working as needed. Help me, please. (and sorry for my english )

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 6:52 am
by superdezign
The issue is that you are looking at the `id` as your condition instead of the `position`. When you change the `position`, the `id` does not change.

Code: Select all

UPDATE `data` SET `position` = `position` - 1 where `id` = $id;
UPDATE `data` SET `position` = `position` + 1 where `id` != $id AND `position` >= $new_position_of_$id;
You would get $new_position_of_$id from a SELECT query.

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 7:03 am
by fatherBoard
but how can I set $new_position_of_id ?

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 7:05 am
by fatherBoard
I need a result page working like this http://move.inkz.ru/

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 8:35 am
by superdezign
You select it, like I said, using a SELECT query. Whether you do it before or after the UPDATE query is up to you. If you already have the information, then update it before putting it into the query instead of pulling from the database again.

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 8:53 am
by fatherBoard
but then some `position` elements become the same value.
for example:

*******************
id | name | position
-----|---------|---------
1 | record1 | 1
24 | record | 2
45 | record | 2
122 | record | 4
128 | record | 3
167 | record | 2
*******************

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 9:11 am
by superdezign
Doing what I told you makes that impossible unless your positions were already invalid. Show us the code that you are using.

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 10:36 am
by fatherBoard
original part of code:

Code: Select all

<?php

    error_reporting(E_ALL);

    include './config.php';
    
    $id = !empty($_GET['id']) ? (int)$_GET['id'] : null;
    $move = !empty($_GET['move']) ? $_GET['move'] : null;

?>

<table id="num">
    
<?php

    $table = '';

    $res = mysql_query("SELECT * FROM `data` ORDER BY `position` ASC");   

    while($row = mysql_fetch_assoc($res))
    {
        $table .= '<tr><td>'. $row ['name'] .'</td>';
        $table .= '<td><a href="?id='. $row['id'] .'&move=up"><img src="/images/arrowup.png" /></a></td>';
        $table .= '<td><a href="?id='. $row['id'] .'&move=down"><img src="/images/arrowdown.png" /></a></td></tr>';
    }

    if($id && is_int($id) && $move == 'up')
    {
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` < ". $id);
        
        header('location:http://'. $_SERVER['HTTP_HOST']);
        exit();
    }
    elseif($id && is_int($id) && $move == 'down')
    {
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` < ". $id);       
        
        header('location:http://'. $_SERVER['HTTP_HOST']);
        exit();
    }

    print $table;   
    
?>

</table>

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 11:21 am
by superdezign
You didn't implement what I said at all in this code. Show me the code that resulted in "some `position` elements becom[ing] the same value."

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 11:51 am
by fatherBoard

Code: Select all

$res = mysql_query("SELECT `position` FROM `data` WHERE `id` = ". $id);
        $row = mysql_fetch_assoc($res);
        
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` != ". $id ." AND `position` >= ". $row['position']);

Re: Dynamic mysql table sorting

Posted: Mon Apr 25, 2011 12:20 pm
by superdezign
Remember how I said "$new_position_of_id"? You are using the old position because you pull it before the position is changed and you do not update it. Just do your SELECT query after the first update instead of before it.