Game Reviews Aggregation Website

Links:

Github Repositiory
www.gameratingsapp.com

Languages/Tools Used:

HTML
CSS & Bootstrap
JavaScript
PHP
MySQL
AWS (EC2, RDS, Route 53, S3 Bucket)
VS Code
MySQL Workbench

Quick Summary

This web application was developed using full stack technologies, including HTML, CSS, Bootstrap, and JavaScript for the front-end. The back-end is powered by PHP and MySQL (originally MS SQL), which handle dynamic data storage and access. The application is hosted on AWS, utilizing an EC2 instance for hosting the site and its code, RDS for the database, and Route 53 for DNS management. The site also utilizes SSL encryption for security.

The Goal

I wanted to build a dynamic website that gave me full stack development experience. I landed on the idea of making a review aggregation website much like metacritic.com or rottentomatoes.com, but specifically for video games. It was the perfect idea as it required building the front-end code as well as back-end components such as a database and the scripts to communicate between them. It also provided me with experience setting up web hosting services to get the site live.

What I learned

Research and planning was key. From designing the database appropriately to finding the best tools to accomplish the vision, doing my research and planning the project accordingly was the biggest takeaway. For example, I chose MS SQL initially for my database and SQL queries as it was what I was familiar with, but finding help and documentation for it was a challenge when it came to accessing data from it for the website and slowed down my development time. I later ported the database and queries to MySQL due to its popularity and support and found it much easier and quicker to set up (documentation and support was way better).

I also discovered a lot about AWS’s service costs and how to be forward thinking when selecting the services and tiers. Thankfully AWS has free tiers for 12 months, but once the 12 months are up, I needed to find a way to keep the site running and not get hit with a heavy bill. So I made sure with the scope of my project that I could select the tiers with the lowest computing power to incur the least cost. Also, while I initially had the database on an RDS instance for a number of months, I moved it over to the EC2 that the site is being hosted on to avoid the charges of the RDS instance once the free tier would expire (RDS was looking to be the biggest cost factor). Ultimately, I would prefer to have the database on its own RDS instance if my site had enough traffic and a large enough database, but given my site’s current state and traffic, this seemed like a reasonable change to make to lower costs.

How it was Built

I started with planning out the scope of the project, what pages I needed, the features I wanted, and the database tables I needed and what the relationships would be.

Once the scope was locked down, I used MS SQL Server Management Studio to start building the database with tables for the games, game developers, game publishers, reviews, and the platforms games are on. I populated the database with just sample data of about 60-70 games.

The next step was to build server-side PHP code to interact with the database and display the data on a webpage. I installed PHP drivers for SQL Server to utilize functions to connect with the database and used XAMPP to test this locally. That first moment I saw the test print statement “Test connection successful” in my browser was exhilarating! I then started building SQL query statements to access the data I knew I’d need to display on my website and built out a few PHP functions to have this data sent back to the site once it’s developed.

Next I developed the homepage of the website using HTML and CSS to house where the data would be displayed. The first iteration of the homepage was very rudimentary and mimicked the now defunct gamerankings.com home page just to use as a starting point. I built sections for Most Popular Games and Most Recent Releases.Then I built JavaScript functions that would inject the html with the data from the database by calling the PHP functions I previously built. Seeing the games populate the webpage with the appropriate data was another moment of beauty! There’s nothing more rewarding than when the different components of a project start coming together flawlessly!

I proceeded to build out the web page for an individual game that used the GameID in the URL to fetch the appropriate game from the database. I also built a search page with search functionality. This was a tricky undertaking as I wanted to have advanced search functionality that allowed a user to filter by any combination of Developer, Publisher, Platform, release year, and entered text for the game’s title. To capture all of this in essentially a single SQL query really flexed my SQL query muscles, but once again was a very rewarding moment once I was able to successfully plan out the query ( all SQL queries plus all site code can be found in the github repository above, if your curious).

So by this point I had my database up and running along with a functioning web pages and server-side code to connect between them. Great! Now I needed to migrate this to a web hosted server.

This was one of the more challenging portions of the project as hosting a live dynamic website was new and foreign to me. I did some research and landed on AWS as it seemed the most popular, had lots of documentation, large community support, and a free tier plan to take advantage of. I set up my database on an RDS instance and all my front-end and server-side code on a Linux EC2 instance. I had to do a lot of reading on how to configure both instances as there are a lot more components to each than I expected, but eventually I set up permissions and security groups to allow connections between the RDS and EC2 instances and huzzah! My website was working publicly on the web! Best feeling! The last piece was to buy a domain and get it to route traffic to my EC2 server. I used AWS Route 53 to set this piece up.

Cool, I can access my website from the web, but there are a couple things I need to address. First, I’m getting warnings when I visit my site that it is not secure. So I looked into free SSL certificates that could secure my website and found a wonderful free certificate authority with Let’s Encrypt. Got this setup with a straight forward tutorial and now my website is secure!

The second thing I needed to address is the appearance of my website. I didn’t like how simple it looked and how little CSS I utilized, so I took it upon myself to learn CSS more in-depth as well as take advantage of the nice built in styling features that CSS Bootstrap 5 has to offer. I utilized CSS Bootstrap features such as its super handy grids system, as well cards, modals, and size flexibility to keep my site looking clean on any resolution. My proudest piece, however, is the horizontal accordion effect for the “Most Popular” section at the top of the homepage that I built with pure CSS (No Bootstrap!). It took a lot of iterating and trying different things to get what I had envisioned in my head for this section, but eventually figured out how to use a hidden radio selector to identify which game is being clicked on and learning CSS selectors and a host of pseudo-elements to have the accordion and game data inside animate. And voila, the beautiful current state of my website!