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.
Results 1 to 11 of 11
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation displaying a record in a db where the date/time equals a custom made date?

    Hi All,

    ok i have an events table in my db which stores events(headline,content,event_date)

    and i have some php code which lists all the current days of the month, my question is how do i build a date of each day to allow me to check the db for an event?

    here is my code
    PHP Code:
    //This gets today's date
    $date =time () ;

    //This puts the day, month, and year in seperate variables
    $day = date('d', $date) ;
    $month = date('m', $date) ;
    $year = date('Y', $date) ;

    //Here we generate the first day of the month

    $first_day = mktime(0,0,0,$month, 1, $year) ;

    //This gets us the month name
    $title = date('F', $first_day) ;

    //We then determine how many days are in the current month
    $days_in_month = cal_days_in_month(0, $month, $year);

    //count up the days, untill we've done all of them in the month
    while ( $day_num <= $days_in_month )
    {
        ?>
        <div class="day">
        <div class="displayDay"><?php print $day_num?></div>
        <?php
        
    //CHECK DB FOR EVENT
        
    $check "SELECT * FROM events WHERE event_date = $day_num";
        
    $sql mysql_query($check)or die(mysql_error());
        while (
    $event mysql_fetch_array($sql)){?>
        <div class="displayEvent"><?php print $event['headline']?></div>
        <?php ?>
        </div>
        <?php
        $day_num
    ++;
        
    $day_count++;
    }
    but the problem i think lies when checking the date in my sql, do i need to build a new date for each day so that i can compare it to the db records???

    any ideas
    Luke
    Last edited by LJackson; 10-15-2009 at 04:29 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    for example

    something like this
    PHP Code:
        $newday date($day_num$month$year);
        print 
    $newday
    althought thats not the correct way to build a date

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok i tried
    PHP Code:
        <?php
    //CREATE TEMP NEW DATE
        
    $newday "$year-$month-$day_num";
        
    //print $newday;
        //CHECK DB FOR EVENT
        
    $check "SELECT * FROM events WHERE event_date = $newday";
        
    $sql mysql_query($check)or die(mysql_error());
        while (
    $event mysql_fetch_array($sql)){?>
        <div class="displayEvent"><?php print $event['headline']?></div>
        <?php ?>
    which prints out a newdate in the format 2009-10-01 etc but it wont find an event in the db, would this be because:

    1/ thats the wrong way to construct a date?
    2/ because the event_date field is actually a date/time field?
    3/ i should stop pretending i know what im talking about
    4/ something else?

    any help would be appreciated
    Luke
    Last edited by LJackson; 10-15-2009 at 04:30 PM.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,108
    Thanks
    2
    Thanked 326 Times in 318 Posts
    2/ because the event_date field is actually a date/time field?
    Yes. To compare a DATE with a DATETIME, you would need to only use the date part of the DATETIME field. You can use the mysql DATE() function to get just the date part of a DATETIME field.

    However, there is a simpler way to retrieve all the dates in the current month in the current year using a single query -

    PHP Code:
        $check "SELECT * FROM events WHERE EXTRACT(YEAR_MONTH FROM event_date) = EXTRACT(YEAR_MONTH FROM CURDATE()) ORDER BY event_date"
    You should almost never execute multiple queries to get values over a range. Whenever possible, execute a single query that retrieves all the rows you want in the order that you want them.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    hi mate,

    the reason why i am executing multi queries is because i have created a calendar and and checking each day on the calendar to see if there is an event for that day and if so add it to the calendar.

    would your above example work for this?

    i have tried
    PHP Code:
        $check "SELECT * FROM events WHERE DATE(event_date) = $newday"
    but nothing shows up on the date the test event is happening?

    here is my full code
    PHP Code:
    <?php 
    include_once("db_info.php");?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">
    .calendarcontainer{
    float:left;
    border:2px solid #000000;
    background-color:#000000;
    }
    .calendarHeader{
    float:left;
    width:500px;
    font-family:"Hobo Std", "Arial Rounded MT Bold";
    font-size:18px;
    }
    .calendarDaysHeader{
    float:left;
    width:110px;
    height:30px;
    font-family:Verdana, Arial, Helvetica, sans-serif;
    font-size:14px;
    margin:1px;
    font-weight:bold;
    text-align:center;
    }
    .dayHeaders{
    float:left;
    width:785px;
    background-color:#9999CC;
    }
    .weekcontainer{
    float:left;
    width:785px;
    height:80px;
    background-color:#FF0000;
    margin-bottom:2px;
    }
    .day{
    float:left;
    width:110px;
    height:80px;
    background-color:#CCCC66;
    margin:1px;
    }
    .displayDay{
    float:left;
    width:110px;
    height:25px;
    background-color:#FFFF00;
    font-family:"Hobo Std", "Arial Rounded MT Bold";
    font-size:16px;
    }
    .displayEvent{
    float:left;
    width:110px;
    height:25px;
    background-color:#FFFF00;
    font-family:Arial, Helvetica, sans-serif;
    font-size:12px;
    }
    </style>
    </head>

    <body>
    <?php
    //This gets today's date
    $date =time () ;

    //This puts the day, month, and year in seperate variables
    $day date('d'$date) ;
    $month date('m'$date) ;
    $year date('Y'$date) ;

    //Here we generate the first day of the month
    $first_day mktime(0,0,0,$month1$year) ;

    //This gets us the month name
    $title date('F'$first_day) ;

    //Here we find out what day of the week the first day of the month falls on
    $day_of_week date('D'$first_day) ;

    //Once we know what day of the week it falls on, we know how many blank days occure before it. If the first day of the week is a Sunday then it would be zero
    switch($day_of_week){
    case 
    "Sun"$blank 0; break;
    case 
    "Mon"$blank 1; break;
    case 
    "Tue"$blank 2; break;
    case 
    "Wed"$blank 3; break;
    case 
    "Thu"$blank 4; break;
    case 
    "Fri"$blank 5; break;
    case 
    "Sat"$blank 6; break;
    }

    //We then determine how many days are in the current month
    $days_in_month cal_days_in_month(0$month$year);

    //Here we start building the table heads
    ?>
    <div class="calendarcontainer">
    <div class="calendarMonth"><?php print $title.$year?></div>
    <div class="dayHeaders">
    <div class="calendarDaysHeader">Sunday</div>
    <div class="calendarDaysHeader">Monday</div>
    <div class="calendarDaysHeader">Tuesday</div>
    <div class="calendarDaysHeader">Wednesday</div>
    <div class="calendarDaysHeader">Thursday</div>
    <div class="calendarDaysHeader">Friday</div>
    <div class="calendarDaysHeader">Saturday</div>
    </div><?php
    //This counts the days in the week, up to 7
    $day_count 1;?>

    <div class="weekcontainer"><?php
    //first we take care of those blank days
    while ( $blank )
    {
        
    ?>
        <div class="day"></div><?php
        $blank 
    $blank-1;
        
    $day_count++;
    }

    //sets the first day of the month to 1
    $day_num 1;

    //count up the days, untill we've done all of them in the month
    while ( $day_num <= $days_in_month )
    {
        
    ?>
        <div class="day">
        <div class="displayDay"><?php print $day_num?></div>
        <?php
        
    //CREATE TEMP NEW DATE
        
    $newday "$year-$month-$day_num";
        
    //print $newday;
        //CHECK DB FOR EVENT
        
    $check "SELECT * FROM events WHERE DATE(event_date) = $newday";
        
    $sql mysql_query($check)or die(mysql_error());
        while (
    $event mysql_fetch_array($sql)){?>
        <div class="displayEvent"><?php print $event['headline']?></div>
        <?php ?>
        </div>
        <?php
        $day_num
    ++;
        
    $day_count++;

        
    //Make sure we start a new row every week
        
    if ($day_count 7  && $day_num <= $days_in_month)
        {
            
    ?>
            </div>
            <div class="weekcontainer"><?php
            $day_count 
    1;
        }


    //Finaly we finish out the table with some blank details if needed
    while ( $day_count >&& $day_count <=7)
    {
        
    ?>
        <div class="day"></div><?php
        $day_count
    ++;
    }
    ?>

    </div></div>

    </body>
    </html>
    thanks mate
    Luke

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,108
    Thanks
    2
    Thanked 326 Times in 318 Posts
    Date values are strings, so $newday must be enclosed in single-quotes in the query. Without the single quotes - 2009-10-01 is equal to the number 1998 (2009 minus 10 minus 1.)

    Retrieving all the data for a month in one query, then using that data inside a loop is many many times more efficient than executing individual queries inside of a loop.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    thanks mate got it working now

    i will give that suggestion a go see if i can get it working stand by

    Luke

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok, stuck already should

    PHP Code:
        $check "SELECT * FROM events WHERE EXTRACT(YEAR_MONTH FROM event_date) = EXTRACT(YEAR_MONTH FROM CURDATE()) ORDER BY event_date";
        
    $sql mysql_query($sql); 
    go before
    PHP Code:
    while ( $day_num <= $days_in_month )

    thanks
    Luke

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    how do i display a date time field as two seperate formatted pieces of data

    e.g current format = 2009-10-23 15:30:19

    i would like to output the date as 23-March-09 and
    the time as 15:30pm as two seperate pieces of data

    here is my current code which pulls out the whole kabam.
    PHP Code:
    <div class="event_list_event_date"><?php print $event['event_date'];?></div>
    <div class="event_list_event_date"><?php print $event['event_date'];?></div>
    not sure what i nee to do to get the results i desire

    any ideas please
    Luke

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,108
    Thanks
    2
    Thanked 326 Times in 318 Posts
    Code:
    SELECT DATE_FORMAT(event_date,'%e-%M-%y') as ev_date, DATE_FORMAT(event_date,'%l:%i%p') as ev_time
    Actually, your example of 15:30pm is 24 hour format with an am/pm indicator, so I assumed you wanted a 12 hour format in the above.

    PHP Code:
    <div class="event_list_event_date"><?php print $event['ev_date'];?></div>
    <div class="event_list_event_date"><?php print $event['ev_time'];?></div>
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    LJackson (10-16-2009)

  • #11
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    yeah 12 hour is great, thanks

    Luke


  •  

    Posting Permissions

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