Géza Anda , MySQL and PHP

The past two years, I've remembered Géza Anda's passing on this blog.  I was so busy this year I forgot.  I've been listening to a lot of Ivan Moravec these days, but I pulled out some of my favorite Anda recordings again.  The Schumann Fantasie and Symphonic Etudes played by Anda are always interesting.  He can really rush things sometimes.  I think I should pull out the Mozart piano concerto set I was given last Christmas too.  I've been listening to Murray Perahia's set in my car.

Tonight, I figured out how to make receipts from a MySQL database.  It's nothing fancy.  I just used PHP to read the records from MySQL and then had it generate numbered receipts by using HTML tables to format them.  I also figured out how to use PHP to display an RSS feed on this page.  I really like open source.  My default office suite is LibreOffice.  Python, Inkscape and Audacity are among the other open source tools I use.  I used Audacity to analyse sound and I used it in my kid's grade 4 class to check who could play a violin note in tune.

Today, a student had trouble with a Powerpoint file generated from a Mac app (not Office Mac).  I had asked her to save it as PPTX, but that was not possible as she didn't have Office Mac.  I converted the slides to images and was about to insert an image library into Powerpoint 2010 when I remembered an online conversion service.  The Mac PPT wouldn't load into Powerpoint 2010 but it did load into LibreOffice 3.5 with glitches (funny how the open source product worked better huh?). I remembered a service I had used successfully before.  I did a Google search "z format conversion" and found Zamzar.  The offending PPT was uploaded to Zamzar and within a few minutes, I was sent an e-mail with a link to the converted file.  Voila, it worked!  The audio files were in M4A format.  I tried VLC Player to convert to MP3 without success.  Next I used Adobe Soundbooth to convert M4A to MP3 and it worked.  Finally, I use Audacity to edit her 3 converted MP3 audio clips together as the background music for the PPT.  I am more familiar with Audacity than SoundBooth (that's why I didn't edit and merge the clips from SoundBooth).

The right tool makes all the difference

I am thoroughly impressed with MySQL and PHP.  The software DBQwikSite is another tool I think highly of.  I have been thinking of how to create a database to calculate school activity points for the last few years.  I started with thinking about how it could be done with Delphi by programming it straight from scratch.  Then I thought about Access.  Then I thought about Delphi or VB.NET to connect to any database.  I could not make very much progress because I was using the WRONG TOOL.  Then, I was exposed to MySQL and PHP through Xibo digital signage.  That was my first exposure to PHPMyadmin to work with MySQL databases.  Last summer, I discovered DBQwikSite.  This slick software lets you create a data driven website without programming.  However, it makes it easy for you to insert custom code (HTML, PHP) so that you can customise the site.  I read Paul Litwin's relational database primer to figure out how to design the two tables required.  I learned about some aspects of MySQL and PHP through W3Schools.  I had to do numerous Google searches to figure out various things

• how to deal with NULL values
• how to use MySQL VIEW to create virtual tables (this is so you can make computed fields).
• how to pass a parameter from one PHP page to another PHP page.
• how to secure the database with PHPMyadmin (create users) and DBQwikSite
• creating reports by outputting the SELECT query result (which is an array)

It's all come together and the database website works quite well!

MySQL and PHP - Open Source at its best!

I've been playing around with MySQL and PHP to create a data enabled website to figure out the activity points for a couple of schools. It's been quite a journey that began with being asked to teach Information Processing about 5 years ago. I had to learn about databases in order to teach IP20. Things started to accelerate last year when I setup a Xibo digital signage server.  Xibo requires a web server and a MySQL database server.  It was through my experience with Xibo that I learned how to use PHPMyAdmin.  I made some initial forays into PHP when I met a teacher from Tisdale who used PHP / MySQL to teach computer science 30.  Then during the summer while visiting my parents, I found a great free database generator - DBQwikSite.  There was still plenty to learn though.  As second semester rolled around, I sat down to figure out how to turn a page of paper data entry into a database.  The key was that I figured it would take two tables.  One table would be to store student data (student number, last name, first name, gender, grade) and a second table to store activity data (student number, activity name, activity points for each grade, activity category).  The primary keys (unique fields) of each table is italicised.  The eureka moment was recognising that the activity data table needed TWO primary keys.  Litwin's paper is a good guide to relational database design. I found out I could host MySQL databases on a free website I had and it went from there.

A few annoyances and design considerations I found:

• Unix servers are case sensitive (for both file names and the MySQL database field names etc).  This was a pain when transitioning from my Windows laptop (localhost) to the webserver.  I was using Windows to develop since I was using DBQwikSite.  I should have just used all lower case!
• Sometimes when copying from Google Docs to PHPMyadmin, the apostrophe wouldn't copy correctly and I would get a MySQL error.  I had to retype the apostrophe in PHPMyadmin to fix that one!
• MySQL doesn't have computed fields like Access because it creates redundant data (a database no no).  Instead, you can make virtual tables with the VIEW command.
• You can actually reuse the DBQwikSite security script with a little modification.  I had made some separate php pages to generate points reports and had to secure those pages.  Very nice - can integrate one's own php pages with the DBQwikSite security.  I couldn't find any feature in DBQwiksite to import custom pages, so this was a great fix!
• I had to use the same MySQL database for both schools on my free website because I'm not allowed more than one MySQL database on my website.  So, the actual database connect would be for the settings on the actual website server, and the DBQwikSite security for users of the database site can be separate.  Also, the single MySQL database would have tables from both schools.
• I should make a master document which has the SQL code for create tables and the create views.  This would save me time to transfer the MySQL database from one machine to the next.  Also, I've been exporting the tables as CSV so that I could re-import the tables on another computer. Exporting CSV files is an easy way to backup a MySQL database (at least for my simple one)!

Now, I'm not big on aesthetics, it should just work.  I know some people don't like Wikispaces, say, because you can't make it look pretty.  I don't really care about that!  Eventually, the Web 2.0 technology will catch up and let you make it look pretty without much effort.  In the meantime, just live with it and try to put out good content.

I really like open source software.  I use LibreOffice, InkScape, VUE, Ubuntu Linux and now PHP and MySQL.  I know there is a dedicated community of volunteers who make this kind of stuff possible as well as some generous companies like Canonical who finance this stuff.  Now, Oracle owns MySQL and I know they're not doing this out of the goodness of their hearts - they can make a buck selling Enterprise versions of MySQL.  Open source volunteers are the antithesis of Apple and Steve Jobs.  I don't have Apple stuff because I don't like what they stand for.

So, in graduate school I spent a lot of time processing seismic data.  Now, I'm processing data again!