Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts

    how to echo only certain dates

    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();
    }
    ?>

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    What datatype are month, day, year stored as in the database?

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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).

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    %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.

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)

  • #9
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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;/>';
        }

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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?

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)

  • #11
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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;/>';
        } 

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)

  • #13
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    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
    Last edited by jchrisphonte; 01-25-2012 at 08:18 PM.

  • #14
    New Coder
    Join Date
    Jan 2012
    Posts
    84
    Thanks
    49
    Thanked 0 Times in 0 Posts
    just as a note -- was researching date and my data does not have leading zeros in the date

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by jchrisphonte View Post
    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.

  • Users who have thanked Fou-Lu for this post:

    jchrisphonte (01-25-2012)


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •