how to echo only certain dates

[jchrisphonte]

Hello All

Can anyone shed some light into how I could adjust the PHP below to show only events that are today's date or later?

Essentially I'm looking to adjust the first script to only show events occurring today or later, currently it shows all events in the table(past,present, & future).

PHP Code:

<?php
require 'connect.inc.php';

$query = "SELECT event, company, location, city, month, day, year FROM calendar_event ORDER by month,day ASC";

if ($query_run = mysql_query($query)) {
   while ($query_row = mysql_fetch_assoc($query_run) ) {
      $event= $query_row ['event'];
      $company= $query_row ['company'];
      $location= $query_row ['location'];
      $city= $query_row ['city'];
      $month= $query_row ['month'];
      $day= $query_row ['day'];              
      $year= $query_row ['year'];
      
  
      
      echo '<div class="where">'.$event.'</div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style=margin-top:5px;margin-bottom:5px;"/>';
    }

}
else{
echo mysql_error();
}
?>

[Fou-Lu]

What datatype are month, day, year stored as in the database?

[jchrisphonte]

hello Fou lu,

So the date is save in 3 columns - 2digit day , 2 digit month, and 4 digit year.

day, month, & year

25,1,2012

[Fou-Lu]

Actually while writing this I just realized that this will be a horrendous task to do if you keep these day month and year columns separated. Finding a >= current date means you have to constantly check the year, day and month and will create a horrible query. The problem is you cannot check for a day >= 25 for example, since Feb 1 is considered > Jan 25. You will likely want to create a datetime datatype to use instead.
This is still doable, the SQL itself would be a nightmare to write, and if PHP is chosen instead it will effectively create what the SQL should be anyway with a mktime to create the datetime, and then compare it to today. This would be easier than the query IMO.
So you need to decide first if you want to convert the structure of the database to use a datetime type, or if you want to put the work on PHP to do the comparisons. IMO I'd convert the SQL as you can then issue a simple WHERE event_date >= NOW() (where event_date would be the new datetime type).

[jchrisphonte]

i have a column with the date in one cell if that helps -- I have a column in excel that i can re-add that cells format is mm/dd/yyyy

I can add a column in mytable just for this if it makes this query easier.

[Fou-Lu]

I realize again I just had a blond moment. It doesn't really matter if they are separate either since we can query the group together to construct an actual datetime.

PHP Code:

$query = "SELECT event, company, location, city, month, day, year
    FROM calendar_event
    WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%m%e') >= NOW()
    ORDER by month,day ASC"; 


The format is very specific. YYYYmmd is exactly what that wants. So the month must be 0 led and with the above format the day must not be 0 led (use %d for 0 led day instead of %e, and %c will go without the 0 lead month instead of the %m).
That should work so long as you match your format

[jchrisphonte]

thanks for this --

I'm trying to implement this now, I'm a newbie of sorts.

do i rename "%Y%m%e" what ever i would like?

also i dont understand the %d, %e....etc

also what do you mean by match format?? I copied and pasted this:

WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%m%e') >= NOW() into my

$query= "SELECT...so it appears as your example above


thank you for your help on this.

[Fou-Lu]

%Y%m%e is the format specifier. That means 2012011 is valid for a date January 1 2012. The %m and %e is what may need to be changed depending on if you are working with direct numbers or strings for those. If they are integers, you likely want the format %Y%c%e, while if they are strings it could be %Y%m%d.

[jchrisphonte]

they are integers - ive changed it to %Y%c%e and now the data shows on my local host yet it still shows the older dated events...

Here is what I have..

PHP Code:

<?php
require 'connect.inc.php';

$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') >= NOW()ORDER by month,day ASC";

if ($query_run = mysql_query($query)) {
   while ($query_row = mysql_fetch_assoc($query_run) ) {
      $id= $query_row ['id'];
      $event= $query_row ['event'];
      $company= $query_row ['company'];
      $location= $query_row ['location'];
      $city= $query_row ['city'];
      $month= $query_row ['month'];
      $day= $query_row ['day'];              
      $year= $query_row ['year'];
      
  
      $urlevent = str_replace(" ","_", $event);
      
      echo '<div class="where"><a href="http://www.hedgevent.com/details/'.$urlevent.'-'.$id.'.html" class="event">'.$event.'</a></div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style= margin-top:5px;margin-bottom:5px;/>';
    }

[Fou-Lu]

Add into the select , STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') AS convertedDate, and print the $query_row['convertedDate'] somewhere. Does this show the date as defined in the year month and date, or does this show nothing, January 1, 1970/December 31, 1969, or some other bad datetime?

[jchrisphonte]

i get an error message.. it says:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS convertedDate, >= NOW()ORDER by month,day ASC' at line 1

PHP Code:

$query = "SELECT id, event, company, location, city, month, day, year FROM calendar_event WHERE STR_TO_DATE(concat(`year`, `month`, `day`), '%Y%c%e') AS convertedDate, >= NOW()ORDER by month,day ASC";



if ($query_run = mysql_query($query)) {
   while ($query_row = mysql_fetch_assoc($query_run) ) {
      $id= $query_row ['id'];
      $event= $query_row ['event'];
      $company= $query_row ['company'];
      $location= $query_row ['location'];
      $city= $query_row ['city'];
      $month= $query_row ['month'];
      $day= $query_row ['day'];              
      $year= $query_row ['year'];
      $converteddate= $query_row['convertedDate'];
      
  
      $urlevent = str_replace(" ","_", $event);
      
      echo '<div class="where"><a href="http://www.hedgevent.com/details/'.$urlevent.'-'.$id.'.html" class="event">'.$event.'</a></div>'.$company.'<div class="date">'.$month.'/'.$day.'/'.$year.'</div><br>'.$location.', '.$city.'<br><hr style= margin-top:5px;margin-bottom:5px;/>';
    } 


[Fou-Lu]

No, that needs to be added to the selection fields, not the where clause. The entire where clause can be removed if necessary.
My assumption is that it will return either nothing or epoch. This indicates that the format is not valid for the specifier provided, and for that we'd need to see what exactly the month, day, year is and its datatypes.

[jchrisphonte]

hey Fou-Lu

since i'm not as good at PHP and queries Ive added a column in my table called 'sdate' which is the start date in regular mm/dd/yyyy format.

Does this make the query easier??

how should I adjust it - thank you in advance for all your help

[jchrisphonte]

just as a note -- was researching date and my data does not have leading zeros in the date

[Fou-Lu]

Originally Posted by jchrisphonte

hey Fou-Lu

since i'm not as good at PHP and queries Ive added a column in my table called 'sdate' which is the start date in regular mm/dd/yyyy format.

Does this make the query easier??

how should I adjust it - thank you in advance for all your help

Is this datatype datetime type, or is it a text/varchar type? Make it a datetime type if you can, since then you can use a simple WHERE sdate >= NOW() in the where clause, while a string would need converting to a date.