Jump to content

PHP to Update and Rotate Records


ncurran217
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

  • Solution

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>
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

 

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.