PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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.
<?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();
?>
<?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.