How do I do a Foreach loop and Mysql with a specific condition?-CodePudding

I am kind of confused and I tried to look online but could not find any answer who fit my need. I am facing an issue with PHP / MYSQL

I have a list of users and each users is currently active.

I would like to create a MYSQL UPDATE (active = 0) to the users who their membership has expired.

The user is automaticaly set up to 1 when they create their account and have their membership for 1 year. if the date pass. This user will be automaticaly to the Active row (0) instead of (1) and the account will automaticaly desactivated.

I want to do it with a loop because we have hundred of members who has not been connected for a while and their account wont be desactivated unless they sign-in.

I know I miss something (a foreach or a for ) but no idea how to represent it.

Someone can give me a hand ?

Thank you so much.

<?php include("config.php");


$date1 = date("d/m/Y"); // currently date of today
$bouclerequete = "SELECT * FROM users";  // connect all the users in once.
$resultboucle = $link->query($bouclerequete);


while($rowtable = mysqli_fetch_array($resultboucle, MYSQLI_ASSOC)){   // while fetch array with all the users


 $date2 = $rowtable["datefincontrat"]; // end of the contract

 if ($date1 > $date2){  // if the date of today is lower than the date of their membership. we don't do anything




 }
 else{  // Otherwise , the membership will return to 0 and the account will be desactivated. 

   $ids2 = $rowtable["id"];
   $sql5 = "UPDATE users SET Active='0' WHERE id=$ids2";

   if ($link->query($sql5) === TRUE) {

   } else {
     echo "Error updating record: " . $conn->error;
   }



 }

}


?>

CodePudding user response：

Keep it simple and make this in one command.

UPDATE users SET Active='0' WHERE datefincontrat < CURDATE();

Still, your code is vulnerable to sql injection, so please read up on prepared statements with parameters. See: How can I prevent SQL injection in PHP?