PHP MySql Swap value in two rows

Question

I've seen a couple variations on this, but mainly they swap the entire row rather than just one value in that row, and not dynamically.

Here's the issue:

I have three rows each with the following cells (id, title, content, display_order, visible). id is auto_increment. title and content are manually entered and visible is a toggle. display_order is set when each row is created and automatically set as the highest integer and set at the bottom of the stack.

I set it like this so that if any of these records were to be manually deleted, i can reorder the stack automatically (if there are 4 records, and I delete #2, the new order resets as 1,2,3 and not 1,3,4).

Each row has a set of up and down arrow buttons that call move.php with queries of id(id), display_order(pos) and direction(dir).

In the move.php it uses a conditional to determine whether to move the record UP or DOWN depending on what the direction variable is set at.

What PHP code do I need to write to take these three variables (id, pos, dir) and swap the value in the table cell display_order: Here's a visual representation

Initial:

id   title   pos   
-----------------
 1   slide1   1
 2   slide2   2
 3   slide3   3

After I click the UP button for record ID #3:

id   title   pos   
-----------------
 1   slide1   1
 2   slide2   3
 3   slide3   2

MIND YOU the ID and POS will not always be the same integer

USING davidethell's suggestion I have created this:

Here's what I have created, but all I'm getting is the echo $newPos rather that is going back to the admin.php:

require ("connection.php");
    $id = $_GET['id'];
    $pos = $_GET['pos'];
    $dir = $_GET['dir'];

    if ($dir == 'up') {
        $newPos = $pos-1;
    } else {
        $newPos = $pos+1;
    }

    $fromRow = "SELECT * FROM pages WHERE display_order = ".$pos."";
    $toRow = "SELECT * FROM pages WHERE display_order = ".$newPos."";

    $reord = mysqli_query($conn, "UPDATE pages SET display_order = " . $toRow['display_order'] . " WHERE id = " . $fromRow['id']."; UPDATE pages SET display_order = " . $fromRow['display_order'] . " WHERE id = " . $toRow['id']);

    if ($reord){
        header("Location: admin.php");
    }else{
        echo $newPos;
    }

The problem I'm running into is that it only echos the $newPos

UPDATED CODE:

require ("connection.php");

    $fromArray = array();
    $toArray = array();

    $id = $_GET['id'];
    $pos = $_GET['pos'];
    $dir = $_GET['dir'];

    if ($dir == 'up') {
        $newPos = $pos-1;
    } else {
        $newPos = $pos+1;
    }

    $fromRow = mysql_query("SELECT * FROM pages WHERE display_order = ".$pos."");

    $toRow = mysql_query("SELECT * FROM pages WHERE display_order = ".$newPos."");


    $reord = mysqli_query($conn, "UPDATE pages SET display_order = " . $toRow['display_order'] . " WHERE id = " . $fromRow['id']);
    $reord = mysqli_query($conn, "UPDATE pages SET display_order = " . $fromRow['display_order'] . " WHERE id = " . $toRow['id']);

    if ($reord){
        header("Location: admin.php");
    }else{
        echo $newPos;
    }

Result: echos the $newPos instead of return to admin.php

Answer 1

You could use this query:

UPDATE
  yourtable INNER JOIN (
    SELECT
      MAX(yourtable.pos) pos_prec,
      curr.pos pos_curr
    FROM
      yourtable INNER JOIN
      (SELECT pos FROM yourtable WHERE id=3) curr
      ON yourtable.pos<curr.pos
    GROUP BY
      curr.pos) cp ON yourtable.pos IN (cp.pos_prec, cp.pos_curr)
SET
  pos = CASE WHEN pos=cp.pos_curr
             THEN pos_prec ELSE pos_curr END

It's a little bit complicated, but it will swap the value of the position where ID=3 with the value of the position of the preceding item, even if there are gaps.

Please see fiddle here.

EDIT

If there are no gaps, you could simply use this to move ID#3 UP:

UPDATE
  yourtable INNER JOIN (SELECT pos FROM yourtable WHERE id=3) curr
  ON yourtable.pos IN (curr.pos, curr.pos-1)
SET
  yourtable.pos = CASE WHEN yourtable.pos=curr.pos
                       THEN curr.pos-1 ELSE curr.pos END;

and just use +1 instead of -1 to move down (both UP and DOWN can be combined in one single query if needed).

PHP Code

And this is using PHP and mysqli, and assuming that the position is given:

<?php
$mysqli = new mysqli("localhost", "username", "password", "test");

$pos = 3;
$dir = 'down';

if ($dir == 'up') { $newPos = $pos-1; } else { $newPos = $pos+1; }

if ($stmt = $mysqli->prepare("
  UPDATE
    yourtable
  SET
    pos = CASE WHEN yourtable.pos=?
               THEN ?
               ELSE ? END
  WHERE
    pos IN (?, ?)
    AND (SELECT * FROM (
         SELECT COUNT(*) FROM yourtable WHERE pos IN (?,?)) s )=2;"))
{

    $stmt->bind_param("iiiiiii", $pos, $newPos, $pos, $pos, $newPos, $pos, $newPos); 
    $stmt->execute();
    printf("%d Row affected.\n", $stmt->affected_rows);

    $stmt->close();
}
$mysqli->close();
?>

(i also added a check, if trying to move UP the first pos, or DOWN the last one it will do nothing).

Answer 2

Assuming a $row object with each row data, just do:

if ($dir == 'up') {
    $fromRow = $row2;
    $toRow = $row1;
}
else {
    $fromRow = $row1;
    $toRow = $row2;
}
$sql = "UPDATE mytable SET pos = " . $toRow['pos'] . " WHERE id = " . $fromRow['id'];
// now execute your SQL however you want in your framework
$sql = "UPDATE mytable SET pos = " . $fromRow['pos'] . " WHERE id = " . $toRow['id'];
// now execute your SQL however you want in your framework

Put that in a function or class of some kind to make it reusable.

EDIT: Based on your edits it looks like you are not fetching enough information in your queries. They should be:

$fromRow = mysql_query("SELECT * FROM pages WHERE display_order = ".$pos."");

$toRow = mysql_query("SELECT * FROM pages WHERE display_order = ".$newPos."");

You were only selecting the id field, but then you were trying to use the display_order field.

Answer 3

i will not write you the PHP code. But i will give you the SQL:

Let's say $delta is either +1 if the slide's position should be raised 1, or -1 if the slide's position should be lowered by 1. $id is the id of the slide. Ow, and let's assume the table is called slides_table.

You will need 2 queries:

update slides_table set pos = pos + $delta where id = $id;
update slides_table set pos = pos - $delta where pos = (select pos from slides_table where id=$id) and id != $id