Evening,

 

Need some help in moving rows up and down in a MySQL database using PHP.

 

I have the following table:

 

id

order

title

link

 

There are about 10 records in the database. There is an admin part to the site, when I go in to amend these records I would like to be able to change the order of the records with an up or down button.

 

Can someone please help me with how I do this? if this makes any sense??

 

Thanks

Donkeyfourthumbs

Rows in an SQL database don't have any "inherent" ordering, so I assume the "order" field in your table is used to sort the list? For example, order=1 is at the top of a list, order=2 is next, order=3 below that etc.? Then to move two items, you just need a script which can exchange two pairs. For example, you want to move item 3 up---well, that involves moving 3 to 2, and 2 to 3. Any pairs will be the same. So your MySQL must swap elements (2,3) over. The easiest way to do this would be something like:

 

UPDATE table SET order=(CASE order WHEN 2 THEN 3 ELSE 2 END) WHERE order=2 OR order=3

Then you just need a PHP script to do this which can be linked to a pair of up/down images. Suppose it takes the arguments as $a and $b for the two order elements to swap, then in your PHP script, use:

 

UPDATE table SET order=(CASE order WHEN $a THEN $b ELSE $a END) WHERE order=$a OR order=$b

Thanks Connetu_C for your reply.

 

Yes I only put the 'order' field there because I thought it was the only way to sort the records, if I wanted to re-arrange them later.

 

At the moment if I add a record the 'id' and 'order' fields auto_increment. So there could be as many as 10 records or even 100.

 

Here is the code so far:

 

<form method="get" action="add_link.php"><p>Update <font color="#000000">Links</font> page:</p>


<?php

include("config.php");
$result = mysql_query("select * from links order BY id desc",$db);
$news=$result;
while ($row=mysql_fetch_array($news)) {

print '<div class="user-name">'.$row["link"]."</div><div class=\"user-edit\"><a href=\"update_link.php?id=$row[id]\">Edit</a><a href=\"delete_link.php?id=$row[id]\"><font color=\"#FF0000\">      Delete</font></a></div><div style=\"clear:both;\"></div>";
print '<div class="line"><img src="images/line.jpg" border="0"/></div><br />';

?>
<? } ?>
<p><input type="submit" value="Add New Link"/></p>
</form>

 

Any chance you could help me with code I would need to add a move up and move down button?

 

Thanks

DFT

Basically, in each row, you need to add this sort of thing:

 

$order = $row['order'];
$up = $order > 1; // is UP possible/to be shown?
$down = $order < (COUNT THE ROWS??-1); // is DOWN possible/to be shown?
if($up) {
 $tmp = $order-1; // move up one place; good enough??
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">UP</a>";
}
if($down) {
 $tmp = $order+1; // move down one place; good enough??
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">DOWN</a>";
}

There are a few gaps there for you to think about and fill: like how to determine if a record is at the top or bottom of a list (i.e. has the smallest or largest "order" value in the table respectively), and is doing $order+1 or $order-1 sufficient to swap two elements over, or could there be gaps in the "order" list? Hint: think about what happens if a record is removed.

 

The move_link.php script just does what I explained above, using the same $a and $b as I had there. You probably want to do an HTTP redirect back to your display page once you're done.

 

See if you can finish it off.

Cheers Connetu_C

 

It's like an assignment at college!!

 

I will give it a go.

 

Thanks for your help.

 

Basically, in each row, you need to add this sort of thing:

 

$order = $row['order'];
$up = $order > 1; // is UP possible/to be shown?
$down = $order < (COUNT THE ROWS??-1); // is DOWN possible/to be shown?
if($up) {
 $tmp = $order-1; // move up one place; good enough??
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">UP</a>";
}
if($down) {
 $tmp = $order+1; // move down one place; good enough??
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">DOWN</a>";
}

There are a few gaps there for you to think about and fill: like how to determine if a record is at the top or bottom of a list (i.e. has the smallest or largest "order" value in the table respectively), and is doing $order+1 or $order-1 sufficient to swap two elements over, or could there be gaps in the "order" list? Hint: think about what happens if a record is removed.

 

The move_link.php script just does what I explained above, using the same $a and $b as I had there. You probably want to do an HTTP redirect back to your display page once you're done.

 

See if you can finish it off.

Connetu_C

 

is DOWN possible/to be shown? - Hopefully what I have done is right, seems to work

 

$order = $row['sort_order'];
$up = $order > 1; // is UP possible/to be shown?
$down = $order < ("select count(*) from links"); // is DOWN possible/to be shown?
if($up) { 
 $tmp = $order-1;
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">UP</a>"; 
}
if($down) {
 $tmp = $order+1; // move down one place; good enough??
 echo "<a href=\"move_link.php?a=$order&b=$tmp\">DOWN</a>";
}

 

Hadn't thought about if a record is removed?

 

Also the move_link.php page, sorry but I am struggling to get it to do anything!

 

<?php
include("config.php");
if (isset($_GET["NOT SURE ABOUT THIS PART"])) {
$result = mysql_query("UPDATE links SET sort_order=(CASE sort_order WHEN $a THEN $b ELSE $a END) WHERE sort_order=$a OR sort_order=$b", $db);
}
if ($result) {
echo "<meta http-equiv=Refresh content=0;url=links2.php>";
}
?>

 

Oh and I changed the field name of 'order' to 'sort_order' because when I tried to list the records:

 

$result = mysql_query("select * from links order BY sort_order asc",$db);

 

 

it was order BY order and it didn't seem to work!

 

Sorry, but can I get a few more hints? I seem to understand it better when it is there in front of me.

 

Thanks

DFT

First thing first, a quick hint: if you want to use SQL keywords as field names, surround them with backticks. So you can indeed do "ORDER BY order" if you write it with ` around the name of the field, like this:

 

select * from links order BY `order` asc

is DOWN possible/to be shown? - Hopefully what I have done is right, seems to work

I don't think it's quite right. Take this simple scenario: you add four records into the table, orders 1, 2, 3 and 4. You then delete the second record, leaving records 1, 3 and 4 only. But now count(*) is 3, but the greatest value is 4. If you did effectively "$order < count(*)", then 1 < 2 so the first entry shows, but 3 < 3 is false and 4 < 3 is false, so DOWN isn't displayed for the middle option when it should be. Also if you remove entry 1, then $up will always be true, even though record 2 is the first entry. So you really should think about using max and min, like this:

 

$up = $order > mysql_query("SELECT min(order) FROM links");
$down = $order < mysql_query("SELECT max(order) FROM links"); // is this right now??

See if you think that's actually correct now; think through it, try it and see. If not, why not, and how do you fix it?

 

Also the move_link.php page, sorry but I am struggling to get it to do anything!

Okay, you have the code mostly there. You just need to convert the request parameters a and b into variables you can use. Also avoid the horrid HTTP meta refresh, and use a proper HTTP header response instead. All-in-all, something like this:

 

<?php
include("config.php");
$a = $_GET["a"];
$b = $_GET["b"];
/* Check numeric --- this is stronger than just isset and ensures no SQL injection occurs */
if (is_numeric($a) && is_numeric($b)) {
 $result = mysql_query("UPDATE links SET `order`=(CASE `order` WHEN $a THEN $b ELSE $a END) WHERE `order`=$a OR `order`=$b", $db);
}
/* Always redirect back using HTTP response */
header("Location: links2.php");
?>

Connetu_C

 

Once again, thank you very much for helping me. I also appreciate the way you are doing it. There is no point learning something if you don't understand how it works! As you can probably tell I am new to PHP (about 8 months), but I am getting there.

 

Before I get into the first part of the code, I have a problem with the header response.

 

Okay, you have the code mostly there. You just need to convert the request parameters a and b into variables you can use. Also avoid the horrid HTTP meta refresh, and use a proper HTTP header response instead. All-in-all, something like this:

 

<?php
include("config.php");
$a = $_GET["a"];
$b = $_GET["b"];
/* Check numeric --- this is stronger than just isset and ensures no SQL injection occurs */
if (is_numeric($a) && is_numeric($b)) {
 $result = mysql_query("UPDATE links SET `order`=(CASE `order` WHEN $a THEN $b ELSE $a END) WHERE `order`=$a OR `order`=$b", $db);
}
/* Always redirect back using HTTP response */
header("Location: links2.php");
?>

 

If I use the

header("Location: links2.php");

I get this error:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/sites/mywebsite.org/public_html/admin/config.php: in /home/sites/mywebsite.org/public_html/admin/move_link.php on line 10

 

But it works fine if I use

echo "<meta http-equiv=Refresh content=0;url=links2.php>";

 

any reason?

Something in your "config.php" file is writing to the response, preventing the header change from occurring. Check you haven't got any output being written or any whitespace outside <?php ?> tags in your config file. Then the header() method should work okay for you.

Something in your "config.php" file is writing to the response, preventing the header change from occurring. Check you haven't got any output being written or any whitespace outside <?php ?> tags in your config file. Then the header() method should work okay for you.

 

My config file looks like this

 

<?php
$hostname="79.170.40.200"; 
$db_username="username"; 
$db_password="something"; 
$db_name="database";
$db=mysql_connect($hostname, $db_username, $db_password); 
mysql_select_db($db_name,$db); 
?>

 

Is this OK? cause it still doesn't work?

That works okay for me (if I omit the MySQL connect/select lines). Are these generating warning/error messages perhaps? If so, that would explain it. Also is your Web server outputting anything else like a pregenerated header to the response? Essentially you get the failure to set a header when something has already been written to the client in the body of the HTTP response, so the headers have already been sent. Something has to be writing to the body for this error to be shown, it's just a case of finding exactly what (and a processing script like this one shouldn't need to write anything to the body at all)

 

P.S. Check that you don't have any newlines or whitespace before the <?php and after the ?> in the config file. That will be written to the response, giving you the problem you have.

Connetu_C

 

Yes, sorry there was a single white space at the very end after the ?> that works fine now.

 

I am still struggling with the following:

 

$up = $order > mysql_query("SELECT min(order) FROM links");
$down = $order < mysql_query("SELECT max(order) FROM links"); // is this right now??

 

I know it's not right, because it doesn't work. The original code of:

 

$up = $order > 1; // is UP possible/to be shown?
$down = $order < ("select count(*) from links"); // is DOWN possible/to be shown?

 

worked fine while all records where in place (1 through to 10 without any breaks), but when a record is deleted,it no longer works. I understand the theory behind it, but can't seem to work it out!

 

Sorry to be a pain.

 

Thanks

Donkeyfourthumbs

Remember again that "order" is an SQL keyword; to use it as a field name you need to use backticks. So for a start you need to write the SQL queries as:

 

SELECT min(`order`) FROM links
SELECT max(`order`) FROM links

Okay. Secondly, I should have pointed out I was using pseudo-code for those lines. I shouldn't have written mysql_query(...) as that's just confusing... why? Because that function returns a handle to a result set, not a result directly. It's therefore necessary to extract the results from the set; like this:

 

$res = mysql_query("SELECT min(`order`) FROM links");
$row = mysql_fetch_row($res);
$minOrder = $row[0];

and similarly for the $maxOrder variable. There is still one more PHP thing to be done for the $down test (think adding or subtracting 1 to $maxOrder, and why) and then it should all come together.

Connetu_C

 

OK, here is my full code so far:

 

<?php

include("config.php");
$result = mysql_query("select * from links order BY `order` asc",$db);
$news=$result;
while ($row=mysql_fetch_array($news)) {

$order = $row['order'];
$up = $order > mysql_query("SELECT min(`order`) FROM links");
$down = $order < ("select count(*) from links");

if($up) {
 $tmp_up = $order-1;
}

if($down) {
 $tmp_down = $order+1;
} 

echo '<div class="user-name">'.$row["link"]."</div><div class=\"user-edit\"><a href=\"move_link.php?a=$order&b=$tmp_up\"><img src=\"images/up.png\" border=\"0\">  </a><a href=\"move_link.php?a=$order&b=$tmp_down\"><img src=\"images/down.png\" border=\"0\"></a>      <a href=\"update_link.php?id=$row[id]\">Edit</a><a href=\"delete_link.php?id=$row[id]\"><font color=\"#FF0000\">      Delete</font></a></div><div style=\"clear:both;\"></div>";
echo '<div class="line"><img src="images/line.jpg" border="0"/></div><br />';

?>
<? } ?>

 

and here is the code for the move_link.php page:

 

<?php

include("config.php");

$a = $_GET["a"];
$b = $_GET["b"];

if (is_numeric($a) && is_numeric($b)) {
 $result = mysql_query("UPDATE links SET `order`=(CASE `order` WHEN $a THEN $b ELSE $a END) WHERE `order`=$a OR `order`=$b", $db);
}

header("Location: links2.php");

?>

 

any chance you could highlight what parts I need to sort in order to complete this task.

 

Thanks

DFT

Yep, these lines need fixing as I described above:

 

$up = $order > mysql_query("SELECT min(`order`) FROM links");
$down = $order < ("select count(*) from links");

Also you need to fix the block where you display the up/down images. The reason for this is that you display the up/down images even if the $up or $down are false (in which case $tmp_up or $tmp_down won't be valid). You should put each of these in the condition blocks I mentioned earlier to avoid this. So write:

 

echo '<div class="user-name">'.$row["link"]."</div><div class=\"user-edit\">";
if($up) {
 $tmp_up = $order-1;
 echo "<a href=\"move_link.php?a=$order&b=$tmp_up\"><img src=\"images/up.png\" border=\"0\">  </a>";
}

if($down) {
 $tmp_down = $order+1;
 echo "<a href=\"move_link.php?a=$order&b=$tmp_down\"><img src=\"images/down.png\" border=\"0\"></a>";
}
echo "      <a href=\"update_link.php?id=$row[id]\">Edit</a><a href=\"delete_link.php?id=$row[id]\"><font color=\"#FF0000\">      Delete</font></a></div><div style=\"clear:both;\"></div>";

The rest looks okay from inspection alone. There is a possible optimisation we can do (to reduce the number of database queries), but it's better to get the script working first before looking at those.

I am really sorry, I feel like I'm cheating everytime I ask you for help.

 

But I really am stuck, don't know if it's because I have been looking at it all day, but my brain can't cope anymore

 

any chance you could just finish it off for me? I promise I will play with it until I understand it!

 

Once again, thanks for your time, you have been very helpful.

 

Donkeyfourthumbs

As I said, it is just a matter of copy/paste what I wrote above in the right order:

 

<?php
include("config.php");

// ==> This is the part that can be optimised
$result = mysql_query("SELECT min(`order`) FROM links");
$row = mysql_fetch_row($result);
$minOrder = $row[0];

$result = mysql_query("SELECT max(`order`) FROM links");
$row = mysql_fetch_row($result);
$maxOrder = $row[0];

$result = mysql_query("select * from links order BY `order` asc",$db);
// ==> Up to here (you can do just one query rather than 3, but how?)
$news=$result;

while ($row=mysql_fetch_array($news)) {

 $order = $row['order'];

 echo '<div class="user-name">'.$row["link"]."</div><div class=\"user-edit\">";
 if($order > $minOrder) { // was $up, can be optimised??
$tmp_up = $order-1;
echo "<a href=\"move_link.php?a=$order&b=$tmp_up\"><img src=\"images/up.png\" border=\"0\">  </a>";
 }
 if($order < $maxOrder) { // was $down, can be optimised??
$tmp_down = $order+1;
echo "<a href=\"move_link.php?a=$order&b=$tmp_down\"><img src=\"images/down.png\" border=\"0\"></a>";
 }
 // NOTE: All this echo business would be better in a Smarty-like template. Future improvement.
 echo "      <a href=\"update_link.php?id=$row[id]\">Edit</a><a href=\"delete_link.php?id=$row[id]\"><font color=\"#FF0000\">      Delete</font></a></div><div style=\"clear:both;\"></div><div class=\"line\"><img src=\"images/line.jpg\" border=\"0\"/></div><br />";
?>

Thank you very much, I do appreciate all your help.

 

I will go through it all shortly and try working the whole thing out and will try to optimize the query section.

 

What do you mean by Smarty-like template?

 

Many thanks

Donkeyfourthumbs

What do you mean by Smarty-like template?

Smarty is a templating framework used to extract all the presentational data ((X)HTML) from the programming logic (PHP). The clean separation de-clutters your code and helps in maintenance. It doesn't take long to use, but is very useful. See Why Use Smarty.

Oh I see, I will take a look when I get chance.

 

There is just one more thing I forgot!!

 

On my add link page I can add a record, id, title, link but how do I add `order`, because when I started I completely forgot about order!!

 

It needs to know what the last `order` value was and then add the next value

 

Cheers

It needs to know what the last `order` value was and then add the next value

When you insert, just set the value for the order column to max(`order`)+1.

I tried the following:

 

$query = "INSERT INTO links (`order`, link, title) VALUES ('max(`order`)+1', '$link', '$title')";

 

and the following:

 

$query = "INSERT INTO links (`order`, link, title) VALUES (max(`order`)+1, '$link', '$title')";

 

but with no luck, sorry but what am I doing wrong?

Yes, I forgot about that

 

Basically MySQL does not currently allow you to use SELECT queries inside INSERTs---you can inside UPDATEs and DELETEs however. This is on their TODO list to comply with newer SQL requirements. Anyway, the way around it is to use INSERT ... SELECT statements. This would do:

 

$query = "INSERT INTO links (`order`,link,title) SELECT (max(`order`)+1),'$link','$title' FROM links";

Hopefully that should fix it, at long last

Connetu_C

 

Yes, finally there, apart from a little optimisation, it all works perfectly now.

 

Many thanks for your help today.

 

Donkeyfourthumbs