Ordering Articles

[dachshund]

Hi everyone,

 

I'm making an admin page which will contain a number of articles. Unlike most sites, these articles won't be ordered based on date, but however the admin wishes.

 

To do this I've added an order column to the mysql, in the admin page all the articles are displayed and next to them a text input, with the value set to their current order number (1,2,3, 4 etc)

 

Here's selections of the code

 

<?php

$o = $_GET['o'];

if($o == 'new')	{

$order1 = $_POST['order1'];

$update = "UPDATE `content` SET `order` = '$order1' WHERE `id` = '1'";

$result=mysql_query($update) or die(mysql_error());

}
?>

<form method="post" action="?o=new" name="order_form">

<!-- CONTENT -->

<div id="content">
<?php	
$sql="SELECT * FROM `content` ORDER BY `order` ASC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
?>
	<ul>
		<li class="article_left">
		<input type="text" value="<?php echo $rows['order']; ?>" name="order<?php echo $rows['id']; ?>" class="order">
		</li>
		<li class="article_left_title">
		<?php echo $rows['title']; ?>
		</li>
		<li <?php if ($rows['live'] == '1') { echo 'class="live"'; }else echo 'class="launch"'; ?>>
		<?php if ($rows['live'] == '1') {
		echo 'Now Live';
		}else	{
		echo 'Launch';
		}
		?>
		</li>
		<div class="clear"></div>
	</ul>
<?php
}
?>
</div>

<!-- END CONTENT -->

<div id="submit_order">
<input type="submit" name="submit" value="Submit Re-Order">
</div>

</form>

 

My problem is hard to explain, but as you can tell $update only does it for the first article. How can I make it change the `order` column for all the articles.

 

Feel free to ask more questions. Would really appreciate any help!

 

[Rifts]

in your mysql statement you need to do(" SELECT * FROM table ORDER BY order DESC")

[phil88]

The problem, as you seem to be aware is fetching the order and the ID of the article that order applies to from the $_POST var. orderx.

 

You could do something along the lines of;

$query = mysql_query("SELECT id FROM ..."); // Get all the IDs that are to be ordered
while($row = mysql_fetch_assoc($query)){
    $artId = $row['id'];
    // Get POSTed order value
    $order = $_POST['order'.$artId];
    $update = "UPDATE `content` SET `order` = $order WHERE `id` = $artId";
    mysql_query($update);
}

 

Not secure or efficient, but I hope it's enough to get you started (:

[dachshund]

thanks! looks good.

 

in the first line what do you mean by ("SELECT id FROM ...")

 

what should the dots be replaced with?

[phil88]

Whatever the query is that will get the IDs of all the articles that are shown to the user. It'll probably be;

SELECT id FROM `content`

[Psycho]

So, there is an explicit ordering of the records. That means when the order of one record is updated, you will need to update the order of one or more other records as. Therefore, when you get the new order index for a record you will need to first query for the current order index and then update multiple records.

 

Example

1. Record A

2. Record B

3. Record C

4. Record D (Change to position 2)

5. Record E

 

In the above scenario you would query the current position of Record D (4), update that record's position AND update the position for all records from the new postion to record D's original position. You will need to figure out the logic for

 

Of course the logic is different based upon whether the record is moving up or down

 

Psuedo code

$recordID    = mysql_real_escape_string($_GET['recordID']);
$newPosition = mysql_real_escape_string($_GET['position']);

//Get records current position
$query = "SELECT position FROM records where id = {$recordID}";
$result = mysql_query($query);
$oldPosition = mysql_result($result, 0);

if($newPosition<$oldPosition)
{
    $movement = 1;
    $movementStart = $newPosition;
    $movementend   = $oldPosition;
}
else
{
    $movement = -1;
    $movementStart = $newPosition;
    $movementend   = $oldPosition;
}

//Move all the affected records
$query = "UPDATE records
          SET order = order+$movement
          WHERE order >= $movementStart AND order <= $movementend"
$result = mysql_query($query);

//Move the specific record to its new position
$query = "UPDATE records
          SET order = $newPosition
          WHERE id = $oldPosition"
$result = mysql_query($query);

[Psycho]

Just noticed there was an error in the last variable of the last query. It should look like this

$query = "UPDATE records
          SET order = $newPosition
          WHERE id = $recordID"

 

Also, I would add some validation logic to the code to ensure the new position is valid (an integer, not less than 1, and not greater than the number of current records).

[Adam]

Very elegant solution, mjdamato.

 

@asurfaceinbetween

 

You can also insert a new article at a specific position by updating all articles >= to that position, as position = position + 1..

 

update articles set position = position + 1 where position >= {$new_article_position}

 

Then you just need to insert your article at that position as normal. A similar method can be used for deleting an article, except you just need to reverse the logic.