CODEWITHSUNDEEP
phpdatabasewhile-loop
asdfqerwq
asdfqerwq

Reputation: 53

PHP While loop do so many times in db

I'm trying to detect if the data in the db is expired or not, and it works prefectly fine as long as I only have on data to check when there are mutiple data needed to be check then everything will somehow follow the first one's behavior 

while($row=mysqli_fetch_assoc($exe))
{   
    date_default_timezone_set("Asia/Taipei");
    echo $row['ExpeTime']." ";
    echo $ExpTim=strtotime($row['ExpeTime']);
    echo " ";
    echo $TWnowSec=date("U");
    echo " ";

    /*if($ExpTim>$TWnowSec){
        echo "沒過期";
    }elseif($ExpTim<$TWnowSec){
        echo "過期了";
    }*/

    if($ExpTim<$TWnowSec) {
        $sql= "UPDATE productneeds SET Status='已過期';";
        if(!mysqli_stmt_prepare($stmt, $sql)){
            echo "update failed";
        }else{
            mysqli_stmt_execute($stmt);
            echo "sucess";
        }
    }else {
        echo "{$row['O_no']} is not expired<br>";
    }
}

Upvotes: 0

Views: 42

Answers (1)

Parfait
Parfait

Reputation: 107697

Consider a pure SQL update query and avoid any PHP looping. Since you appear to be looping from a different recordset fetch, run MySQL's supported UPDATE...JOIN query to update records across one or more tables. Some adjustments required to achieve your needed time zone. Below is a demonstration for you to finalize:

// SET MYSQL SESSION TIME ZONE TO GMT
$sql = "SET TIME_ZONE = '+00:00';"        
mysqli_query($conn, $sql);

// RUN UPDATE + JOIN WITH ADJUSTMENT FOR 'Asia/Taipei' TIME ZONE
$sql = "UPDATE productneeds p
        INNER JOIN myotherTable t
           ON p.myRelatedID = t.myRelatedID
        SET p.Status = '已過期' 
        WHERE t.ExpeTime < NOW() + INTERVAL 8 HOUR"; 

mysqli_query($conn, $sql);

Upvotes: 1

Related Questions