...

View Full Version : PHP/MYSQL Reorder



tsidell
12-02-2008, 05:29 PM
I am trying to create a news system backend for a user where they can go in, add new news, delete news, set status of the news, and rearrange the order the news should appear. Once they click on Publish it will send an email to the administrator with the code to copy and paste into the CMS.

I am having trouble with the move/rearrange function. How would I do a simple move, that when the user clicks on the move up button it rearranges that news item so that it is above the one that was previously above it and vice versa for down. I've tried many scenarios but I seem to be stuck.

CFMaBiSmAd
12-02-2008, 05:36 PM
Simplest way -

You need a "position" column in your database that holds the intended display position. Each click of a "move-up" or "move-down" button causes the value in the "position" column for the current item to be exchanged with the value of the correct (up or down) neighboring item.

tsidell
12-02-2008, 05:41 PM
Thanks CFMaBiSmAd for the quick response. I understand that part but the part where I'm stuck now is how to initially generate the position of the news item. For testing purposes I created a position column. How do I set up the position initially so that it should be 1, 2, 3, 4, 5, 6, 7, 8 instead of the default of zero. Thanks.

tsidell
12-02-2008, 05:45 PM
Do I do auto increment in the database for that field? But if I do that wont I run into a problem since they can delete news items.

tsidell
12-02-2008, 05:49 PM
Also, I have newsid as a key. It wont allow me to auto increment position since newsid is already auto incremented

CFMaBiSmAd
12-02-2008, 05:58 PM
For an existing table and you are the only one making changes to the data, I would just write some php code to execute UPDATE queries in a loop with a counter. If you are really good at sql (I'm not) you could probably use a temporary sql variable as a counter to do it all in one query.

For inserting new items, I would INSERT the item and read the last insert id (assuming you have an autoincrement index) and put that value into the position column too. You can use triggers to cause this to happen automatically - http://dev.mysql.com/doc/refman/5.0/en/triggers.html This will place new items at the end. An alternative would be to reserve the first position for new items so that they would automatically appear at the start of the position list.

tsidell
12-02-2008, 06:08 PM
I wrote a query

$positionquery = mysql_query("UPDATE news SET position = position+1");


but it updates every news item with the same position. How do I accomplish the feat of having it update each particular news item separately.

tsidell
12-02-2008, 06:53 PM
I'm making progress, I got the position function to work but how would I get the move up to work.

the move.php file I have is:

include ("connect.php");
$update= $_GET['newsid'];
$movequery = mysql_query("UPDATE news SET position = position + 1 WHERE newsid = '$update'");
$movequery2 = mysql_query("UPDATE news SET position = position - 1 WHERE newsid = '$myrow[position]-1'");

it does nothing...

CFMaBiSmAd
12-02-2008, 07:17 PM
to be exchanged with the value of the correct (up or down) neighboring item.

I recommended that you exchange values because when rows have been deleted incrementing and decrementing the position values won't work.

CFMaBiSmAd
12-02-2008, 09:15 PM
I have a correction to some of the above, triggers cannot be used to UPDATE a value in the row just INSERTED and since the last insert id is not available until after the INSERT, doing what I suggest would require an INSERT followed by an UPDATE of the same row.

CFMaBiSmAd
12-03-2008, 12:30 AM
Here is some basic move-up/move-down code (Disclaimer: Your mileage may vary, depending on how you drive... :D ) -


// $_GET['action'] will indicate up/dn
$action = isset($_GET['action']) ? $_GET['action'] : false; // condition input and set default
// $_GET['id'] is the id to move up/dn
$id = isset($_GET['id']) ? (int)$_GET['id'] : false; // condition input and set default
if(!$id){
die("No id supplied");
}
if($action <> "up" && $action <> "dn"){
die("Invalid action selected");
}

// get the current position of the id
$query = "SELECT position FROM $table_name[0] WHERE id = $id";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
if(mysql_num_rows($result) <> 1){
echo "There is no id: $id<br />";
} else {
list($get_position) = mysql_fetch_row($result); // get the position of the requested id
if($action == "up"){
// get the id and position of that and the next lower position
$query = "SELECT id, position FROM $table_name[0] WHERE position <= $get_position ORDER BY position DESC LIMIT 2";
} else {
// get the id and position of that and the next higher position
$query = "SELECT id, position FROM $table_name[0] WHERE position >= $get_position ORDER BY position LIMIT 2";
}
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
// test how many rows (if less than 2, there is no locaiton to move to)
if(mysql_num_rows($result) < 2){
echo "You are already at the limit<br />";
} else {
// move up/dn one by swapping the position values
$row1 = mysql_fetch_assoc($result); // row1
$row2 = mysql_fetch_assoc($result); // row2
// update row1 (with row2's position value)
$query = "UPDATE $table_name[0] SET position = {$row2['position']} WHERE id = {$row1['id']}";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
// update row2 (with row1's position value)
$query = "UPDATE $table_name[0] SET position = {$row1['position']} WHERE id = {$row2['id']}";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
}
}

ScoobyDooobyD00
12-18-2008, 12:15 AM
Thanks CFMaBiSmAd for the code! Extremely helpful!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum