skyer2000 Posted January 24, 2008 Share Posted January 24, 2008 Right now I have PHP pulling a list of items from MySQL. What I need to do is to allow the user to change the order, one by one. For example: 1 2 3 4 to 1 3 4 2 I have up and down arrows for the users to click, and added a "fieldorder" column in the table. Now what steps do I take to make this happen? Any resources you can point me to? Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/ Share on other sites More sharing options...
revraz Posted January 24, 2008 Share Posted January 24, 2008 What is determining the new order? You may need a new column to sort by. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448018 Share on other sites More sharing options...
skyer2000 Posted January 24, 2008 Author Share Posted January 24, 2008 What is determining the new order? You may need a new column to sort by. The 'fieldorder' column is determining the order. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448032 Share on other sites More sharing options...
revraz Posted January 24, 2008 Share Posted January 24, 2008 What do the up/down arrows do? Insert a new value in the fieldorder? Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448066 Share on other sites More sharing options...
skyer2000 Posted January 24, 2008 Author Share Posted January 24, 2008 What do the up/down arrows do? Insert a new value in the fieldorder? This is where I'm stuck, I don't know what to do. I just need to see a working example, I can't even figure out where to start. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448084 Share on other sites More sharing options...
PHP Monkeh Posted January 24, 2008 Share Posted January 24, 2008 Well make the up/down arrows submit buttons, and name say the Up arrow as ascending, and the Down arrow as descending. Then you can put an if statement for your query which could be something like: <?php if(isset($_POST['ascending'])) { $query = "SELECT * FROM table ORDER BY fieldorder ASC"; } else if (isset($_POST['descending'])) { $query = "SELECT * FROM table ORDER BY fieldorder DESC"; } ?> That's the sort of thing you should be looking for. You may also want to include a default for the order when neither of the buttons have been hit (like when the page loads for the first time). Enjoy! Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448178 Share on other sites More sharing options...
skyer2000 Posted January 24, 2008 Author Share Posted January 24, 2008 I'm sorry for not doing a very good job explaining this, and of course when I try to find an example for people to try out, I come up short. I did find this on Facebook though that pretty much does what I'm trying to figure out: You can re-arrange the listing, not just by ASC or DESC, but the actual individual order. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448228 Share on other sites More sharing options...
PHP Monkeh Posted January 24, 2008 Share Posted January 24, 2008 Oh that's my fault I didn't read your problem correctly at all. I wrote something like this for a simple links script I made where you could add links in to each of the different categories, and I wanted to be able to edit the order of each category. Basically I have a list box with all the items in (with their values as the links ID). Once you hit Up for example, the first query will select the one you have selected, then it'll check to see whether it's the highest in the list (where you won't want to do anything as it can't go further up!). If there's a value above it, it'll increase its position by 1, and reduce the one above it by 1. Anyway here's the code of the whole page, hopefully you can understand it (there's comments so hopefully you can!). <center><b>Edit Link Category Order</b></center><br><br> <?php if(isset($_POST['up']) || isset($_POST['down'])) { // Selected line $posQuery = "SELECT id, position FROM link_cat WHERE position = '".$_POST['list']."'"; $posResult = mysql_query($posQuery) or die(mysql_error()); $posObj = mysql_fetch_object($posResult); if(isset($_POST['up'])) { // Position above selected line $moveQuery = "SELECT id, position FROM link_cat WHERE position = ".$posObj->position."+1"; $moveResult = mysql_query($moveQuery) or die(mysql_error()); $moveObj = mysql_fetch_object($moveResult); $checkPos = mysql_query("SELECT position FROM link_cat ORDER BY position DESC LIMIT 1") or die(mysql_error()); $check = mysql_fetch_object($checkPos); if($posObj->position != $check->position) { mysql_query("UPDATE link_cat SET position=position-1 WHERE id='".$moveObj->id."'") or die(mysql_error()); mysql_query("UPDATE link_cat SET position=position+1 WHERE id='".$posObj->id."'") or die(mysql_error()); } } else if (isset($_POST['down'])) { // Position below selected line $moveQuery = "SELECT id, position FROM link_cat WHERE position = ".$posObj->position."-1"; $moveResult = mysql_query($moveQuery) or die(mysql_error()); $moveObj = mysql_fetch_object($moveResult); $checkPos = mysql_query("SELECT position FROM link_cat ORDER BY position ASC LIMIT 1") or die(mysql_error()); $check = mysql_fetch_object($checkPos); if($posObj->position != $check->position) { mysql_query("UPDATE link_cat SET position=position+1 WHERE id='".$moveObj->id."'") or die(mysql_error()); mysql_query("UPDATE link_cat SET position=position-1 WHERE id='".$posObj->id."'") or die(mysql_error()); } } } $query = "SELECT id, cat_name, position FROM link_cat ORDER BY position ASC"; $result = mysql_query($query) or die(mysql_error()); ?> <form action="index.php?c=link_cat&p=editorder" method="post"> <div class="tableWrapper"> <div style="width: 300px; float: left; margin-right: 10px;"> <select class="listBox" name="list" size="8"> <?php while($r = mysql_fetch_object($result)) { echo "<option value=\"".$r->position."\">".$r->cat_name; } ?> </select> </div> <input type="submit" name="up" value="Up" class="button"><br><br> <input type="submit" name="down" value="Down" class="button"> </div> </form> Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448237 Share on other sites More sharing options...
revraz Posted January 24, 2008 Share Posted January 24, 2008 This would probably much simpler with JS or Ajax Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448241 Share on other sites More sharing options...
PHP Monkeh Posted January 24, 2008 Share Posted January 24, 2008 No doubt that's what facebook uses aswell, but I don't an example and I thought it would be best giving an outline of how it works rather than nothing Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448267 Share on other sites More sharing options...
fenway Posted January 24, 2008 Share Posted January 24, 2008 You just have to save the sortorder of all of the items each time... then just enumerate them. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448363 Share on other sites More sharing options...
revraz Posted January 25, 2008 Share Posted January 25, 2008 Yeah, I think he's looking for that logic though. Like if UP is clicked, subtract one number from where it's at but add one number to the one it's taking the place of. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448466 Share on other sites More sharing options...
fenway Posted January 25, 2008 Share Posted January 25, 2008 Yeah, I think he's looking for that logic though. Like if UP is clicked, subtract one number from where it's at but add one number to the one it's taking the place of. But you don't have to any math at all... just read them back in the "new" order, and number them from there. Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-448543 Share on other sites More sharing options...
char skd1 Posted February 5, 2008 Share Posted February 5, 2008 Oh that's my fault I didn't read your problem correctly at all. I wrote something like this for a simple links script I made where you could add links in to each of the different categories, and I wanted to be able to edit the order of each category. Basically I have a list box with all the items in (with their values as the links ID). Once you hit Up for example, the first query will select the one you have selected, then it'll check to see whether it's the highest in the list (where you won't want to do anything as it can't go further up!). If there's a value above it, it'll increase its position by 1, and reduce the one above it by 1. Anyway here's the code of the whole page, hopefully you can understand it (there's comments so hopefully you can!). I would be very interested in seeing a working version of this or even getting this as a package to look at it on a local install. Thanks Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-458998 Share on other sites More sharing options...
PHP Monkeh Posted February 5, 2008 Share Posted February 5, 2008 I don't actually have the working script/pages anymore at all You're out of luck because I lost a lot of code last week. Anyway this should work fine: <?php // CREATE THE TABLE mysql_query("CREATE TABLE link_cat ( id int(10) AUTO_INCREMENT NOT NULL, cat_name VARCHAR(45) NOT NULL, position int(10), PRIMARY kEY(id) )") or die(mysql_error()); ?> You could add any other entries to the above, those are just the key fields. Now use this script below for the actual page (this should be called index.php - if you rename it go through and change all index.php values to whatever you've called it). <center><b>Edit Link Category Order</b></center><br><br> <?php if(isset($_POST['up']) || isset($_POST['down'])) { // Selected line $posQuery = "SELECT id, position FROM link_cat WHERE position = '".$_POST['list']."'"; $posResult = mysql_query($posQuery) or die(mysql_error()); $posObj = mysql_fetch_object($posResult); if(isset($_POST['up'])) { // Position above selected line $moveQuery = "SELECT id, position FROM link_cat WHERE position = ".$posObj->position."+1"; $moveResult = mysql_query($moveQuery) or die(mysql_error()); $moveObj = mysql_fetch_object($moveResult); $checkPos = mysql_query("SELECT position FROM link_cat ORDER BY position DESC LIMIT 1") or die(mysql_error()); $check = mysql_fetch_object($checkPos); if($posObj->position != $check->position) { mysql_query("UPDATE link_cat SET position=position-1 WHERE id='".$moveObj->id."'") or die(mysql_error()); mysql_query("UPDATE link_cat SET position=position+1 WHERE id='".$posObj->id."'") or die(mysql_error()); } } else if (isset($_POST['down'])) { // Position below selected line $moveQuery = "SELECT id, position FROM link_cat WHERE position = ".$posObj->position."-1"; $moveResult = mysql_query($moveQuery) or die(mysql_error()); $moveObj = mysql_fetch_object($moveResult); $checkPos = mysql_query("SELECT position FROM link_cat ORDER BY position ASC LIMIT 1") or die(mysql_error()); $check = mysql_fetch_object($checkPos); if($posObj->position != $check->position) { mysql_query("UPDATE link_cat SET position=position+1 WHERE id='".$moveObj->id."'") or die(mysql_error()); mysql_query("UPDATE link_cat SET position=position-1 WHERE id='".$posObj->id."'") or die(mysql_error()); } } } $query = "SELECT id, cat_name, position FROM link_cat ORDER BY position ASC"; $result = mysql_query($query) or die(mysql_error()); ?> <form action="index.php" method="post"> <div class="tableWrapper"> <div style="width: 300px; float: left; margin-right: 10px;"> <select class="listBox" name="list" size="8"> <?php while($r = mysql_fetch_object($result)) { echo "<option value=\"".$r->position."\">".$r->cat_name; } ?> </select> </div> <input type="submit" name="up" value="Up" class="button"><br><br> <input type="submit" name="down" value="Down" class="button"> </div> </form> Also make sure you edit all my crappy CSS That should work if you install the tables and paste exactly what I've got there in to a page - I haven't tested it though I'm afraid. Also remember you still need to connect to the DB ^.^ I left that bit out, hope you know how to do it! Quote Link to comment https://forums.phpfreaks.com/topic/87597-how-to-change-order-of-records/#findComment-459233 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.