Scanning and modifying mysql database problem

[WeBBy421]

This has been driving me crazy all day. Out of hair to pull out so hoping for some help.

 

Trying to go through a database, selecting expired memberships, and setting those expired from active = 'Y' to active = 'n'.

 

The script goes through the database fine, but when it encounters an expired member and updates active to 'n', it then goes into error trying to re-enter the cycle (continuing to scan database) and dies.

 

//Check all active members
$db = mysql_connect("localhost","xxxx","xxxx");
mysql_select_db("paratb_members",$db);
$result = mysql_query("SELECT * FROM temporary WHERE active = 'y' AND exempt = 'n'",$db);
while ($row = mysql_fetch_array($result)) {
extract($row);
//some variables
$todaydate = $_SERVER['REQUEST_TIME']; //current timestamp
$almostdue = strtotime("-3 months", $expiredate); //months to start warning
$whendue = date('F d, Y',$expiredate);
if ($expiredate < $todaydate ){
//Change Member STATUS
mysql_select_db("paratb_members",$db);
$query = "update temporary SET active = 'n' WHERE id = '$id'";
$result = mysql_query($query,$db) or die ("Error: (" . mysql_errno() . ") " . mysql_error());;
echo "User id: $id - DUES PAST DUE ON $whendue<br>";
}
echo "Userid:$id - Members Status OK<br>";
}
echo "DONE";

 

I get the following Error message (after changing database):

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/xxxx/public_html/tmp.php on line 7

 

line 7 is the original query:

 while ($row = mysql_fetch_array($result)) { 

 

Any idea as to why it is going into fatal error after making the db change?

 

Any help would be greatly appreciated. Thanx

[benanamen]

First things first. You are using obsolete Mysql code that will not work at all in the current version of Php. You need to use PDO with parameterized queries.

 

Second, the whole thing can be done in ONE sql statement.

 

Third, your active and exempt columns should be tinyint with a lenth of 1 and use the values 1  or 0 for yes/no

 

Fourth, why are you re-selecting your database. 

Edited October 10, 2015 by benanamen