Sunday, January 25, 2015

Video Game Database Tutorial Part 2: Displaying your Database in HTML using PHP and CSS

Welcome to Part 2 of my tutorial on setting up a simple video game database using MySQL and PHP. In Part 1, I explained how to import a spreadsheet file into MySQL which contained the video game information we needed to populate our online database table. For this second installment, I will explain how you can connect to your MySQL video game database and display it in a web browser using PHP. I will also show you how to make your table look great using several CSS3 tricks.

A database connection is always necessary for accessing content through MySQL. It is in good practice to always keep your PHP connection code in a separate file which can be easily inclue or require within any other php file. Doing so will make things a bit more secure, and less of a headache when moving your database to an online server.

For my  database, I created the following connection file using the setup from Part 1 of my tutorial. It also checks to see if a connection is made, and displays an error message if something goes wrong.

Next we create a PHP/HTML file (xboxGames_table_display.php) containing the code that will display the the table. The connection file we created previously, xboxGames_sql_connect.php, is linked using require.

The syntaxt for defining a link based on MySQL table data is Screenshot, where as anything between the > and < will serve as the display name of the link. If you'd like to display the actual path and file name as the link, you can change it to .$screenshot. We also link to a CSS file called (xboxGames_table.css) which we will create next.

Currently, your table should display like the image below.

PHP will automatically close your database connection when this PHP script finishes running. Out of habit, I like to close the connection myself at the end of each file using mysqli_close();.

Now it's time style our table with CSS to make it more appealing and easier to read. I chose the Green Background of the page to match the official color used on the Xbox Live website. I also decided to switch the default font to a serif font as they tend to be easier to read on screen. Please refer to the notes within the code for further explination.

After applying our cascading style sheet, the table should look like the image below.

Now that we've set up our initial table, Part 3 of my tutorials will attempt to apply functionality to the database. Ultimately, I would like to be able to add and delete table entries as I see fit, while still keeping the rows sorted in alphabetical order. Eventually, I'd like to take it a few step further and create a JavaScript lightbox to display the screenshots in, and I would also prefer create a few search features allowing for useful databse queries to be executed.

As always, comments and discussion are appreciated. Part 1 of the tutorial can be found at the link below.

Video Game Database Tutorial Part 1: How to Import a Spreadsheet into a MySQL Database Table.

No comments: