Building A Dates Management System On Top Of Google Calendar Using PHP

06 December 2013


**Disclaimer: This was built a while ago. I'd probably do things differently if I built it again. Mostly this is just to chronicle my experience with something I built that I thought was cool (and useful).**

My band needed a good way to manage our show dates. In the past, we had been updating the html manually (ugh) and I decided I could do better.

After looking around, and not finding a good enough solution, (Most didn’t fit into what we needed, wouldn’t integrate very easily or weren’t as easy to manage as I wanted) I decided to roll my own. I built it on top of Google Calendar because I liked the idea of being able to update the website from my calendar app on my iPhone.

This was really helpful: Google Calendar API Reference

I started off with a basic script called fetchNewCalendarDates.php

 1 <?php
 2 $db = new PDO(
 3     'mysql:host='.
 4     $hostname.
 5     ';dbname='.
 6     $databasename,
 7     $user,
 8     $password);
 9 
10 $time = new DateTime();
11 
12 $today = $time->format('c');
13 
14 $path = "https://www.googleapis.com/calendar/v3/calendars/";
15 $calendarId = "mycalendargroup@group.calendar.google.com";
16 $localCalendar = "mytestcalendargroup@group.calendar.google.com";
17 $folder = "/events";
18 $params = "?timeMin=".$today;
19 $params .= "&timeZone=America%2FDenver";
20 $apiKey = "&key=myApiKey";
21 $request = $path . $calendarId . $folder . $params . $apiKey;

My intention here was to make it a bit easier to see what’s happening, but all I’m doing is concatenating a string with the required info to fetch events from a google calendar. $calendarId and $localCalendar are just two different calendars. One is live on the web, and one is a calendar I can use to test events with. We’ll make a GET request to the $request variable. It will return json. (line 3)

 1 <?php
 2 
 3 $json = json_decode(file_get_contents($request));
 4 if (empty($json->items)){
 5   //make sure events aren't empty
 6 
 7   $truncate = "TRUNCATE calendar";
 8   $statement = $db->prepare($truncate);
 9   $statement->execute();
10   $result['message'] = "Calendar Updated. No Dates Found On Calendar.";
11   $result['success'] = true;
12   echo json_encode($result);
13   exit;
14 }

All this snippet is doing is checking to make sure we actually have upcoming events (which is in $json->items). The way I set it up is if we didn’t, the table with the dates on our database gets truncated, and then we’ll echo a message to our front end, so we could ensure that it was doing something.

If we did have events, something like this would get returned:

{
 "kind": "calendar#events",
 "etag": "\"etag\"",
 "summary": "local test",
 "description": "local test calendar",
 "updated": "2013-12-02T19:13:13.259Z",
 "timeZone": "America/Denver",
 "accessRole": "reader",
 "items": [
  {
   "kind": "calendar#event",
   "etag": "\"etag\"",
   "id": "calendarId",
   "status": "confirmed",
   "htmlLink": "https://www.google.com/calendar/event?eid=eventId",
   "created": "2013-12-02T19:13:34.000Z",
   "updated": "2013-12-02T19:13:34.142Z",
   "summary": "Event!",
   "description": "here's a description",
   "location": "123 Main St",
   "creator": {
    "email": "dannstockton[at]gmail.com",
    "displayName": "Dann Stockton"
   },
   "organizer": {
    "email": "email@group.calendar.google.com",
    "displayName": "local test",
    "self": true
   },
   "start": {
    "date": "2013-12-03"
   },
   "end": {
    "date": "2013-12-04"
   },
   "transparency": "transparent",
   "iCalUID": "icaluid@google.com",
   "sequence": 0
  }
 ]
}

Next, I had a class that would do some stuff to the json that got returned from Google.

 1 <?php
 2 
 3 //creates new Events object
 4 $newEvents = new Events($json);
 5 
 6 //sorts by start time
 7 $newEvents->sort($json);
 8 
 9 //grabs relevant bits from json object
10 $container = $newEvents->parseEvents($json);
11 
12 //makes start dates/times human readable, and returns an array fullDate, day, month, date, time
13 $prettyStart = $newEvents->prettifyDate($container['cleantimes']['start']);
14 
15 //makes end dates/times human readable, and returns an array fullDate, day, month, date, time
16 $prettyEnd = $newEvents->prettifyDate($container['cleantimes']['end']);
17 
18 //get ticket links from description
19 for ($i = 0; $i < count($container['description']); $i++){
20   preg_match("/(tickets *: *)([a-zA-Z]*.[a-zA-Z]*)/i",
21   $container['description'][$i],
22   $tickets[$i]);
23 }

The snippet above looks in the description of each event, and if there’s “Tickets: url”, it’ll grab that info, and pop it into an array called $tickets. We’ll use this later. For example, if a description had:

Tickets: ticketmaster.com/link/to/event/125

The above would return the $tickets which would look like this:

<?php
    array (size=1)
      0 =>
        array (size=3)
        /* the string we're searching */
        0 => string 'Tickets: ticketmaster.com/link/to/event/125' (length=43)

        /* first group match */
        1 => string 'Tickets: ' (length=9)

        /* second group match */
        2 => string 'ticketmaster.com/link/to/event/125' (length=34)

$tickets will be useful in just a little bit. Next, We’ll get ready to put stuff into the database.

 1 <?php
 2 
 3 // Full url's of ticket
 4 $ticketLinks = array();
 5 
 6 $ticketPrices = array();
 7 $price = array();
 8 
 9 //insert links
10 for ($i = 0; $i < count($tickets); $i++){
11   if (!empty($tickets[$i][2])){
12     // finds the length of the entire matched ticket links
13     $substring = strlen($tickets[$i][0]);
14 
15     // truncate the desriptions so we can display just the description portion without the ticket links
16     $truncatedDescriptions[$i] = substr(trim($container['description'][$i]), 0, -($substring));
17 
18     // If the url of the tickets equals 'ticketlinks', that means we're selling tickets on our own, and we'll populate
19     // the url with our own link based on the event's UID.
20     if ($tickets[$i][2] == "ticketlinks"){
21       $ticketLinks[$i] =
22       "<a target='_blank' href='https://pagecxvi.com/".
23       $tickets[$i][2] .
24       "/?eventid=" .
25       $container['uid'][$i] .
26       "'>Buy Tix</a>";
27 
28     } else {
29 
30       // Handles external ticket links -- ie ticketmaster.
31       $ticketLinks[$i] = "<a target='_blank' href='http://" . $tickets[$i][2] . "'>Buy Tix</a>";
32     }
33   } else {
34     //in case there's no ticket link for an event
35     $ticketLinks[$i] = "<a href='#'>Buy Tix</a>";
36 
37     // no ticket links are present, so we just set the entire description to the truncated description which we'll use later
38     $truncatedDescriptions[$i] = $container['description'][$i];
39   }
40 }

The above code loops through each ticket link result, finds the length of the ticket, and then snips that part off of the description (since ticket links and description in google calendar are the same field.) This is sorta clunky, and if I did it again, I’d probably use str_replace instead.

Now that we have our descriptions and ticket links separated, we’re checking for the url portion of the ticket link match to be ‘ticketlinks’ which just means that we’re selling tickets on our own, and then the full html ticket link will be based on the UID of the event, and on our own website.

Otherwise, it just makes an html link to wherever we put in.

Next, we’ll look for ticket prices.

 1 <?php
 2 
 3 
 4 for ($i = 0; $i < count($truncatedDescriptions); $i++){
 5   preg_match("/[\$][\d]+[\.]?[\d]{0,2}/", $container['description'][$i], $price[]);
 6 }
 7 for ($i = 0; $i < count($price); $i++){
 8     if (isset($price[$i][0])){
 9         $ticketPrices[$i] = $price[$i][0];
10         $substring = strlen($price[$i][0]);
11         $truncatedDescriptions[$i] = substr(trim($truncatedDescriptions[$i]), 0, - ($substring));
12     } else {
13         $ticketPrices[$i] = "";
14     }
15 }

We’re using preg_match again to loop across all the descriptions, which looks for a ‘$’, followed by one or more digits, followed by an optional period, followed by 0-2 digits. If there’s a match, it’ll throw it into a $price array.

After that, we’re doing the really clunky truncate again, which means that for this to work properly (this could be made much better), the ticket links have to be the last thing in the description, otherwise, the substr won’t truncate the description properly. Again. something like str_replace is a better option, which would mean that the order wouldn’t matter.

Next, we’ll make the price into cents, which we’ll use later if we’re selling tickets on our own. (We’re using Stripe for payments, and you have to pass the amount to charge as an integer in cents. Which makes sense.)

1 <?php
2 
3 $pricesInCents = array();
4 foreach ($ticketPrices as $price) {
5     $price = substr($price, 1);
6   $pricesInCents[] = $price * 100.0;
7 
8 }
9 $container['priceincents'] = $pricesInCents;

All the above code is doing is taking in the ticket prices, (which look something like $20.00, and making it into 20.00, and then multiplying it by 100, to make it cents. It’s storing it in the $pricesInCents array.

We could have done this when setting the description, but since it gets a little confusing when looking at prices in cents, I wanted to make it more human readable when updating the calendar.

Next, we’ll insert everything in the database.

 1 <?php
 2 $sql = array();
 3 for ($i = 0; $i < count($container['summary']); $i++){
 4   $sql[] = "REPLACE INTO calendar(
 5       summary,
 6       ticketlink,
 7       ticketprice,
 8       location,
 9       description,
10       fulldate,
11       start,
12       end,
13       htmllink,
14       dow,
15       month,
16       day,
17       time,
18       year,
19       uid,
20       priceincents
21   )
22 
23   VALUES(
24       :summary,
25       :ticketlink,
26       :ticketprice,
27       :location,
28       :description,
29       :fulldate,
30       :start,
31       :end ,
32       :htmllink,
33       :dow,
34       :month,
35       :day,
36       :time,
37       :year,
38       :uid,
39       :priceincents
40   );";
41 
42   $statement = $db->prepare($sql[$i]);
43   $statement->bindParam(':summary', $container['summary'][$i]);
44   $statement->bindParam(':ticketlink', $ticketLinks[$i]);
45   $statement->bindParam(':ticketprice', $ticketPrices[$i]);
46   $statement->bindParam(':location', $container['location'][$i]);
47   $statement->bindParam(':description', $truncatedDescriptions[$i]);
48   $statement->bindParam(':fulldate', $prettyStart['fullDate'][$i]);
49   $statement->bindParam(':start', $container['cleantimes']['start'][$i]);
50   $statement->bindParam(':end', $container['cleantimes']['end'][$i]);
51   $statement->bindParam(':htmllink', $container['htmllink'][$i]);
52   $statement->bindParam(':dow', $prettyStart['dow'][$i]);
53   $statement->bindParam(':month', $prettyStart['month'][$i]);
54   $statement->bindParam(':day', $prettyStart['day'][$i]);
55   $statement->bindParam(':time', $prettyStart['time'][$i]);
56   $statement->bindParam(':year', $prettyStart['year'][$i]);
57   $statement->bindParam(':uid', $container['uid'][$i]);
58   $statement->bindParam(':priceincents', $container['priceincents'][$i]);
59   try {
60 
61     $statement->execute();
62   } catch (PDOException $e) {
63     error_log($e->getMessage());
64     exit;
65   }
66 }
67 
68 $result['success'] = true;
69 $result['message'] = "Calendar Updated";
70 
71 echo json_encode($result);

This is pretty straightforward. If I was doing it again, I’d just have a big events object that you could get/set information from, like start time, ticket price, description, etc.

After that, all we’re doing is echoing to our front end that we’ve updated the calendar.

Again, this is pretty old code. If I were doing it again, I’d make everything more OO, and break out the work into separate objects and classes. That way it’s not a giant single file that’s sort of hard to debug/enhance.

All we need to do is enter some info on the google calendar, (which at the time, I had set up on my iPhone), and it will take care of putting it in a database, which then would display on the front end of the website.

Thanks for reading!