Problem scanning and modifying mysql database

General PHP Help
#1

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.

[php]
//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
”;
}
echo “Userid:$id - Members Status OK
”;
}
echo “DONE”;
[/php]

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:
[php] while ($row = mysql_fetch_array($result)) { [/php]

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

Any help would be greatly appreciated. Thanx

#2

First thing first. You need to update your code. Mysql_ functions are deprecated. Update to mysqli, there is more to it then just adding the i, or pdo.

That will alleviate some of your issues.

Instead of changing the dbname through that function, you could send the cmd “use paratb_members”

#3

As I replied to you in two other forums:

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.

Dont you think you might be overwriting your $result variable? Look at what the code is doing.

  • You are also setting the date in a loop over and ever again. the date doesn’t change.

If you took the time to read the replys you have already gotten elsewhere you wouldnt have to post the same thing to three or more forums. The responses arent going to change. The whole code is junk.

#4

I agree, NOObie, you should upgrade to MySQLi. It is actually quite easy as you are using simple functions
which are easy to alter to the “Improved” version.

Now for you current errors, it is because you are nesting MySQL calls. You can’t do that with the same names.
Think of it this way:

Open your DB connection
Process a Query with variables $query and place the results into $result
Loop thru the results of the query…
Process a second Query with variables $query and $result
…
close connection

If you notice you alter the results of the first query and create a new result variable which in effect will
trash your first results and therefore either trashes your data and possibly your DB or throws you an
error. So to fix that, all nested queries should use different names…

Your line #6 posted starts a loop using the variable array named $result. Therefore you query starting at
line #14 should not use that variable. So, I think you should change that internal query to use different
variable names and you will be alright. Just use $query2 and $result2 in lines #15 and #16 and it will work.

Now, also, you do not need to reselect the same database table. It remembers the previous one, so the
line #14 should not be needed. Lastly, you did not INDENT the IF clause on line #12 thru 19. Therefore,
it appears that it is not inside the WHILE loop. I suggest indenting that part. This might help you notice
queries inside of while’s that are processing the previous query’s data. Might help…

Hope that helps…

#5

As Kevin pointed out, this could easily be completed with a single query (the kind used in a crontab).
You just do a mass update based on the expiration date and whether it is currently active. The loop would not be needed at all.

#6

Thanx all.

First let me apologize for posting in multiple forums. Has been years since I have posted in php help forums (had to use the old “forgot password” link) and am used to days to get an answer if you got one at all. Things have obviously changed for the better - again, my apologies.

Got enough good feedback (including changing to mysqli) that I think I can get things to work.

Some of what is confusing (like variables that are not used) is because this is just a snippet of the entire code.

This code will end up being a cron job that wil scan the database every 2 weeks and send an email to members when their dues are due within 90 days. If they pay their dues, the emails stop. Once their dues expire (the code I posted) they get sent an email telling their dues have expired and the change in active status redirects the index page to a Pay your Dues page.

So, must scan the entire db and:
If (dues expired and active=‘y’), send email, and change active to ‘n’.
If (dues <90 days due && active=‘y’), send email.

I think I can now get this to work now. :smiley: thanx

My only dilemna is that an expired date (before active=n), both emails will be sent unless I run the 2 fuctions independently… hmmmmm, i.e., eliminate the if statements and scan the db for expired and THEN scan for <90 due.

#7

Good for you, NOObie, but, listen to Kevin and Astonecipher for the query change.
Further info on that, you can do a single query that will alter your “active” field right in a query.
You use a conditional update query which would mark all of your expired ones in one query.
Much easier to just use that process…

#8

thanx again…

yup, updated to mysql ;)i

#9

Don’t know what I am doing wrong here. Have similar scripts running elsewhere but this one doesn’t work.

What it does is it appends all subsequent emails. In order words, the first email is fine, but the second email is emailed to the second “hit” but the message contains the email to the first and second users and so on.

[php]
$todaydate = $_SERVER[‘REQUEST_TIME’]; //current timestamp
$db = mysqli_connect(“localhost”,“xxxx”,“xxxx”, “xxxx”);
$result = mysqli_query($db,“SELECT * FROM members WHERE active = ‘y’ AND exempt = ‘n’”);
while ($row = mysqli_fetch_array($result)) {
extract($row);
$almostdue = strtotime("-3 months", $expiredate); //months to start warning
if ($todaydate > $almostdue){
$to = “$fname $lname <$email>”;
$subject = “Your Membership Dues”;
$headers .= “From: [email protected]” . “\r\n”;
$headers .= “Reply-To: [email protected]” . “\r\n”;
$headers .= “MIME-Version: 1.0” . “\r\n”;
$headers .= “Content-type:text/html;charset=UTF-8” . “\r\n”;
$headers .= ‘X-Mailer: PHP/’ . phpversion();
$fromEmail = "[email protected]";
$fifth = “-f” . $fromEmail;
$message .= "Dear $fname,


Blah, blah, blah, blah
mail($to, $subject, $message, $headers, $fifth);
}
}
[/php]

I even tried to unset() some of the variables but that didn’t help either.

Any help in what stupid thing I am diong would be greatly appreciated.

#10

No need to start a new topic when you already have one open.

#11

You need to clear out the variables before you set them again. $message and $headers just continue to grow.
As an example.

[php]$counter = 10;
$example = “This is a string\n”;
while ( true ){
$example .= “So is this.\n”;
$counter–;
if ( $example == “So is this.\n” )
break;
}[/php]

$example continues to grow because it is never cleared. Either declare them inside the loop, so they are always reinitialized, or set them to empty strings.

#12

I have tried to clear the variables with unset() but that had no effect
Not very good with loops - I am a N00b

#13

NOObie, you set it up that way.

You are using $message .= and therefore it concatenates it to the end of the previous value of $message.

If you remove your period then it will reset the variable before it assigns the value of the message.

#14

Thanx ErnieAlex

Thought it was something stupid ::slight_smile:

#15

Yes, the simple things like one period is the hardest to find! Sounds like you have your project finished!

Congrats! Always nice to solve a programming puzzle!

Sponsor our Newsletter | Privacy Policy | Terms of Service