[RESOLVED] Ok you haveto see this. It makes NO sense.

[ElectricRain]

This is my mysql table

It IS named mail.

Now for the code

//fetch read mail more than 3 months old
$pastdate = gmdate('Y-m-d', strtotime('-3 months'));

$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < '$pastdate'";
$theresult = mysql_query($thequery) or die;
$the_num = mysql_num_rows($theresult);
if ($the_num >= '1') { echo "ok"; }
elseif ($the_num == '0') { echo "no"; }

$therow = mysql_fetch_assoc($theresult);
print_r ($therow);
exit;

now $mysite_username is definately 'Eternity' I tried printing it out to see. The problem is, I cant see any mistake in all this n yet it always prints 'no' WHY???

[thorpe]

try removing the quotes.

if ($the_num >= 1) { echo "ok"; }

mysql_num_rows returns the number of rows found, not a string.

ps: you dont need an elseif either. an else will suffice.

[cahva]

Ah.. Thorpe got it first..

BTW, you can use mysql's date functions so that you dont need that gmdate

$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < CURDATE() - INTERVAL 3 MONTH"; 

[ElectricRain]

I know I dont need an elseif, Im using one just because on this occasion, if it echos ok i want to be absolutely and beyond all doubt sure that the condition is true.
It shouldnt make it not work should it?

Taking away the quotes didnt work 🙁 I tried that already too... shoulda mentioned. I still get a no.

< CURDATE() - INTERVAL 3 MONTH";

Uhh.. what is this? This could be useful but ive not seen it before :S

[ElectricRain]

oh and its not the quotes.. see what Im really trying to do is this

mysql_query("DELETE from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < '$pastdate'") or die (mysql_error);

When it didnt work I just added in the other stuff to try n work out WHY it doesnt work. But in the original variation there was no mysql_num_rows stuff and it still didnt work 🙁

[thorpe]

so have yu got it working yet or what? post your current code if not. and if it is working, please mark the thread resolved.

[ElectricRain]

//dump read mail more than 3 months old
$pastdate = gmdate('Y-m-d', strtotime('-3 months'));
$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < '$pastdate'";
$theresult = mysql_query($thequery) or die;
$the_num = mysql_num_rows($theresult);
if ($the_num >= 1) { echo "ok"; }
elseif ($the_num == 0) { echo "no"; }
$therow = mysql_fetch_assoc($theresult);
print_r ($therow);

// this bit is what I actually want to do. The above is just me trying to work out why this bit doesnt work.
mysql_query("DELETE from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < '$pastdate'") or die (mysql_error);

Same problem- it still echoes no and does not delete.

[rincewind456]

Assuming that your 0 or 1 for the mail read option is an enum field, you need to treat it as a string not an integer, put quotes around the 1 in the query like so

$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened = '1' AND datestamp < '$pastdate'";

and that should sort it.

[ElectricRain]

Its an integer (int) field and putting the quotes around the 1 does not fix the error 🙁

now i have

//dump read mail more than 3 months old
$pastdate = gmdate('Y-m-d', strtotime('-3 months'));
$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened = '1' AND datestamp < '$pastdate'";
$theresult = mysql_query($thequery) or die;
$the_num = mysql_num_rows($theresult);
if ($the_num >= 1) { echo "ok"; }
elseif ($the_num == 0) { echo "no"; }
$therow = mysql_fetch_assoc($theresult);
print_r ($therow);

mysql_query("DELETE from mail WHERE touser = '$mysite_username' AND opened = '1' AND datestamp < '$pastdate'") or die (mysql_error);

[rincewind456]

The code works! I've got it running on my local machine.
have you tried echoing out your variables to make sure they are what they should be? like

//dump read mail more than 3 months old 
$pastdate = gmdate('Y-m-d', strtotime('-3 months')); 

echo $pastdate;
echo $mysite_username;

$thequery = "SELECT * from mail WHERE touser = '$mysite_username' AND opened =1 AND datestamp < '$pastdate'"; 
$theresult = mysql_query($thequery) or die; 
$the_num = mysql_num_rows($theresult); 
if ($the_num >= 1) { echo "ok"; } 
elseif ($the_num == 0) { echo "no"; } 
$therow = mysql_fetch_assoc($theresult); 
print_r ($therow); 

mysql_query("DELETE from mail WHERE touser = '$mysite_username' AND opened = 1 AND datestamp < '$pastdate'") or die (mysql_error);

[ElectricRain]

Ahh cracked it. The problem (for once) wasnt my code. Or not this piece anyway. See this piece of code appears on the login page. Preceding it is the code that sets the cookies, and says that config.php should be included. config.php says that the var
$mysite_username = $HTTP_COOKIE_VARS["mysite_username"];

For some unknown reason, this page, and this page alone cant seem to recognise $mysite_username. So when I define $mysite_username as $_POST[username] then it works. Odd... but it works.

Thank you for all of your help anyway.