Jump to content

convert mysql data to a calendar


whitehat

Recommended Posts

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
Link to comment
Share on other sites

[code]

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>

[/code]
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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

[code]
<?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>
[/code]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[quote author=whitehat link=topic=110884.msg448981#msg448981 date=1160336540]
i have this question posted in 4 different forums...
i retrieve excact same questions as you now ask.

[/quote]

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

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

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

I added a few comments to help

[code]
<?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>
[/code]
Link to comment
Share on other sites

it work as i asked.
However it not do the trick i thought i could do with it , it is to complex for me.
[code]
<?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");

?>
[/code]


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.
Link to comment
Share on other sites

[quote author=whitehat link=topic=110884.msg449025#msg449025 date=1160348468]
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.

[/quote]

What, exactly, are you trying to do?
Link to comment
Share on other sites

The code above comes from here:
http://www.dhtmlgoodies.com/scripts/fly-to-basket/fly-to-basket.html

i managed to make a shoppingcart of it
can see it here on my TEST live server:
http://www.inteladd.com/modules.php?name=Fly_to_Basket&file=productinfo&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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.