Skip to content

Finding "Next Monday" using MySQL Dates

Several people who read my article on Exploring Mysql CURDATE and NOW. The same but different posed questions regarding how to return a valid MySQL date equivalent to "Next Monday" given any particular day, as determined by MySQL's CURDATE(). This is a little bit tricky, but can be done entirely in MySQL syntax, making it usable with Calendar applications built on top of MySQL queries, without the need to use serverside date functions. This builds upon concepts discussed in my prior article, so if you have trouble understanding the implications of using CURDATE or DATE_ADD, you should probably take a minute and read that article. Continue reading "Finding "Next Monday" using MySQL Dates"

Centos Virtual LAMP server -- Part II

*Part 1 of this series is here*

Customizing your LAMP server


Unix people are probably familiar with the father of the DNS system -- the /etc/hosts file. The hosts file has a simple format:

ipaddress hostname

In the days prior to DNS, people would update a master hosts file and copy it around to all the servers in the enterprise. Surprisingly Windows versions also support this file, as a way of overriding DNS, so we can use this to our advantage, by adding an entry for our development server. In this example, I'm going to use dev.gizmola.com, which is not a real server.

One important reason to do this is that Apache and other web servers, use a feature of HTTP 1.1 that specifies a header field named "Host:". This mechanism facilitates the service of multiple domains from a single apache server, through the configuration of apache virtual host (or vhost) entires. The server uses the Host name in the HTTP header to determine how to route requests, so without host name resolution. you have to use non-standard ports and other mechanisms that are more trouble than they're worth. a

Continue reading "Centos Virtual LAMP server -- Part II"

Too much information about the MySQL TIMESTAMP

The MySQL timestamp is an oddity, being both a mySQL "Data Type" as well as a type of specialty column that provides a built in default. It doesn't help matters, that the timestamp was changed significantly around mysql version 4.1.

The Old TIMESTAMP


In older mysql versions, the TIMESTAMP was not in the same format as a DateTime column, and you could also set up truncation by defining the TIMESTAMP to have a fixed size. For example, you could define a TIMESTAMP column to be a TIMESTAMP(4) which would then only store the 4 digit Year portion of a DateTime value. I won't go into much detail on the pre version 4.1 TIMESTAMP, however, if you're stuck with an older version of MySQL I recommend you read the manual carefully before you attempt to use any of the information here. I'm going to concentrate on the current TIMESTAMP.

TIMESTAMP Properties


At its most fundamental, the TIMESTAMP is really nothing more than a Unix TimeStamp, which is to say, that internally it is stored as an integer value of seconds. Where a MySQL DATETIME column can be used to store any date and time from Jan 1, 1000 to 12/31/9999, the TIMESTAMP is limited in the same ways that the Unix timestamp is currently limited -- it can only store values from Jan 1, 1970 to Jan 9, 2038.

Those familiar with Unix design, will recognize the Jan 9, 2038 date as being the next big "Y2K" computing panic, and if you're young enough, you may realize a large payday in your future, selling remediation services to companies in roughly another 28 years. The folks at http://www.y2038.com/ are already estimating this to be as much as a 10 trillion dollar jackpot, although no doubt by that time most of the coding will be done by the Morlocks from their underground cave cities. Outsourcing of IT to Morlocks will be a major industry trend by the year 2020, mark my words.

Continue reading "Too much information about the MySQL TIMESTAMP"

Mysql Update: Null + 1 is Null!

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:




mysql> CREATE TABLE screenshots (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, votes INT);      
Query OK, 0 rows affected (0.09 sec)
 


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++).

  1. UPDATE screenshots SET votes = votes + 1 WHERE id =  


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! "

LAMP Tutorial Series originally published on PHPFreaks.com

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.


Defined tags for this entry: , , , , ,

Serendipity GeSHi Plugin update .05

Give this a day and it should be in Spartacus and the Sourceforge Additional Plugins cvs branch.

-.05 release
- Updated GeSHi to latest release (1.0.7.4)
- This release includes some fixes, and new language files for:
applescript, D, diff output, DIV game language, DOS batch language, eiffel, freebasic, gml, Delphi Inno script, Matlab M language files, MySQL specific SQL, Objective CAML, Ruby, Scheme, SDLBasic, and VHDL: Very high speed integrated circuit HDL
Defined tags for this entry: , , , , ,