Double query not cycling

[N00bie]

Thought I had fixed this long ago, but I guess not. What I am trying to do should be simple, but my simple mind isn’t figuring it out.
What I want to do is search the database for an expired value ($expiredate) and then change another variable.

I have tried if SELECT where $expiredate < $todaydate, I have tried IF statements, to no avail.

I am here:

require_once ('connect.php');
$todaydate = $_SERVER['REQUEST_TIME']; 
$db = mysqli_connect($db_hostname,$db_username,$db_password,"ptb_members") or die ("Cannot Connect to database");
		$result = mysqli_query($db,"SELECT * FROM test WHERE active = 'y' AND exempt = 'n'") or die ("Cannot select from db");
		while ($row = mysqli_fetch_array($result)) {
		extract($row);  	
	if ($expiredate < $todaydate){
			$query = "UPDATE test SET active='n' WHERE (id = '$id')";
			$result = mysqli_query($db,$query) or die("<b>Fatal Error: (" . mysqli_errno() . ") " . mysqli_error());  
			echo "User $fname $lname ($id) active status changed to n<br>";
}

But it only changes the first one and no more.

Why doesn’t SELECT * Where expiredate < $todaydate work?
Why cant I UPDATE active=‘n’ WHERE $expiredate < $todaydate?

I know there is a better way, but haven’t figured it it yet. Needs smarter minds than mine.
Thanx

[phdr]

@N00bie

But it only changes the first one and no more.

This is the only problem you have provided enough information to solve. You are reusing the $result variable for the UPDATE query, so, when the code attempts to loop through the result set from the SELECT query, there is no result set. The result from the mysqli query() call for the UPDATE query doesn’t need to be assigned to a variable.

For any other help, you need to post an sql dump of your database table, with some sample data.

Some points about the code -

1. Use ‘require’ for things you code must have for it to work and require isn’t a function. The () around the filename don’t do anything.
2. $_SERVER[‘REQUEST_TIME’] is a unix timestamp and all the comparisons you have shown or implied must be with timestamp values.
3. Don’t use or die() for database error handling. Instead, use exceptions for database statement errors and in most cases simply let php catch and handle any database statement exception.
4. List out the columns you are SELECTing, especially when asking others for help with your queries.
5. Don’t use extract(), ever.
6. You can probably do this in one single UPDATE query, but it would take knowing what your data actually is to do so.

[N00bie]

@phdr

You can probably do this in one single UPDATE query, but it would take knowing what your data actually is to do so.

Thank you for the informative reply - much appreciated.
I did figure it out before going to bed and forgot to post: it was the dbl query and result that, like you said, were conflicting. Got it working but would be nice to have it all in one query, but really doesn’t matter as script only runs twice a month by cron.

Relevant data is an expiration date (expiredate : timestamp) and a status column (active : y/n). The script compares expiredate to todaydate and if expired (expiredate < todaydate) changes the active column from y to n…

Thanx again

[phdr]

Single query -

require 'connect.php';
$db = mysqli_connect($db_hostname,$db_username,$db_password,"ptb_members");

$sql = "UPDATE test SET active='n' WHERE active = 'y' AND exempt = 'n' AND expiredate < UNIX_TIMESTAMP()";
$mysqli_query($db,$sql);