Comisión de Espectáculos Públicos y Actividades Recreativas de la Comunitat Valenciana

As outlined at the start of the book, PHP is commonly used to make web content dynamic. We are going to use it to do exactly that by getting it to glue together our leaflet.js JavaScript and a MySQL Database. The end result should be a web page that will leverage the significant storage capability of a MySQL database and the ability to vary different aspects of returned data.

If you’re wondering what level we’re going to approach this at, let me reassure (or horrify) you that it will be in the same vein as the rest of this book. I am no expert in MySQL databases, but through a bit of trial and error I have been able to achieve a small measure of success. Hopefully the explanation is sufficient for beginners like myself and doesn’t offend any best practices :-).

phpMyAdmin

I’m not one to dwell on the command line for too long if it can be avoided (sorry). So in this section you’ll see me delving into a really neat program for managing your MySQL database called phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php).

As the name would suggest, it’s been written in PHP and as we know, that’s a sign that we’re talking about a web based application. In this case phpMyAdmin is intended to allow a wide range of administrative operations with MySQL databases via a web browser. You can find a huge amount of information about it on the web as it is a freely available robust platform that has been around for well over a decade.

If you have followed my suggestion earlier in the book to install WAMP¹⁸² or you have phpMyAdmin installed already you’re in luck. If not, I’m afraid that I won’t be able to provide any guidance on its installation. I just don’t have the experience to provide that level of support.

MySQL Tips and Tricks for leaflet.js 122

Create your database

Assuming that you do have WAMP installed, you will be able to access a subset of its functions from the icon on your system tray in the lower right hand corner of your screen.

The WAMP server icon

Clicking on this icon will provide you with a range of options, including opening phpMyAdmin.

Opening phpMyAdmin

Go ahead and do this and the phpMyAdmin page will open in your browser.

MySQL Tips and Tricks for leaflet.js 123

The Databases tab

From here we can create ourselves a new database simply by giving it a name and selecting ‘Create’. I will create one called ‘homedb’.

Give our new database a name That was simple!

On the panel on the left hand side of the screen is our new database. Go on and click on it.

Open the homedb database

MySQL Tips and Tricks for leaflet.js 124

Databases and Tables

Ahh yes… Think of databases as large collections of data (yes, I can smell the irony). Databases can have a wide range of different information stored in them, but sometimes the data isn’t strictly connected. For instance, a business might want to store its inventory and personnel records in a database. Trying to mash all that together would be a bit of a nightmare to manage. Instead, we can create two different tables of information. Think of a table as a spreadsheet with rows of data for specific columns. If we want to connect the data at some point we can do that via the process of querying the database.

So, lets create a table called data2 with three columns.

Create a table

I’ve chosen data2 as a name since we will put the same data as we have in a file called data2.csv. That’s why there are three columns for the date, close and open columns that we have in the data2.csv file.

So, after clicking on the ‘Go’ button, I get the following screen where I get to enter all the pertinent details about what I will have in my table.

Format the table’s columns

I’m keeping it really simple by setting the ‘date’ column to be plain text (It could be set as a ‘DATE’ format, but we’ll keep it simple for the time being), and the two numeric columns to be decimals with 8 digits overall and 2 of those places for the digits to the right of the decimal point.

The selection of the most efficient data type to maximise space or speed is something of an obsession (as it sometimes needs to be) where databases are large and need to have fast access times, but in this case we’re more concerned with getting a result than perfection.

MySQL Tips and Tricks for leaflet.js 125 Cool, now you are presented with your table (click on the table name in the left hand panel) and the details of it in the main panel.

The details of the ‘data2’ table Sure it looks snazzy, but there’s something missing….. Hmm….. Ah Ha! Data!