根据以前的ID更新下一行

let say i have a table named banners. the columns are:

假设我有一张名为横幅的桌子。列是:

+---+-------------------------------+
|ID | link  | image       | active  |
+---+-------+-------------+---------+
|1  |#link1 | image1      | 0       |
|2  |#link2 | image2      | 1       |
|3  |#link3 | image3      | 0       |
+---+-------+-------------+---------+

there you can see row #2 is active. how can i update next row based on latest active row? also if active row is the last row, then set first row as active row.

在那里你可以看到第2行是活跃的。如何根据最新的活动行更新下一行?如果活动行是最后一行,则将第一行设置为活动行。

PS: I will do the query using cron, update every 2 hours for example. no problem about the cron, I did it.

PS:我将使用cron进行查询,例如每2小时更新一次。关于cron没问题,我做到了。

3 个解决方案

#1

use this stored procedure :

使用此存储过程:

DELIMITER //
CREATE PROCEDURE updateActiveRow()
BEGIN
    SELECT MAX(ID) INTO @activeID FROM banners WHERE active;
    UPDATE banners SET active=1 WHERE ID > @activeID LIMIT 1;
    IF ROW_COUNT() = 0 THEN
        UPDATE banners SET active=1 ORDER BY ID LIMIT 1;
    END IF;
    UPDATE banners SET active=0 WHERE ID = @activeID;/*do this only if you want to deactivate current active row*/
END //

#2

Thanks to all, I solved it myself. here is the code

谢谢大家,我自己解决了。这是代码

class Job{
    private $con;
    function __construct(){
        $this->con = mysqli_connect('localhost', 'root', '', 'egoji');
    }

    function getCurrentActiveID(){
        $q = "SELECT id FROM banners WHERE active='1'";
        $result = $this->con->query($q);
        $row = mysqli_fetch_assoc($result);
        return $row['id'];
    }

    function getNextID($id){
        $q = "SELECT MIN(id) AS id FROM banners WHERE id > '{$id}'";
        $result = $this->con->query($q);
        $row = mysqli_fetch_assoc($result);
        return $row['id'];
    }

    function isLastRow(){
        $currentActiveId = $this->getCurrentActiveID();

        $q = "SELECT id FROM banners ORDER BY id DESC LIMIT 1";
        $result = $this->con->query($q);
        $row = mysqli_fetch_assoc($result);

        return $row['id'] == $currentActiveId ? true:false;
    }

    function updateFirstRow(){
        $q = "SELECT MIN(id) AS id FROM banners";
        $result = $this->con->query($q);
        $row = mysqli_fetch_assoc($result);

        $this->deactivateAll();

        $update_q = "UPDATE banners SET active='1' WHERE id='{$row['id']}'";
        $this->con->query($update_q);
    }

    function updateNextRow(){

        $currentActiveId = $this->getCurrentActiveID();
        $nextID = $this->getNextID($currentActiveId);

        $this->deactivateAll();

        $update_q = "UPDATE banners SET active='1' WHERE id='{$nextID}'";
        $this->con->query($update_q);
    }

    function deactivateAll(){
        $update_q = "UPDATE banners SET active='0'";
        $this->con->query($update_q);
    }

    function doit(){
        if($this->isLastRow()){
            $this->updateFirstRow();
        }else{
            $this->updateNextRow();
        }
    }
}

$job = new Job;
$job->doit();

I am open to any suggestions or correction.

我愿意接受任何建议或更正。

#3

Tested SQL Fiddle

经过测试的SQL小提琴

SET @activeID = (SELECT ID FROM Banners WHERE active = 1);
SET @isLast = (SELECT COUNT(*) FROM Banners) LIKE @activeID;

UPDATE Banners
SET active = IF(ID = @activeID, 0, IF(ID = @activeID+1, 1, active));

UPDATE Banners
SET active = IF(@isLast AND ID = 1, 1, active);

#1