MOD EDIT: Post moved into its own thread; original thread can be found here.

Installer;10725927 wrote:

Store the dates and text in a database, then retrieve the text for the current date something like this:

mysql_query = "SELECT my_text FROM my_table WHERE start_date <= DATE() AND end_date >= DATE()";

I know this is a very old thread, but I am looking to do this same thing, and have not been able to get this query to work. Can anyone help? 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.<br />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?

    Welcome to PHPBuilder! I've moved your post into its own thread (and into the Database forum) so that we aren't hijacking someone else's thread.

    As for your issue, note that a datetime value is a string, and that all strings must be delimited with quotes. In your SQL query, you didn't quote the date value.

    However, also note that generating timestamps in PHP is unnecessary - MySQL is perfectly capable of telling what date/time it is without PHP's help. For example, you should get rid of the $now variable and instead use the MySQL function named (ironically) NOW().

      Thanks for the reply! I changed the query to this:

      $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.<br />Error: (" . mysql_error() . ") " );

      $text= mysql_result($result,0);

      echo $text;

      but still get that same error. Am I using the NOW() function correctly? Any other ideas on how to do this? I am completely stuck!

        You've added quotes to your SQL query where they don't belong. sermon_text isn't a string - it's an identifier (e.g. the name of a column), so it shouldn't be quoted. Same for start_date and end_date.

          Oh, WOW... thank you SO MUCH! That completely worked! I just did a test and the text switched out at the end time just like I need. Thanks again, I have posted on several PHP help boards, and you were the first to help me figure this out. Thanks for your time.

          I am in US EST and my server is PST. Is there an easy way to set the timecodes to be EST or should I just do the 3 hr conversion with each row?

            This is one of the reasons why I like TIMESTAMP columns instead of DATETIME columns:

            MySQL Manual wrote:

            Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

            In other words, you can set the time zone setting in the MySQL connection and MySQL will take care of converting the stored time value to an appropriate 'local time' value.

            Otherwise, with a DATETIME column, you have to a) make an assumption about what time zone is reflected in the stored data, and b) adjust it upon retrieval as necessary. For that last part, you could do something like:

            SELECT the_datetime_column - INTERVAL 5 HOUR

            where the '-' and '5' values should be adjusted accordingly to reflect the difference.

              That sounds perfect, I like the TIMESTAMP type. I have changed the types in my database for start_time and end_time to TIMESTAMP. So in my call to the database (using mysql_connect) I would pass along time zone information? Or would this be done in the database somehow? Sorry if this is an easy question... just getting the hang of all this.

                Read the time zone support page I linked to in my previous post. It explains the different ways which you could go about setting the time zone.

                  Write a Reply...