Pull text from mysql based on date

sweetback · June 1, 2011, 12:47am

Hello All,

I am a php newbie and trying to figure out how to automatically pull text from a database based on what the current datetime is. I have a table set up with three columns, start_date, end_date and text. I would like to pull the text field based on what datetime it is… so the field will auto update when the current date is in the range of the new row. So, the text will update on Monday at noon or something like that. Right now I am trying this:

$now = date(“Y-m-d H:i:s”);
$query=“SELECT text FROM database WHERE $now BETWEEN start_date AND end_date”;
$result=mysql_query($query) or die(“A MySQL error has occurred.
Error: (” . mysql_error() . ") " );

$text = mysql_result($result,0);

echo $text;

I am getting this error:
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 2

So it seems like the query is not pulling any results. Is that correct? Not sure how to go about this, any ideas?

Thanks so much for your time and expertise!

phphelp · June 1, 2011, 6:41am

Do you have any records in this your table named ‘database’ ?
If yes, what are field types of: start_date and end_date, and what values do you currently have in records there?

sweetback · June 1, 2011, 1:39pm

yes, I added one row for testing purposes. start_date and end_date are both datetime types. here is the row info:

start_date: 2011-05-29 12:00:00
end_date: 2011-06-05 11:59:59
text: Test Text

I was testing with the current datetime which would fall in that range.

Thank you so much for any help!

phphelp · June 1, 2011, 1:54pm

I think you’re missing single quotes:
[php]$query=“SELECT text FROM database WHERE ‘$now’ BETWEEN start_date AND end_date”;[/php]

sweetback · June 1, 2011, 3:05pm

Thanks for the reply! I tried the single quotes, but am still getting the same error as before. Is my query correct? Not sure what else to try here.

sweetback · June 3, 2011, 6:06pm

Got it working! I changed out the $now variable for the NOW() function and all is working great. If anyone else needs to do this sort of thing this is what I used:

$query=“SELECT sermon_text FROM sermontitle WHERE NOW() BETWEEN start_date AND end_date”;
$result=mysql_query($query) or die(“A MySQL error has occurred.
Error: (” . mysql_error() . ") " );

$text= mysql_result($result,0);

echo $text;