Page 1 of 1

Up/Down order mySQL table entries doesn't work

Posted: Tue Mar 02, 2010 5:45 am
by Sindarin
I am trying to make an ordering system for an article system

It will look like this:

article title | move up - move down

move up and down would re-sort the article order by swapping the row_order values, at least that was my idea.
For some reason the current row updates but the next (or previous) doesn't.

Code: Select all

 
 
<?php
 
/* include the class file for the database connection */
require_once('sql-database-class.php');
$db = new sqlDatabase('mysql','utf8','localhost','test');
$db->sqlConnect('root','');
 
if ($_GET['action']=='sort')
{
    $row_id = $_GET['row_id'];
    $row_order = $_GET['row_order'];
    $go = $_GET['go'];
 
    if ($go == 'up')
    {
        //get previous item order
        $row_order2 = $row_order-1;
        $db_result3 = $db->sqlQuery("SELECT * FROM `articles` WHERE row_order='$row_order2'");
        while ($row = $db->sqlFetch($db_result3))
        {
            $row_order2 = $row['row_order'];
        }
        
        //DEBUG 
        echo 'current item order was '.$row_order.' and now will be '.$row_order2.'<br/>
        the previous item order was '.$row_order2.' and it will be '.$row_order.'<br/>';
        
        //swap orders
        $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order' WHERE `row_order`='$row_order2' ");
        $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order2' WHERE `row_order`='$row_order' ");
        
        echo 'sorted<br/><br/>'.$db->sqlError();
        
    }
    elseif ($go == 'down')
    {
        //get next item order
        $row_order2 = $row_order+1;
        $db_result3 = $db->sqlQuery("SELECT * FROM `articles` WHERE row_order='$row_order2'");
        while ($row = $db->sqlFetch($db_result3))
        {
            $row_order2 = $row['row_order'];
        }
        
        //DEBUG 
        echo 'current item order was '.$row_order.' and now will be '.$row_order2.'<br/>
        the next item order was '.$row_order2.' and it will be '.$row_order.'<br/>';
        
        //swap orders
        $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order' WHERE `row_order`='$row_order2' ");
        $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order2' WHERE `row_order`='$row_order' ");
        
        echo 'sorted<br/><br/>'.$db->sqlError();
        
    }
    
}
 
$db_result1 = $db->sqlQuery("SELECT * FROM `articles` ORDER BY `row_order`");
 
/* return field values sorted by row_order */
while ($row = $db->sqlFetch($db_result1))
{
    echo $row['row_title'].' ['.$row['row_order'].'] 
    <form style="display:inline" action="index.php" method="get">
        <input type="hidden" name="action" value="sort" />
        <input type="hidden" name="go" value="up" />
        <input type="hidden" name="row_id" value="'.$row['row_id'].'" />
        <input type="hidden" name="row_order" value="'.$row['row_order'].'" />
        <input type="submit" value="up" />
    </form> 
    <form style="display:inline" action="index.php" method="get">
        <input type="hidden" name="action" value="sort" />
        <input type="hidden" name="go" value="down" />
        <input type="hidden" name="row_id" value="'.$row['row_id'].'" />
        <input type="hidden" name="row_order" value="'.$row['row_order'].'" />
        <input type="submit" value="down" />
    </form>
    <br/>
    ';
}
 
/* close connection to the database */
$db->sqlDisconnect();
 
?>
 
 

Re: Up/Down order mySQL table entries doesn't work

Posted: Fri Mar 05, 2010 9:43 am
by Sindarin
Finally got it to work, I had to change UPDATE queries to look for row_id so I am posting the code if anyone's going to benefit from it:

Code: Select all

 
<?php
 
/* ORDER EXAMPLE */
 
/* include the class file for the database connection */
require_once('sql-database-class.php');
$db = new sqlDatabase('mysql','utf8','localhost','test');
$db->sqlConnect('root','');
 
if ($_GET['action']=='sort')
{
    $row_id = $_GET['row_id'];
    $row_order = $_GET['row_order'];
    $go = $_GET['go'];
 
    if ($go == 'up')
    {
        //get previous item order
        $row_order2 = $row_order-1;
        
        if($row_order==1)
        {
            echo 'reached beginning';
        }
        else
        {
            $db_result3 = $db->sqlQuery("SELECT * FROM `articles` WHERE `row_order`='$row_order2'");
            while ($row = $db->sqlFetch($db_result3))
            {
                $row_order2 = $row['row_order'];
                $row_id2 = $row['row_id'];
            }
        
            //swap orders
            $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order' WHERE `row_id`='$row_id2' LIMIT 1");
            $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order2' WHERE `row_id`='$row_id' LIMIT 1");
        }
        
    }
    elseif ($go == 'down')
    {
        $row_order2 = $row_order+1;
            $db_result4 = $db->sqlQuery("SELECT * FROM `articles`");
            $max = $db->sqlNumRows($db_result4);
            //$max=$max+1;
        if ($row_order2>$max)
        {
            echo 'reached end';
        }
        else
        {
            //get next item order
            $db_result4 = $db->sqlQuery("SELECT * FROM `articles` WHERE `row_order`='$row_order2'");
            while ($row = $db->sqlFetch($db_result4))
            {
                $row_order2 = $row['row_order'];
                $row_id2 = $row['row_id'];
            }
            
            //swap orders
            $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order' WHERE `row_id`='$row_id2' LIMIT 1");
            $db->sqlQuery("UPDATE `articles` SET `row_order`='$row_order2' WHERE `row_id`='$row_id' LIMIT 1");
        }
        
    }
    
}
 
echo '<br/><div style="width:900px;height;auto;">';
 
$db_result1 = $db->sqlQuery("SELECT * FROM `articles` ORDER BY `row_order`");
$max = $db->sqlNumRows($db_result1);
 
/* return field values sorted by row_order */
while ($row = $db->sqlFetch($db_result1))
{
    
    $row_order = $row['row_order'];
 
    echo '<div style="width:200px;float:left;position:relative">'.$row['row_title'].' ['.$row['row_order'].'] </div>';
    echo '<div style="width:200px;background-color:#d9d9d9;padding:3px;height;32px;">';
    
    if ($row_order>1)
    {
    echo '
    <form style="display:inline" action="index.php" method="get">
        <input type="hidden" name="action" value="sort" />
        <input type="hidden" name="go" value="up" />
        <input type="hidden" name="row_id" value="'.$row['row_id'].'" />
        <input type="hidden" name="row_order" value="'.$row['row_order'].'" />
        <input type="submit" value="up" />
    </form> ';
    }
    else
    {
        echo '<input type="button" value="up" disabled="disabled" />';
    }
    
    if ($row_order<$max)
    {
    echo '
    <form style="display:inline" action="index.php" method="get">
        <input type="hidden" name="action" value="sort" />
        <input type="hidden" name="go" value="down" />
        <input type="hidden" name="row_id" value="'.$row['row_id'].'" />
        <input type="hidden" name="row_order" value="'.$row['row_order'].'" />
        <input type="submit" value="down" />
    </form> ';
    }
    else
    {
        echo '<input type="button" value="down" disabled />';
    }
    
    echo '</div><br/>';
}
 
echo '</div>';
 
/* close connection to the database */
$db->sqlDisconnect();
 
?>
 
This does not work correctly if you give the user the option to change the order by number because there would be duplicate row_order values (I assume they need some kind of defragmentation) but this will work okay for small lists.