Jump to content


Photo

convert mysql data to a calendar


  • Please log in to reply
14 replies to this topic

#1 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 03:08 PM

Hello @all

I'm working on a project to use database info as a calendar
for example :
Table calendar
calendar_id  int(11)  Nee  0               
calendar_date  date  Nee  0000-00-00 

calendar_id|calendar_date

1, '2006-01-01'
2, '2006-01-02'
3, '2006-01-03'
4, '2006-01-04'
5, '2006-01-05'
6, '2006-01-06'
7, '2006-01-07'
8, '2006-01-08'
9, '2006-01-09'
10, '2006-01-10
11, '2006-01-11
12, '2006-01-12
until 365
I really tryed and looked at some date/time functions however all of them is to create calendar withouth
setted dates from a database  ::)

I really hope someone can help me out as i'm already strugling with it over a week and i cant go on and sleep anymore.
It wont take long and i start crying as a girl

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 08 October 2006 - 03:11 PM


Didn't have time before but here's sample code. First the test data then the calendar script


Code:
CREATE TABLE room_booking (
	`bookID` int(11)  NOT NULL auto_increment,
	`clientID` int(11)  NOT NULL ,
	`roomID` int(11)  NOT NULL ,
	`arrive` date  NOT NULL DEFAULT '0000-00-00',
	`depart` date  NOT NULL DEFAULT '0000-00-00',
	`nights` int(11)  NOT NULL ,
	`discount` varchar(100)  NOT NULL ,
	`status` tinyint(1)  NOT NULL ,
	PRIMARY KEY(bookID)
);

INSERT INTO room_booking VALUES ('1', '1', '1', '2006-06-30', '2006-07-02', '2', '0', '1');
INSERT INTO room_booking VALUES ('2', '2', '1', '2006-07-07', '2006-07-11', '3', '0', '2');
INSERT INTO room_booking VALUES ('3', '3', '1', '2006-07-14', '2006-07-17', '2', '0', '1');
INSERT INTO room_booking VALUES ('4', '4', '2', '2006-07-07', '2006-07-11', '3', '0', '2');
INSERT INTO room_booking VALUES ('5', '1', '2', '2006-07-14', '2006-07-19', '4', '0', '2');
INSERT INTO room_booking VALUES ('6', '3', '4', '2006-07-14', '2006-07-29', '14', '0', '3');

Calendar script

Code:
<?php
include('db.php');

$cal_start = isset($_GET['cal_start']) ? $_GET['cal_start'] : mktime(0,0,0);
$cal_days = $bookings = array();
  // dates for calendar display
for ($i=0; $i<28; $i++) {
	$cal_days[] = strtotime("+$i days", $cal_start);
}
 // create room booking array
for ($r=1; $r<=4; $r++) {
	$bookings[$r]=array();
	foreach($cal_days as $cd) {
		$bookings[$r][$cd] = 0;
	}
}

$cal_end = end($cal_days);
  // get bookings for calendar period and put in array
$d1 = date('Y-m-d', $cal_start);
$d2 = date('Y-m-d', $cal_end);
$sql = "SELECT b.bookID, b.clientID, b.roomID, b.arrive, b.depart, b.status
		FROM room_booking b 
		WHERE ((b.arrive BETWEEN '$d1' AND '$d2') OR
			  (b.depart BETWEEN '$d1' AND '$d2')) 
		ORDER BY b.roomID, b.arrive";
$res = mysql_query($sql) or die(mysql_error());
while (list($bid, $cid, $rid, $arr, $dep, $stat) = mysql_fetch_row($res)) {
	for ($d=strtotime($arr); $d<=strtotime("$dep -1 days"); $d+=86400) {
		if (isset($bookings[$rid][$d])) $bookings[$rid][$d] = $stat;	
	}
}
  // calc prev and next weeks
$prevweek = strtotime('-7 days', $cal_start);
$nextweek = strtotime('+7 days', $cal_start);

function display_calendar (&$dates, &$bookings, $prev, $next) {
	echo "<table cellspacing='0' cellpadding='0' >\n";
	echo "<TR><TH><a href='?cal_start=$prev'>&lt;</a></TH>
			<TH colspan='27'>Fawlty Towers Guest House<br>Room Bookings</TH>
			<TH><a href='?cal_start=$next'>&gt;</a></TH></TR>";
		// dates
	echo "<TR>\n";
	echo "<TH>Room</TH>" ;
	foreach ($dates as $day) {
		switch (date('w', $day)) {
			case 0:
			case 6: $class = 'class=wkend'; break;
			default: $class = '';
		}
		echo "<TH $class>".date('M', $day).'<br>'.date('j', $day)."</TH>\n";
	}
	echo "</TR>\n";
	
		// rooms
	foreach ($bookings as $room => $rmdata) {
		display_room_bookings ($room, $rmdata);
	}
	echo "</table><br>\n";
	display_key();
}

function display_room_bookings ($room, $rmdata) {
	echo "<TR><TH>$room</TH>\n";
	foreach ($rmdata as $dt=>$status) {
		switch ($status) {
			case 1:	$class = 'class=booked'; break;
			case 2: $class = 'class=conf'; break;  
			case 3: $class = 'class=paid'; break;  
			default: $class = ''; break;  
		}
		echo "<TD $class>&nbsp;</TD>\n";
	}
}

function display_key() {
	echo '<table cellspacing="0">
		<tr><td>Free</td>
		<td class="booked">Booked</td>
		<td class="conf">Confirmed</td>
		<td class="paid">Paid</td>
		</tr></table>';
}

?>
<html>
<head>
<meta name="generator" content="PhpED Version 4.5 (Build 4513)">
<title>Sample bookings calendar</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="07/15/2006">
<style type='text/css'>
        table {
        	border-left: 1px solid gray;
        	border-top:  1px solid gray;
        	width: 90%;
        }
        th {
        	border-right: 1px solid gray;
        	border-bottom:  1px solid gray;
        	font-family: sans-serif;
        	font-size: 0.75em;
        	font-weight: 700;
        	background-color: #E0E0E0;
        	height : 20px;
        	width: 3.5%
        }
        th.wkend {
        	background-color: #C0C0C0;
        }
        td {
        	border-right: 1px solid gray;
        	border-bottom:  1px solid gray;
        	font-family: sans-serif;
        	font-size: 0.75em;
        	font-weight: 300;
        	text-align: center;
        	width: 25px;
        	height : 20px;
        	background-color: #C0FFC0;
        }
        td.booked {
        	background-color: #FFFFC0;
        }
        td.conf {
        	background-color: #DFC868;
        }
        td.paid {
        	background-color: #FF8080;
        }
</style>
</head>
<body>
	<?php display_calendar ($cal_days, $bookings, $prevweek, $nextweek)?>
</body>
</html>


Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 03:53 PM

this is for date differences for a booking/reservation?

what i ment was more like this:

September 2006
Mon Tue Wed Thu Fri Sat Sun
    1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30


#4 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 04:28 PM

i changed the database to whati think make more sence to be able to retrieve , oncei know how, a better format :
calendar_id  int(11)   Nee  0                (for all yearday)
did  int(11)   Nee  0                (for calandarday)
calendar_date  date   Nee  0000-00-00  (full date)

1,    1, '2006-01-01'
2,    2, '2006-01-02'
3,    3, '2006-01-03'
4,    4, '2006-01-04'
5,    5, '2006-01-05'
6,    6, '2006-01-06'
7,    7, '2006-01-07'
8,    8, '2006-01-08'
9,    9, '2006-01-09'
10, 10, '2006-01-10'
11, 11, '2006-01-11'
12, 12, '2006-01-12'
xxx
244, 1, '2006-09-01'


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 08 October 2006 - 06:59 PM

This calendar script generates the calendar for a month, highlighting any days that have an appointment/event.

The event days are passed in an array

<?php
function displayCalendar($currYear=0,$currMonth=0,$currDay=0, $appoints=null) {

    $currYear = $currYear == 0 ? date('Y') : $currYear;
    $currMonth = $currMonth == 0 ? date('n') : $currMonth;
    $today = (($currYear == date('Y')) && ($currMonth == date('n'))) ? date('j') : 0;
    if ($currDay == 0) $currDay = $today;

    $prevMonth = $currMonth==1 ? 12 : $currMonth-1;
    $nextMonth = $currMonth==12?  1 : $currMonth+1;
    $prevYear = $currMonth==1 ? $currYear-1 : $currYear;
    $nextYear = $currMonth==12? $currYear+1 : $currYear;

    $appdays = array();
    if (is_array($appoints)) {
        foreach ($appoints as $ap) {
            if ($currMonth == date("n",strtotime($ap))
                && $currYear == date("Y",strtotime($ap))) {
                $appdays[date("j",strtotime($ap))] = 1;
            }
        }
    }

    $day1 = mktime(0,0,0,$currMonth,1,$currYear);
    $dim = date('t', $day1);
    $dayN = mktime(0,0,0,$currMonth,$dim,$currYear);
    $dow1 = (date('w',$day1)+6) % 7;
    $dowN = (date('w',$dayN)+6) % 7;
    $calHead = date('F Y',$day1);
    $self = $_SERVER['PHP_SELF'];
    echo <<<EOT
        <table border="0" cellspacing="0" style="border: 1pt solid silver">
        <tr>
          <td class="hd"><a class="nav" href="$self?cy=$prevYear&cm=$prevMonth">&lt;&lt;</a></td>
          <td colspan="5" class="hd">$calHead</td>
          <td class="hd"><a class="nav" href="$self?cy=$nextYear&cm=$nextMonth">&gt;&gt;</a></td>
        </tr>
        <tr>
          <th class="wd">M</th>
          <th class="wd">T</th>
          <th class="wd">W</th>
          <th class="wd">T</th>
          <th class="wd">F</th>
          <th class="we">S</th>
          <th class="we">S</th>
        </tr>
        <tr>
EOT;

    for ($d=0;$d<$dow1;$d++) {
         $cl = ($d%7==5) || ($d%7==6) ? 'we' : 'wd';
         echo "<td class='$cl'>&nbsp;</td>";
    }
    $c = $dow1;
    for ($d=1; $d<=$dim; $d++, $c++) {
        if ($c%7==0) echo "</tr><tr>";
        $cl = ($c%7==5) || ($c%7==6) ? 'we' : 'wd';
        if ($appdays[$d]) $cl .= 'a';
        $st = ($d == $currDay) ? "style='border: 1pt solid #FF0000'" : '';
        echo "<td class=\"$cl\" $st>\n";
        $dstr = "<a class=\"nul\" href=\"$_SERVER[PHP_SELF]?cy=$currYear&cm=$currMonth&cd=$d\">$d</a>";
        echo ($d==$today) ? "<B>$dstr</B>" : $dstr;
        echo "</td>\n";
    }
    while ($c++ % 7 != 0)  {
           $cl = ($c%7==6) || ($c%7==0) ? 'we' : 'wd';
           echo "<td class='$cl'>&nbsp;</td>";
    }
    echo "</tr></table>\n";
}
?>
<html>
<!-- Creation date: 04/02/05 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>baaCalendar</title>
<meta name="Author" content="Barand [barryaandrew at aol dot com]">
<meta name="Generator" content="AceHTML 4 Pro">
<style type="text/css">
<!--
TD {font-family: arial; font-size: 9pt; text-align: center}
TH {font-family: arial; font-size: 9pt; text-align: center; width: 20px; font-weight: 600; color: #FFFFFF}
TH.wd {background: #99CC99}
TH.we {background: #999999}
TD.wea {background: #CCCCCC}
TD.we {background: #EEEEEE; }
TD.wd {background: #EEFFEE}
TD.wda {background: #AAFFAA}
TD.hd {background: #FFFFFF; color: #339900}
A.nul {text-decoration: none}
A:hover {background: #DDDDDD; color: #FF0000}
/* End of style section. Generated by AceHTML 04/02/05 */
-->
</style>


</head>
<body>

<?
$aps = array('2006-09-23', '2006-10-11', '2006-10-20', '2006-11-01');
$y = isset($_GET['cy']) ? $_GET['cy'] : 0;
$m = isset($_GET['cm']) ? $_GET['cm'] : 0;
$d = isset($_GET['cd']) ? $_GET['cd'] : 0;

displayCalendar($y, $m, $d, $aps);

?>

</body>
</html>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 07:20 PM

NOOOO pls
no classes that generate a calender directly out of php
As i sayed all dates must be calculated from the dates inside the database.
From these i find 1000's
i really need it formatted out of the data from sql
i apreciate that you want to help me this way but it isnt what i need.

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 08 October 2006 - 07:24 PM

Please explain it to me. What is the point of storing every date in the database? These can be generated anytime. The only thing you need to store in the db is those dates that have an event attached.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 07:42 PM

i have this question posted in 4 different forums...
i retrieve excact same questions as you now ask.
pls i dont want to be offending but i'm really in a mood i could strugle some1 (lol.. i still am able to laugh abouth it.. lucky me)

my whole concept is based on this methode , each day of the year fungate as a product.
This is not for either reservations nore to make events.

Please also not try tell me that you would still do it different then.
i'm not a good php coder , then i also must work with php inside ajax,dhtml,css etc etc.
I want to keep it simple for myself it is me who have to understand at the end what i made.
So when i see all date/time functions and classes it is to much for me... however i'm able to trace references to a database verry well.(i think).
for the loading time i not worry as i plan to load on the productpage only per month.
if you want i send a pm to you with my full concept idea.

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 08 October 2006 - 08:25 PM

i have this question posted in 4 different forums...
i retrieve excact same questions as you now ask.


That should tell you something!

OK, let's indulge this whim of yours.

First you need to query the table to get the dates in the target month

SELECT date, calendar_date FROM tablename
WHERE YEAR(calendar_date) = '$year'
AND MONTH(calendar_date) = '$month'

So now you have all the dates in the month. But you still have to lay the calendar out on the page. You can replace the for() loop in my code and loop through the selected records instead.

BUT...

What day of the week do we start on for the 1st of the month? Oh dear, having to resort to date/time functions to sort that one out.

Want a title for the calendar like "October 2006". Oops. There we go again with the date time functions.

So there is no way you are just going to select a bunch of records from a db and it magically lays itself out without PHP date/time functions.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 08:43 PM

yes ofcourse i know it still require to use php date/time functions
and my apolegies for let you misunderstand me.

but to now use these date/time functions is where i'm stucked.

And i would be greatfull if you or someone else could help me make this to work.



#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 08 October 2006 - 09:32 PM

I added a few comments to help

<?php
function displayCalendar($currYear=0,$currMonth=0) {

    //
    // get unix time for 1st of the month
    //
    $day1 = mktime(0,0,0,$currMonth,1,$currYear);
    //
    // get day of week it falls on
    // normally sun is day 0 but we want mon to be day 0
    // hence add 6, divide by 7 and get remainder (d+6)%7
    //
    $dow1 = (date('w',$day1)+6) % 7;
    //
    // get heading as October 2006
    //
    $calHead = date('F Y',$day1);
    
    $self = $_SERVER['PHP_SELF'];
    echo <<<EOT
        <table border="0" cellspacing="0" style="border: 1pt solid silver">
        <tr>
          <td colspan="7" class="hd">$calHead</td>
        </tr>
        <tr>
          <th class="wd">M</th>
          <th class="wd">T</th>
          <th class="wd">W</th>
          <th class="wd">T</th>
          <th class="wd">F</th>
          <th class="we">S</th>
          <th class="we">S</th>
        </tr>
        <tr>
EOT;

    //
    //  Blanks until 1st of month
    //
    for ($d=0;$d<$dow1;$d++) {
         $cl = ($d%7==5) || ($d%7==6) ? 'we' : 'wd';
         echo "<td class='$cl'>&nbsp;</td>";
    }
    $c = $dow1;
    //
    // get dates from the db table
    //
    $sql = "SELECT date FROM tablename
            WHERE YEAR(calendar_date) = '$currYear'
            AND MONTH(calendar_date) = '$currMonth'";
    $res = mysql_query($sql) or die (mysql_error());
    while (list($d) = mtsql_fetch_row($res)) {
            // new row every 7 days
        if ($c%7==0) echo "</tr><tr>";
            // set style accordong to weekday or weekend
        $cl = ($c%7==5) || ($c%7==6) ? 'we' : 'wd';
            // show current day
        $st = ($d == $currDay) ? "style='border: 1pt solid #FF0000'" : '';
        
        echo "<td class=\"$cl\" $st>$d</td>\n";
        $c++;
    }
    
        // finish off the last row of the month
    while ($c++ % 7 != 0)  {
           $cl = ($c%7==6) || ($c%7==0) ? 'we' : 'wd';
           echo "<td class='$cl'>&nbsp;</td>";
    }
    echo "</tr></table>\n";
}
?>
<html>
<!-- Creation date: 04/02/05 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>baaCalendar</title>
<meta name="Author" content="Barand [barryaandrew at aol dot com]">
<meta name="Generator" content="AceHTML 4 Pro">
<style type="text/css">
<!--
TD {font-family: arial; font-size: 9pt; text-align: center}
TH {font-family: arial; font-size: 9pt; text-align: center; width: 20px; font-weight: 600; color: #FFFFFF}
TH.wd {background: #99CC99}
TH.we {background: #999999}
TD.wea {background: #CCCCCC}
TD.we {background: #EEEEEE; }
TD.wd {background: #EEFFEE}
TD.wda {background: #AAFFAA}
TD.hd {background: #FFFFFF; color: #339900}
A.nul {text-decoration: none}
A:hover {background: #DDDDDD; color: #FF0000}
/* End of style section. Generated by AceHTML 04/02/05 */
-->
</style>


</head>
<body>

<?
$y = '2006';
$m = '10';
displayCalendar($y, $m);

?>

</body>
</html>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 10:01 PM

:-* :-* :-*

;D THANK YOU verry much

#13 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 11:01 PM

it work as i asked.
However it not do the trick i thought i could do with it , it is to complex for me.
<?php


define('INDEX_FILE', true);
require_once("mainfile.php");
$module_name = basename(dirname(__FILE__));
get_lang($module_name);
include("header.php");
global $db;

	

	$result = $db->sql_query("select * from calendar");
OpenTable();

	
	while ($row = $db->sql_fetchrow($result)) {

        $products_id = $row["calendar_id"];
        		
?>

		<div id="products">
			<!-- DIV FOR A PRODUCT -->
			<div class="product_container">
<?php
echo"				<div id=\"slidingProduct_".$products_id."\" class=\"sliding_product\">";
?>
					<?php echo $row["did"];  ?><br>
					<?php echo $row["calendar_price"]; ?>
				</div>
<?php

echo "<a href=\"#\" onclick=\"addToBasket('$products_id','$calendar_date','$calendar_price');return false;\"><img src=\"modules/Fly_to_Basket/images/basket.gif\"></a>";
?>
			<!-- END DIV FOR A PRODUCT -->
			
					
		</div>			
<?php
	 
}
?>
			
		<a href="modules.php?name=Fly_to_Basket&file=cart">Your Shopping Cart &gt;&gt;</a>
					
	
<?php
 
CloseTable();

include("footer.php");

?>


your working example is totaly made as 1 function and including the calendar design.
i'm sure it must be like that.
But i cant get that running with the code i showed you.
i'm so upset and feel guilt for taking your time and help.


#14 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 08 October 2006 - 11:11 PM

it work as i asked.
However it not do the trick i thought i could do with it , it is to complex for me.

your working example is totaly made as 1 function and including the calendar design.
i'm sure it must be like that.
But i cant get that running with the code i showed you.
i'm so upset and feel guilt for taking your time and help.


What, exactly, are you trying to do?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#15 whitehat

whitehat
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 08 October 2006 - 11:23 PM

The code above comes from here:
http://www.dhtmlgood...-to-basket.html

i managed to make a shoppingcart of it
can see it here on my TEST live server:
http://www.inteladd....oductinfo&Pid=2

on my local i changed project and added 1 year dates as a product.
Anyway i want as you already now show these dates as products in a calendar style view.
Next year i just wanted to insert a new sql table for 2007.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users