GizmoLA.com

You can't add to Null

Here's something about mysql create table definitions that can easily catch you if you aren't careful. Consider this table definition:





What the user wanted was a simple table to keep track of user submitted screen shots. Of course the real table had a submission date, and name column but I've omitted those in order to focus on what can happen when you allow a numeric column to have NULL values.

In the application in question, when a user votes for the screen shot they like, the system should "count" the vote, by increasing the values in the "votes" column by one. Initially the developer working on this application was trying to read the value from the database, and in a PHP web script, they would increment this value and take the result and set "votes" to be equal to it in an UPDATE statement. I explained that this could cause lost votes, because if two or more users were voting at nearly the same time, each would overwrite the value of the "vote" column. In fact there are scenarios far worse than that --- a user with a cached page could vote and set the vote count back days or weeks. I didn't bother to mention the possibility that someone might recognize what was going on in the web form, and start tampering with it, since it was plainly evident that the form was passing the current number of votes.

One of the many benefits of using a relational database is built in concurrency. In an UPDATE statement, you can add to the value of the column without having to know what its original value is, just as computer languages allow assignment to a variable that references the variable's current value (ie. $a = $a + 1, $a++).



All that's needed is to have the serverside language provide a value for a particular "id" and the votes will be tallied and updated correctly. Even more importantly, mysql will serialize the updates, insuring that no votes are lost.

However, given the original Mysql CREATE TABLE statement , what will happen if our code embeds the UPDATE statement provided?

Continue reading "Mysql Update: Null + 1 is Null! " »

A few years ago I published a 3 part LAMP tutorial series entitled LAMP, MySQL/PHP Database Driven Websites on the well known php community website PHPFreaks.com. This series dealt with a slew of practical issues including how a LAMP server works, relational database design using MySQL, many to many tables, SQL inner and outer joins, practical PHP debugging, php documentation tools, basic PHP classes, css, interactive javascript & DHTML with a chooser widget, php HEREDOC and php basics like how to process forms and utilize GET and POST methods.

The series was fairly successful, (a 4.5 of 5 after hundreds of ratings), many pages of comments and questions, and page views to the 100k's+ although PHPFreaks auditing system was turned off at some point and stopped recording views.

Unfortunately, some years ago PHPFreaks.com suffered some fairly catastrophic issues with its publishing system. There were also some bugs, and the site was exploited with some XSS, and the admins simply decommissioned the majority of the site. My series was part of what disappeared. At that point, a couple of college Computer Science courses on web development had taken the series and integrated it into their curriculum, and the professor of one of these courses had converted it into a Word document, which I was able to download and convert to pdf.

I plan to write a compatible publishing addon for gizmola.com so that I can take the original markup and republish it here, but in the meantime, here is the series in pdf format. The conversion utility they used stripped out the original markup, and page breaks are gone, but the text, source code, and illustrations are all still there.

I also offer all the source code for parts 2 & 3 of the series. I'm not sure what happened to the source for part 1, however, it is all included inline in the tutorial. The LAMP, MySQL/PHP Database Driven Websites series is now available in pdf format. Click here.

The Dark Ages of Camelot (DAoC) is one of a number of Massive Multiplayer Online Role playing games (MMORPG) available for people who like those types of things. Mythic Entertainment, the company behind DAoC decided to offer an xml feed with various statistics about the game including the number of active players. From April 2002 to July 2005 I used a cron job to query the xml feed once every 5 minutes, parse the results and store the statistics in a database table.

I made a graph of these statistics available on a fan site I operated. The graph used the open source PHPlot graphing class in order to draw a line graph charting the number of players playing the game by hour for a 24 hour period. Visitors to the site would see the line graph shown here, plotting the number of players per hour against a second line showing the day's average. Like many PHP graphing libraries, PHPlot assumes that you have the GD library installed and available to PHP on order for the graphs to work.

This was one of my early experiments with PHP, and illustrated its power and flexibility. Now that the site where the graph appeared is no longer online, I figured I would provide the code, and a small subset of the database, for people interested in how to program with PHPlot. At the time I created this code, PHPlot didn't have much documentation and required some trial and error experimentation. Of course, since then (several years now) PHPlot has been improved, and should definately be considered if you want graphing in your application. Because I don't want to spend time debugging old obsolete code, I've provided the library I used in the download along with the graph script. It would probably work with the latest release, but I don't have time to test it. You can download my example database, version of PHPlot and script here. The database contains one months data, so you can play with the script and explore the class.

Read on for some brief instructions and an explanation of the code.

Continue reading "PHPlot, MySQL and the Dark Ages of Camelot" »

Sometimes I see people attempting to use VARCHARS or CHARS to store dates in their MySQL database application. This is really fighting against MySQL, which has a variety of interchangeable date types. Internally MySQL is storing dates as numbers, which allows it to do all sorts of nice arithmetic and comparisons with very little effort on your part. For example, when you use a mysql DATE column to store a date, you don't have to worry about sortation, or comparing that date to another DATE, because MySQL already understands how to do those things internally. A lot of people also don't realize that they can output a DATE column in just about any way they choose using the DATE_FORMAT function. This causes people to shy away from using DATE, DATETIME, TIME, or TIMESTAMP columns, when they really should.

Continue reading "Exploring Mysql CURDATE and NOW. The same but different." »

It's been a while since I updated. I have plans to move the site to a new server I bought and have been configuring. Part of the process involved getting my new ISP in place (thankfully, and after numerous false starts) I now have DSL again, courtesy of Megapath Networks. The new server is setup with RedHat 7, and is hosting several small gaming sites: Cali Tribes Players and LaLa-Lan. Eventually I plan to move Gizmola.com onto it. So obviously, I've been working on a few side ventures.

I'm also playing a lot of Ice Hockey at Culver city and Burbank, and learning PHP and MySQL in the process. I think I can now be considered officially nocturnal, as I find myself up most nights. Lately I've been playing a fair amount of Tribes2, and my Tribes2 team has climbed into the top 10 of the teamwarfare ladder. This is somewhat contributory to the nocturnal lifestyle, although I think the primary reason is that I'm just basically a night person, and without the constraints of a regular job to attend, my free running cycle allows for me to sleep when I'm tired. As I write this, it's 5:40am and I'm still awake.