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.

Sunday, January 4, 2015

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

In Part 1 of this tutorial, I will show you how to convert a spreadsheet file into a Comma-Separated Values (CSV) file and ultimately import the values into a MySQL database table. While there are more direct software options for achieving this, I feel that this method is the most flexible in terms of troubleshooting and customization.

For this tutorial, I will be using OpenOffice Calc 4.1.1 to create my spreadsheet file. However, the tutorial should work exactly the same for Microsoft Excel, and any other spreadsheet software that can save to CVS format. I am also using WAMP 2.5 64 bit through Windows 7 to build and test this database. Your end results may vary slightly depending on your system's configuration. Please refer to the WAMP website for the latest version and install instructions.

You can begin by creating a sample spreadsheet. You can choose however many columns and rows as you wish. For this example I created an spreadsheet cataloging my xbox 360 game collection. The spreadsheet contains 55 rows, one of which is a header, and 5 columns of data. The columns contain the name of the game, a link to a screenshot for each game in a sub-directory, the year the game was published, its genre, and if I own it as a Digital Download or actual DVD.

  1. First step, save your spreadsheet file as a CSV text file using the File > Save As In OpenOffice Calc, a export option window will pop-up asking you what character set to use (see Figure 1). For this example, I kept the default Western Europe Windows 1252 character set option. (PLEASE NOTE that your spreadsheet program will only save the currently visible spreadsheet to CSV format. For a workbook with multiple spreadsheets, you will have to save each spreadsheet individually.)

  2. Open your CSV file in any text editor such as Windows Notepad to check it.

  3. Open your MySQL console.

  4. First we will have to create a new database. For my example, I'll name my database xboxGames and then select it for use.

  5. Once selected, it is time to create a table for your database. In this case, I will create one called xbox360 which will contain 5 columns keyed in as “Game”, “Screenshot”, “Year”, “Genre”, and “Media”. As you can see, in the code below, all of the columns are character fields (CHAR) with the Year being strictly and integer (INT). In the command, I also set the ID to be an auto increment integer that will act as a primary key.

  6. We will now import the CSV using the load local data command in the MySQL console. Note that the command is set to ignore the first line of the CSV file which contains the Header row that was present in my original Spreadsheet.

  7. If you receive no errors, you should be able to enter the following command to successfully view your newly populated table within the MySQL console window.
In part 2 of my tutorial, I will show you how to create a PHP file to display the database on a web page and how to format it using CSS3

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