I’ve been running my “T-Comm” Bus Locator website for over seven years now. As a quick recap, on the backend, it’s a procedural PHP/MySQL website, kind of a typical thing one would see of PHP development circa 2000s. The high level overview is that every minute or two, the system polls TransLink’s real time information APIs for the locations of the all the buses in the system. The system then calculates some information based on TransLink’s GTFS data, which has the schedule information for the entire system. Then it saves the data into a MySQL database, and some other outputs Continue Reading
mysql
Checking for a Null value in SQL
Yesterday I fell into a very simple but dangerous SQL trap. Can you identify it? Found it? It’s the comparison to null. My intention was to get rows where another_column was null, however the query did not do so. It didn’t give me any results where another_column was null, but didn’t give me any error either. Why? Null in SQL is not a real value. Null means “lack of a value” or “unknown”. Anything compared to null leads to an “unknown” result. Therefore, in SQL there are actually three values conditions can evaluate to: true, false, and unknown/null. This is called Continue Reading
Database query optimization is a useful skill
Recently I read an article titled “Quick tip: Improve developer habits by showing time cost of DB queries” by Dan Birken. He claims that one common reason why websites load slowly are because of unoptimized database queries: Websites can be slow for many reasons, but an overwhelmingly common one is time spent performing database queries. Now database queries are likely a very important part of rendering the webpage – the information to populate a page has to come from somewhere. But sometimes rendering a page is slowed down by either unnecessary queries or improperly optimized queries. I completely agree with his view. For most Continue Reading
Side Project 1: Real Time Bus Map in Vancouver
Since TransLink released their new mobile Next Bus site with real-time GPS updates of bus locations, I’ve been trying to find ways to get the data and rehash it into something that Metro Vancouver transit enthusiasts (more specifically, enthusiasts who chase buses and monitor the transit system’s operation) will find useful. There were two main shortcomings of TransLink’s site from the viewpoint of a transit enthusiast: Can’t search for a specific bus. Often times transit enthusiasts “chase” a particular bus, usually a new bus, a fresh bus after a midlife refurbishment, or a bus with a new advertisement wrap. Can’t Continue Reading
MyBB Security
These are just my thoughts about MyBB security updates. I’m not a security expert of any sort, but I just offer my opinion based on the knowledge I have. Over the last few weeks there have been two releases to MyBB to patch potential security vulnerabilities that have been discovered by various parties. I have seen some people who have found these seemingly miniscule updates too trivial to apply to their own boards, despite the fact that I and various other members of the MyBB staff have recommended these updates. These people seem to believe that just because no harm Continue Reading