Building That Schedule Maker


That Conference is a “summer camp for geeks” starting on August 11th. It’s a three day technical conference packed with talks on mobile and web software development. There are over 150 sessions, but only so many timeslots. Which leads to some tough decisions- which talks will you attend, and which will you have to miss?

In trying to evaluate That Conference and its value to me as a developer, I wanted to decide what sessions I would attend for each timeslot. However, there is so much session detail on That Conference’s schedule page that it’s difficult to get a top-level view. I started to put the data into a spreadsheet (this was before they added the export option), but soon realized it would take way too much time. Being a software engineer, I started thinking of how I could automate the task. I opened up Chrome’s Developer Tools and saw the schedule data was in JSON format. Perfect!

Approach

I was already using bootstrap on my site, so I didn’t need to spend too much time on the design and CSS. Using knockout, I created the view by looping through each day, timeslot, and session. I also had to add loops in each session to build a list of speakers and tags.

UX

The hard part was deciding how to keep my goal of keeping it simple and maintaining a high-level view. I decided on only listing the session’s title and tags under each timeslot. That should be enough information for most users (or just me) to know if they are interested in the talk or not. The rest of the information is hidden until the session is selected. To alert the user that a row is clickable, each row highlights on hover (bootstrap class “table-hover”) and the cursor is a pointer. When the user clicks a row, a bootstrap dialog box opens containing the rest of the session’s info, such as speakers, the description, and room location.

Building the Schedule

Each session can be added or removed to the user’s schedule in the session dialog box, which adds the session ID to a Javascript array. When the user is done, they click save to bring up the final dialog box. This box requires an email address to send the schedule.

PHP

On post, the server receives the email address and session IDs. Validating the email address is simple in PHP by calling filter_var($userEmail, FILTER_VALIDATE_EMAIL). Then the script loops through the array of session IDs and builds the HTML table. The PHP loads the same JSON file the client uses to look up the session details using the ID. Once the HTML message is built, the email is sent and the post returns a JSON response indicating if the send was successful and an HTML message to display.

SQL

To get a unique ID for each schedule that is created, I decided to save the information in a MySQL database. I created three tables: tc_schedule, tc_sessions, and tc_schedules_sent. A check is first performed to see if the schedule configuration has already been used. If it has, it returns that schedule’s ID previously generated by MySQL.

$dbh = new PDO("mysql:host=localhost;dbname=my_database", $user, $pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sessionInValues = implode(",", $addedSessions);
$stmt = $dbh->prepare("SELECT scheduled.schedule_id, COUNT(*) as sessions, 
    IFNULL(missing.sessions,0) as missing_sessions
    FROM my_database.tc_schedule_sessions scheduled
    LEFT JOIN
    (SELECT schedule_id, COUNT(*) as sessions 
        FROM my_database.tc_schedule_sessions
        WHERE session_id NOT IN (" . $sessionInValues . ")
        GROUP BY schedule_id) missing
	ON scheduled.schedule_id = missing.schedule_id
	WHERE scheduled.session_id in (" . $sessionInValues . ")
	and IFNULL(missing.sessions,0) = 0
	GROUP BY scheduled.schedule_id
	HAVING COUNT(*) = :session_count;");
$stmt->execute(array('session_count' => count($addedSessions)));
$dbSchedule  = $stmt->fetch();

If no record is returned, the script inserts the new schedule configuration and returns the newly created ID.

Site Metrics vs. Privacy Concerns

After the sessions are in the database and the ID is known, I wrote the script to save an email receipt, including schedule ID, email address, and date sent. I’ve mostly worked for a large enterprise, so my data logging philosophy is to save as much as possible, because it’s usually requested at some point. This is fine when developing software for internal use in a corporation, but this tool is for anyone to use and doesn’t contain any privacy disclaimers.

I didn’t have plans to use any email addresses stored, so why record the information? After talking it over with a colleague, it was clear I should remove that code to respect each user’s privacy. I dropped the email address column from the database, and added a message about privacy to the schedule maker page. I am still recording what schedule configuration was sent and when, because it could be interesting to see what sessions are generating interest. There is no user information recorded along with it, so I feel that it’s a good balance between collecting useful data while respecting privacy.

This was a fun project to tackle. I found that the programming work for this tool was pretty simple. The biggest challenge was deciding on the interface and user experience. With so many sessions to display, I tried to bring the focus on the most important information, while still making additional details easily and obviously accessible when desired. Try out That Schedule Maker and let me know what you think.


Tags: php sql
comments powered by Disqus

About

Hi, I'm Dominic. I'm a software developer working mostly with .NET and other web technologies. Check out some of my projects, or get in touch with me.