Problem comparing date variable in Mysql query

Daviddjr

Hello,

I've tried everything I can think of. I've researched PHP and MySQL documentation as well as searched the web and forums like this.

What I'm doing is trying to post a random daily "article" on the home page. So I'm trying to check to see if an "article" has already been posted for that day and if so repost that. If one hasn't been posted for the day it will then do another query to find an "article" that hasn't ever been posted and post that.

I can't seem to get my queries to work. I had it working fine when I wasn't trying to search for an article that was already posted that day. When I added that query it started failing. As I have it now if there was an article posted for the current day it will show that. But if there are none in the database with today's date it will still evaluate to true and try to run the code for that. However it isn't actually finding a valid article. I've tried posting the result variable and got the "Resource ID" as, expected for an array. However when I cut the initial query down to one field from the database it still showed the "Resource ID" as the result. So I'm thinking somehow the Resource isn't getting cleared out and is throwing off the result variable, as indicated here:

http://us3.php.net/manual/en/language.types.resource.php

However every other time I use the same code it works fine.

Below is what I have. Any assistance would be appreciated.

Regards,
David Devaney, Jr.

<?php

$ptoday = Date("Y-m-d");

dbConnect("davidd_poetry");
$resultP = mysql_query("select UserID,Title,DateWritten,Poem,DatePost from ind_poems where DatePost= '$ptoday' limit 1");
if (!resultP) {
echo "no match found - $ptoday";
$result = mysql_query("select UserID,Title,DateWritten,Poem from ind_poems where Posted < 1 limit 1");
if (!$result){
echo "No Matches Found";
}else{
while ($row = mysql_fetch_row($result)){
$uid = $row[0];
dbConnect("davidd_RegUsers");
$poetresult = mysql_query("select Name from RegUser where UserID = $uid limit 1");
$poet = mysql_fetch_row($poetresult);

	$poem = $row[3];
	$poem = str_replace ("\r\n","<br>",$poem);
	$date = convertDate($row[2], "F d, Y");

	echo "<Table Border=0 cellspacing=1 cellpadding=5 width=475>
	<tr><td align=left><font size=4><b>$row[1]</b></font><br>
	<i>$poet[0]</i><br>
	$date</td></tr>
	<tr><td align=center>$poem</td></tr>";
	$cdate = convertDate($row[2], "Y");
	echo "<tr><td align=right><a href=copyright.php><font size=-1 color=000000>&copy $cdate $poet[0]</font></a></td></tr>
	</table>";
	}
}

}else{
echo "match found - $ptoday";
$row = mysql_fetch_row($resultP);
$uid = $row[0];
dbConnect("davidd_RegUsers");
$poetresult = mysql_query("select Name from RegUser where UserID = $uid limit 1");
$poet = mysql_fetch_row($poetresult);

$poem = $row[3];
$poem = str_replace ("\r\n","<br>",$poem);
$date = convertDate($row[2], "F d, Y");

echo "<Table Border=0 cellspacing=1 cellpadding=5 width=475>
<tr><td align=left><font size=4><b>$row[1]</b></font><br>
<i>1. $poet[0]</i><br>
2. $date<br>
3. $row[4]</td></tr>
<tr><td align=center>4. $poem</td></tr>";
$cdate = convertDate($row[2], "Y");
echo "<tr><td align=right><a href=copyright.php><font size=-1 color=000000>&copy $cdate $poet[0]</font></a></td></tr>
</table>";
# }

}
?>

Daviddjr

Hello,

Following up to my own message with more info. If I change the initial query to have == , for example

DatePost == '$ptoday'

Then the

if (!result)

evaluates to false even if the value matches, and then runs that code.

Thanks again,

David Devaney, Jr.

Daviddjr

I got the following simplified version of the code to work, but I used a different date field in my database. The date field I need to compare to has NULL values in the field if nothing has been entered. Actually it has 0000-00-00 when I INSERT through PHP.

$result = mysql_query("select UserID,Title,DateWritten,Poem from ind_poems where UploadDate=DATE_FORMAT(CURDATE(),'%Y-%m-%d') limit 1");
if (!$result){
echo "No Matches Found";
}else{
	 while ($row = mysql_fetch_row($result)){
		$uid = $row[0];
		dbConnect("davidd_RegUsers");				
		$poetresult = mysql_query("select Name from RegUser where UserID = $uid limit 1");
		$poet = mysql_fetch_row($poetresult);

	$poem = $row[3];
	$poem = str_replace ("\r\n","<br>",$poem);
	$date = convertDate($row[2], "F d, Y");

	echo "<Table Border=0 cellspacing=1 cellpadding=5 width=475>
	<tr><td align=left><font size=4><b>$row[1]</b></font><br>
	<i>$poet[0]</i><br>
	$date</td></tr>
	<tr><td align=center>$poem</td></tr>";
	$cdate = convertDate($row[2], "Y");
	echo "<tr><td align=right><a href=copyright.php><font size=-1 color=000000>&copy $cdate $poet[0]</font></a></td></tr>
	</table>";
	}
}

So I'm guessing it has to do with "Null" values. How do I get around that. I tried the following...

$posting = mysql_query("select UserID,Title,DateWritten,Poem from ind_poems where DatePost=DATE_FORMAT(CURDATE(),'%Y-%m-%d') && DatePost IS NOT NULL limit 1");

But that doesn't work either. I thought it might be the variable so I changed it from $resultP to $posting. Still no luck.

Thanks again for any assistance.

yelvington

If a field is of type DATE, you can compare it directly:

WHERE DatePost != '0000-00-00'

WHERE DatePost = CURDATE()

Where DatePost <= DATE_SUB(CURDATE(), INTERVAL 4 DAY)

Daviddjr

Originally posted by yelvington
If a field is of type DATE, you can compare it directly:

WHERE DatePost != '0000-00-00'

WHERE DatePost = CURDATE()

Where DatePost <= DATE_SUB(CURDATE(), INTERVAL 4 DAY)

Thanks for the info unfortunately that is not working either. I had previously tried the

WHERE DatePost = CURDATE()

This time I tried both

WHERE DatePost = CURDATE() && DatePost != '0000-00-00'

AND

WHERE DatePost = CURDATE() & DatePost != '0000-00-00'

When I use && it evaluates the first statement to True even if there are no records that = CURDATE(). In that case it does not display and record information. If I use only & then it still evaluates to True but displays a record that has 0000-00-00 in that Date field. As far as I know I'm supposed to use &&.

I have the following configuration, which I originally forgot to mention:

PHP version 4.3.8
MySQL version 4.0.20-standard

I have confirmed that my field is of type "DATE" with the following parameters, cause maybe that makes a difference. If "blank" is indicated there is no value showing.

Field - DatePost
Type - DATE
Length\Values - "blank"
Attributes - "blank"
Null - null
Default - NULL
Extra - "blank"

Is there a problem with the

If (!$posting)

code? Does that not work in this version of PHP?

If I run the SQL command in PHPMyAdmin I don't get any results if no records have the Current Date and I properly get a result if a record does have the current date. So I don't think it's my SQL query, at least not anymore.

Again, thanks for your assistance and I appreciate any more that anyone else can provide.

David Devaney, Jr.