Need Help With Some Calendar Code.....

[dazzathedrummer]

Hi,

 

I've been looking for a way to create an iCal feed from my calendar database (a MySQL db that my users update from php forms).

 

I found the following code on this blog...http://stevethomas.c...-and-mysql.html

 

code:

<?php

// the iCal date format. Note the Z on the end indicates a UTC timestamp.
define('DATE_ICAL', 'Ymd\THis\Z');

// max line length is 75 chars. New line is \\n

$output = "BEGIN:VCALENDAR
METHOD:PUBLISH
VERSION:2.0
PRODID:-//Thomas Multimedia//Clinic Time//EN\n";

// loop over events
foreach ($query_appointments->result() as $appointment):
$output .=
"BEGIN:VEVENT
SUMMARY:$appointment->firstname $appointment->surname
UID:$appointment->id
STATUS:" . strtoupper($appointment->status) . "
DTSTART:" . date(DATE_ICAL, strtotime($appointment->starts)) . "
DTEND:" . date(DATE_ICAL, strtotime($appointment->ends)) . "
LAST-MODIFIED:" . date(DATE_ICAL, strtotime($appointment->last_update)) . "
LOCATION:$appointment->location_name $appointment->name
END:VEVENT\n";
endforeach;

// close calendar
$output .= "END:VCALENDAR";

echo $output;

?>

 

....it looks like it would do the trick.......I'm not brilliant at PHP, but I get by OK.......I assume that all I need to do is create a query from my DB that will populate the fields in the FOREACH loop.

 

I created a query and tested it by outputting it to a table - it works fine....I get a full list of my calendar entries.

 

So, I put query into the code like this (you'll note that some of the fields are dummies for testing purposes)..

 

<?php
// Connects to your Database

include '/connect_include.php';

$query = "
SELECT
gl_id as 'id',
'' as 'last_update',
'name' as 'name',
'fname' as 'firstname',
'sname' as 'surname',
'confirmed' as 'status',
concat(left(gl_date,4), mid(gl_date,6,2), mid(gl_date,9,2),'T210000Z') as 'starts',
concat(left(gl_date,4), mid(gl_date,6,2), mid(gl_date,9,2),'T235959Z') as 'ends',


case
when gl_unavailable = '1' then gl_comments
else gl_venue end as 'location_name'



FROM tg_gig_list
where gl_date >= curdate()
and year(gl_date) = year(curdate())
and gl_delete <> 1
order by gl_date asc";
$query_appointments = mysql_query($query);


// the iCal date format. Note the Z on the end indicates a UTC timestamp.
define('DATE_ICAL', 'Ymd\THis\Z');

// max line length is 75 chars. New line is \\n

$output = "BEGIN:VCALENDAR
METHOD:PUBLISH
VERSION:2.0
PRODID:-//Thomas Multimedia//Clinic Time//EN\n";

// loop over events
foreach ($query_appointments->result() as $appointment):
$output .=
"BEGIN:VEVENT
SUMMARY:$appointment->firstname $appointment->surname
UID:$appointment->id
STATUS:" . strtoupper($appointment->status) . "
DTSTART:" . date(DATE_ICAL, strtotime($appointment->starts)) . "
DTEND:" . date(DATE_ICAL, strtotime($appointment->ends)) . "
LAST-MODIFIED:" . date(DATE_ICAL, strtotime($appointment->last_update)) . "
LOCATION:$appointment->location_name $appointment->name
END:VEVENT\n";
endforeach;

// close calendar
$output .= "END:VCALENDAR";

echo $output;

?>

 

.....with this I'm getting an error on the foreach line.....presumably the loop is unable to load the data for some reason??

 

I must admit that the syntax for the loop isn't something that i've seen before - I usually use a "while($row = mysql_fetch_array($result, MYSQL_ASSOC))" and then call the fields using {$row['name']} etc.

 

Would appreciate some pointers on this.

 

Thanks,

 

Darren

[PFMaBiSmAd]

The example you found was for CodeIgniter. The ->result() method returns an array of the rows the query matched.

 

You would replace the foreach(){} loop with your traditional while(){} loop.

[dazzathedrummer]

...yeah - I tried to rebuild the script from scratch....

 

<? 
// Connects to your Database 
include '/connect_include.php';

$query  = "
SELECT 
gl_id as 'id',
'' as 'last_update',
'name' as 'name',
'fname' as 'firstname',
'sname' as 'surname',
'confirmed' as 'status',

concat(left(gl_date,4), mid(gl_date,6,2), mid(gl_date,9,2),'T210000Z') as 'starts',
concat(left(gl_date,4), mid(gl_date,6,2), mid(gl_date,9,2),'T235959Z') as 'ends',

case 
when gl_unavailable = '1' then gl_comments
else gl_venue end as 'location_name'
FROM tg_gig_list 
where gl_date >= curdate()
and year(gl_date) = year(curdate())
and gl_delete <> 1
order by gl_date asc";
$result = mysql_query($query);
define('DATE_ICAL', 'Ymd\THis\Z');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
   echo 
"BEGIN:VEVENT
SUMMARY:{$row['surname']}.
UID:{$row['id']}".
strtoupper("{$row['status']}").
date(DATE_ICAL, strtotime("{$row['starts']}")).
date(DATE_ICAL, strtotime("{$row['ends']}")).
date(DATE_ICAL, strtotime("{$row['last_update']}")).
"{$row['location_name']}".
"END:VEVENT\n"
;

}

mysql_close();
?>

 

This gives the following output.....

 

BEGIN:VEVENT SUMMARY:sname. UID:2310CONFIRMED20121123T220000Z20121124T005959Z19700101T010000ZDazza unavailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2249CONFIRMED20121124T220000Z20121125T005959Z19700101T010000ZO'Neill'sEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2226CONFIRMED20121130T220000Z20121201T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2227CONFIRMED20121201T220000Z20121202T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2395CONFIRMED20121201T220000Z20121202T005959Z19700101T010000ZO'Neill'sEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2228CONFIRMED20121202T220000Z20121203T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2306CONFIRMED20121207T220000Z20121208T005959Z19700101T010000ZThe Burghley ClubEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2401CONFIRMED20121208T220000Z20121209T005959Z19700101T010000ZThe London InnEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2397CONFIRMED20121214T220000Z20121215T005959Z19700101T010000ZEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2139CONFIRMED20121215T220000Z20121216T005959Z19700101T010000ZO'Neill'sEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2230CONFIRMED20121215T220000Z20121216T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2398CONFIRMED20121215T220000Z20121216T005959Z19700101T010000ZEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2231CONFIRMED20121216T220000Z20121217T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2399CONFIRMED20121216T220000Z20121217T005959Z19700101T010000ZEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2250CONFIRMED20121221T220000Z20121222T005959Z19700101T010000ZQuinnsEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2312CONFIRMED20121222T220000Z20121223T005959Z19700101T010000ZDazza unavailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2232CONFIRMED20121228T220000Z20121229T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2233CONFIRMED20121229T220000Z20121230T005959Z19700101T010000ZDazza unanvailableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2256CONFIRMED20121229T220000Z20121230T005959Z19700101T010000ZGav not availableEND:VEVENT BEGIN:VEVENT SUMMARY:sname. UID:2234CONFIRMED20121230T220000Z20121231T005959Z19700101T010000ZDazza unanvailableEND:VEVENT

 

....so, my (dumb) question now is......how do I get line breaks into this?

[dazzathedrummer]

Can anyone help with this?

 

or point me in the direction of a script that I can use?

 

thanks,

 

Darren

[Christian F.]

The easiest method is to use the HereDoc syntax to create the iCal item. It works pretty much the same as a double-quoted string, except that quotes don't need to be escaped in it. Function calls will also have to be done before defining the string, since you don't want to close and open the HereDoc block for every variable you want to have in there.

 

Another alternative is to add "\n" to the string.