How to change order of records

[skyer2000]

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?

[revraz]

What is determining the new order?

 

You may need a new column to sort by.

[skyer2000]

What is determining the new order?

 

You may need a new column to sort by.

 

The 'fieldorder' column is determining the order.

[revraz]

What do the up/down arrows do?  Insert a new value in the fieldorder?

[skyer2000]

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.

[PHP Monkeh]

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!

[skyer2000]

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.

[PHP Monkeh]

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>

[revraz]

This would probably much simpler with JS or Ajax

[PHP Monkeh]

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

[fenway]

You just have to save the sortorder of all of the items each time... then just enumerate them.

[revraz]

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.

[fenway]

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.

[char skd1]

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

[PHP Monkeh]

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!