PHP to Update and Rotate Records

[ncurran217]

I am working on trying to get php code to take a record set from mysql table and rotate the records, so when you refresh the page, the first record would show at the top of the list, then record 2 and so on down the line.  If you were a new person to come to the page, record 2 would show at the top of the list, then record 3 and down the line, and record 1 would now be at the bottom.

 

I have a position column in the table and when the page loads it will update the position field of each record to be one less then it was before, so when the next person to come to the page, it will be up one spot in the table.  My issue is that when the page gets refreshed to quickly or I have had it not finish updating the records and then there is multiple records with the same position number.  I am looking to only have 9 records only for the example, so if Position is set to 1 it will be set to 9, if not it will take Position - 1. 

if(isset($_GET["page"])){
}
else{
	$sql = "SELECT ID,Name,Position FROM tableA ORDER BY Position DESC"; 
	$result = $conn->query($sql);
	while ($row = $result->fetch_assoc()) {
		if($row["Position"] == 1){
			$p = 9;
		}
		else{
			$p = $row["Position"]-1;
		}
		$conn->query("UPDATE tableA SET Position = $p WHERE ID = $row[ID]");
		}
		mysqli_free_result($result);
}

Am I going down the right path?  It just seems like it will not work 100% of the time.  Hopefully I explained what is going wrong fully and clearly.  Thank in advance.

[scootstah]

You might try transactions. This will basically look the table/row while your queries run, to make sure that the values don't change in the meantime.

[Barand]

I'd do the position updates in one query

<?php
$sql = "SELECT position, name
        FROM tableA
        ORDER BY position";
$res = $db->query($sql);
while (list($pos,$name) = $res->fetch_row()) {
    echo "$pos | $name<br>";
}
//
// ROTATE THE POSITIONS
//
$sql = "UPDATE tableA
            JOIN (SELECT @max := (SELECT MAX(position) FROM tableA)) as getmax
        SET position = CASE position 
            WHEN 1 THEN @max 
            ELSE position-1 
            END";
$db->query($sql);    
?>
<form>
    <input type="submit" name="btnSub" value="Test">
</form>

[NotionCommotion]

It's hard to believe you couldn't just use the appropriate SELECT query without needing the UPDATE queries.

[ncurran217]

Thanks for the replies and help.  I will try your guys suggestions and see what works best.  

[mac_gyver]

an appropriate select query - 

(SELECT ID,Name,Position FROM tableA WHERE Position >= (select pos from tableB) ORDER BY Position ASC)

UNION

(SELECT ID,Name,Position FROM tableA WHERE Position < (select pos from tableB) ORDER BY Position ASC)

tableb just holds the current position of the 1st/top row to display. when you update/increment the tableb.pos column to rotate the display, make sure it wraps around from 9 to 1 (whatever the max value in your tablea.position column is.)

[mac_gyver]

here's another way to do this in a select query - 

SELECT ID,Name,Position FROM tableA ORDER BY Position < (select pos from tableB), Position ASC

[ncurran217]

 

I'd do the position updates in one query

<?php
$sql = "SELECT position, name
        FROM tableA
        ORDER BY position";
$res = $db->query($sql);
while (list($pos,$name) = $res->fetch_row()) {
    echo "$pos | $name<br>";
}
//
// ROTATE THE POSITIONS
//
$sql = "UPDATE tableA
            JOIN (SELECT @max := (SELECT MAX(position) FROM tableA)) as getmax
        SET position = CASE position 
            WHEN 1 THEN @max 
            ELSE position-1 
            END";
$db->query($sql);    
?>
<form>
    <input type="submit" name="btnSub" value="Test">
</form>

This worked amazing!  Thank you very much!

[NotionCommotion]

I like mac_gyver's solutions.