Practicing with PHP and MySQL

Well, here it is. It’s a start anyway. I had to upload it onto a different host since Freehostia still seems to be broken. I want to give a bit of a run down on how this works and what I have learned about PHP and MySQL so far.

I was quite inspired by sites that allow you to view and download GPS waypoints, and thought it would be cool to be able to generate GPX files ‘live’ from a Database rather than having them stored statically.

Firstly I got the Microsoft Access version of the UK Hills database

After fiddling around for a while trying to import CSV files into MySQL I found an online guide that showed how to set up an ODBC connection . This worked perfectly and I exported the resultant table from MySQL so I was able to import it to my development environment.

The page uses PHP, the user enters search criteria in the form ( which needs a bit more thought yet !) . When the form is submitted, PHP  creates a Database Query to return the requested results. PHP then builds an HTML table and this is sent back to the user . I had to get the REGEXP manual out to allow searching for the classification string. The database uses a ‘listed field’ ( tut tut ) eg. W,N for Wainwright,Nuttal .

On the client side Javascript is used to enable columns of the table to be hidden  or shown and the table to be sorted. Each table row also has an on-click event handler which triggers the drawing of a map, using the Google API.

Upcoming work in no particular order:

-add ability to page through results, using LIMIT in Query

-make it look pretty, fix the layout !

-create a GPX file from selected hills, for peak bagging GPS users !

-see if colgroups can be used in the table instead of individual CSS

-create PDF output to enable printing of check lists

You may wonder why there are two sets of table headers at the moment. It’s because I initially wanted to make the table scroll whilst keeping the map in view.. Suffice it to say that this is not a particularly easy thing to do if you want to keep the headers visible and aligned. You can see they are different widths, which is because of the the way the browser renders tables based on the content. The top single row table gets different column widths since it has not other rows ! Hmmm…. ?



Leave a Reply

Your email address will not be published. Required fields are marked *