PHP While loop do so many times in db

Question

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>";
    }
}

Your SQL statement "UPDATE productneeds SET Status='已過期';" does not have a WHERE part and does therefor update ALL rows in the productneeds, not only one row which might be what you expect? — thephper
– thephper, Commented Apr 21, 2020 at 13:01
Your prepared/execute handling is not quite correct. See docs where you assign $stmt to prepare call whose first argument should be connection object, $conn. Then you execute the query using $stmt. — Parfait
– Parfait, Commented Apr 21, 2020 at 14:18

Parfait · Accepted Answer · 2020-04-21 14:23:44Z

1

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

answered Apr 21, 2020 at 14:23

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP While loop do so many times in db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related