Jelly and Custard

SQL Join syntax: Theta vs ANSI

At the heart of every dynamic website is some form of data source, whether it's a simple flat text file, XML schema, or full blown database. In this article, I'll be addressing database table joins with SQL (specifically MySQL).

4 comments August 10th, 2006

ARCHIVE-ing Data in MySQL

Over time, there comes a point where you have to make certain choices as a DBA in order to maintain the integrity and efficiency of your database system. In a fair amount of systems, there is information that needs to be archived away, possibly compressed, and then used to do a few lookups on when needed.

In the past, you could compress MyISAM tables, however, this required you to take your MySQL server offline to perform the compression. However, starting with MySQL 4.1.3, there is the new ARCHIVE engine. You can find out if your MySQL distribution support the ARCHIVE format by running the following query:

Add comment February 16th, 2006

Dates & MySQL

I recently heard about someone who used to store dates in their DB, retrieve them, and then use mktime(), date() etc to then use that date in a more readable format! So here's a quick look into some of the ways to retrieve dates from MySQL and format them.

Timestamps are your Friend

The UNIX timestamp is a count of the number of seconds since 1st January 1970. The date() function in PHP can format a UNIX timestamp into a large number of user-defineable formats:

Continue Reading 3 comments February 13th, 2006

MySQL Events

A new feature of MySQL 5.1 is Events. Similar to a crontab or the 'Task Schedule', it allows you to execute an SQL statement at certain time intervals. The syntax is exceptionally easy to use:

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

The first thought might be why you would want to run such a query from within MySQL. Running queries at regular intervals is sometimes necessary in large projects, e.g. for general cleanup etc. You can do this from within your chosen scripting language (PHP, Ruby etc), however, this creates another layer of dependancy. If your PHP script can't connect to the MySQL server, then it won't run - storing the event within MySQL overcomes this dependancy.

Continue Reading Add comment January 27th, 2006

Getting totals in MySQL

At work recently, I had to grab the sum of totals from multiple rows meeting certain conditions in a mysql table. Rather than get each total and add them in PHP in a loop, I decided to get MySQL to do all the legwork for me:

SELECT sum(amount)
FROM table
WHERE id = '$id'
AND condition = '1'
GROUP BY id;

That should return you a summation of the amount columns for all rows matched in the the WHERE statemtents.

1 comment December 21st, 2005

More on submitting forms

At the heart of every dynamic website is a way for the user to interact with the data it provides, and one very common way of interacting with with such data is by the use of HTML Forms. Generally (and hopefully obviously), a form is "submitted" when the user activates an HTML input element with the type attribute set to submit. However, most user agents will also submit a form when you give focus to an input element with attribute set to text and hit return. With PHP, the most common way to test if a form has been submitted is to simply test if the $_POST/$_GET array is empty or not, however when using the aforementioned multiple submit button techniques, a problem arises:

• sensible user agents (Gecko based browser, for example) will simply act as though the first submit button had been pressed, and proceed normally.
• MSIE (Internet Exploder?) will submit the form without passing any submit elements in the resulting array, which can cause all kinds of problems if you want to test their validity

The Solution? It is perfectly valid to have multiple input elements with the same name attribute, so simply place a hidden field with the same name/value as your preferred default action. For example:

1 comment November 28th, 2005

Retrieve valid entries for a MySQL ENUM/SET column

Here's a handy little PHP function I threw together to retrieve valid entries for a MySQL ENUM/SET column, handy for generating dynamic radio group/checkbox/multiple select HTML elements:

4 comments November 23rd, 2005

Count occurence of character (i.e. line breaks) in a MySQL field

Unfortunately, there is no inbuilt MySQL function to count the occurance of a character in string. By counting the number of characters in the original string, then temporarily 'deleting' the character you want to count and counting the string again, subtracting the first count from the second, you get the number of occurances:

SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurances`
--> 3

Obviously, in this example 'b' is the string you want to count the number of occurances of.

You can use the same principle to count the number of lines in a field, by adding one to the difference:

1 comment November 8th, 2005