Help Posting Random Text from MySQL DB Daily

Daviddjr

Hello,

I need some help with selecting a record from a MySQL database on a daily basis. I basically want to display a poem on the main page of mywebsite. I want to post one every day but only one. This would be similar to a Joke/Quote of the day. In the DB I created I have a DATE field to store the date that the Poem was posted and an INTEGER field that has 0(zero) if the record has not been posted and 1 if it has.

No matter what I try I can't seem to properly compare the Date field in the DB to the Current Date to see if it matches. I basically have in my query...

WHERE Posted='1' && DATE=CURDATE()

It always returns true I think because the variable has some value. What I'm wanting is if there is a record that already has a date it will select that record, if there aren't any records with that date it will select one with no date. I tried having 0000-00-00 in the fields and even tried entering a date of 1940-01-01 in every record, still no luck.

I'm not going to post my code, as I already did that and didn't get any responses. What I'm looking for is if there are any existing scripts out there for posting a Joke/Quote/Text of the Day. I know there are but so far I haven't found any that combine PHP and MySQL that compare based on a Date field.

Any help would be appreciated. I can post/send my code but I wanted to get opinions without anyone seeing what I have yet.

Thanks in advance for any assistance.

David

drawmack

if you named the field DATE that's a problem since DATE is a reserved word in mysql.

Bah__Name

$sql1 = "SELECT minute(DATEFIELD) as MyDATE, NAME FROM tblMyTable WHERE CurrentlyDisplaying = 2";

I did one to update every minute. Doing it by day there is probably a day() version of minute() somewhere (there better be).
Then I compare it later:

$DATE = GetDate();
//... a few lines
//...
//...
If ($newarray1['MyDate'] == $DATE['minutes']) {
//use existing
}else{
//get a new one
}

Then some code to change 'HasBeenDisplayedBefore' flags etc before fetching the new one...
I put it to minute-ly rather than daily so I wouldn't have to wait 24 hours to debug it every time 😃

Hope that helped.

Daviddjr

Hello,

Thanks for replying but I'm wanting to do the compare before I pull back the data. I don't want to have to itterate through all the records to search for the current date. Eventually there will be thousands of records.

I just can't figure out why something like:

WHERE PostDate = CURDATE()

or even

WHERE PostDate = '2004-08-30'

is not working.

Actually I should clarify, it does work if there is no embedded script. If after finding that all I do is post the value then it works. However if I do a:

if (!$posting) {
}else{}
it always validates to true, where $posting is my SQL Result. Here is the code I have right now, if it helps.

<?PHP
dbConnect("davidd_poetry");
$posting = mysql_query("SELECT UserID,Title,DateWritten,Poem FROM ind_poems WHERE PostDate=CURDATE()");
if (!$posting) {
    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";
    while ($row = mysql_fetch_row($posting)){
        $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>";
}
}
?>

Thanks again for any assistance. I'm just STUCK.

David Devaney, Jr.

majik-sheff

What is the data type of the date column in your database?

If it's not a date/time data type your date comparison functions will not work.