...

View Full Version : displaying a record in a db where the date/time equals a custom made date?



LJackson
10-15-2009, 04:54 PM
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

//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

LJackson
10-15-2009, 04:57 PM
for example

something like this

$newday = date($day_num, $month, $year);
print $newday;


althought thats not the correct way to build a date :(

LJackson
10-15-2009, 05:03 PM
ok i tried

<?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 :D
4/ something else?

any help would be appreciated
Luke

CFMaBiSmAd
10-15-2009, 05:36 PM
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 -


$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.

LJackson
10-15-2009, 05:45 PM
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

$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
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,$month, 1, $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 > 0 )
{
?>
<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 >1 && $day_count <=7)
{
?>
<div class="day"></div><?php
$day_count++;
}
?>

</div></div>

</body>
</html>


thanks mate
Luke

CFMaBiSmAd
10-15-2009, 05:56 PM
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.

LJackson
10-15-2009, 05:59 PM
thanks mate got it working now :)

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

Luke

LJackson
10-15-2009, 06:04 PM
ok, stuck already should


$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

while ( $day_num <= $days_in_month )
{


thanks
Luke

LJackson
10-16-2009, 03:54 PM
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.

<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

CFMaBiSmAd
10-16-2009, 04:23 PM
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.



<div class="event_list_event_date"><?php print $event['ev_date'];?></div>
<div class="event_list_event_date"><?php print $event['ev_time'];?></div>

LJackson
10-16-2009, 04:32 PM
yeah 12 hour is great, thanks

Luke



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum