Miroslav Genev's .BLOG

Pivot tables in MySQL

2010-07-07T04:09:00.006+03:00

In this article:

Introduction
The problem (example)
Simple solution
Automating the process
Generating the SUM() columns
Using Prepared statements (MySQL)
Using an external language (PHP)

Introduction

MySQL and pivot? Try and google it. Or you already did?

I really never had the need for pivoting data, until yesterday. Then I started searching, but I didn't find a solution to enclose into a predefined function which may be reused.

I'm not very good at explaining (if you bothered to read other my posts you must have already noticed that), so maybe it'll be better if I start with a simple example of what we'll work with and what's the result we need. Take a look at the following table definition:

CREATE TABLE `Contracts` (
    ...
    `Agent` VARCHAR(64),
    `Country` VARCHAR(3)
);

Amongh the other data, each row holds the name of the agent responsible for the contract, as well the country in which is the contract partner. For our example we'll need only those two columns. So, the data we're interested in looks something like that:

`Agent`	`Country`	Other columns...
'Peter'	'UK'	...
'Hans'	'UK'	...
'Ivan'	'GER'	...
'Ivan'	'GER'	...
(...)	(...)	(...)

OK, this is far from what was the data I was working with, I admit it. Also I fully realize that nobody ever, ever will have to work with data so simplified, but it's perfect to illustrate the idea for now.

So, what we need is a table with agents as columns, countries as rows, and number of contracts (agent <-> country) into cells, something like this:

`Country`	`Peter`	`Hans`	`Ivan`	Other agents...
'UK'	1	1	0	...
'GER'	0	0	2	...
(...)	(...)	(...)	(...)	(...)

The next chapter introduces a simple solution for getting results in that form.

Simple solution

The following is a representation of the solution I found in various variants on the net. First, we need a list of all countries:

SELECT DISTINCT `Country` FROM `Contracts`;

Second, we need to construct a query which selects rows from `Contracts` and groups them by agent:

SELECT `Agent` FROM `Contracts` GROUP BY `Agent`;

Last we have to add the columns, each of them representing a country and displaying the sum of contracts with the agent in row. That would be done using IF() and SUM():

,SUM( IF( `Country`='UK', 1, 0 ) ) as `UK`
,SUM( IF( `Country`='GER', 1, 0 ) ) as `GER`

Then the whole select will look like this:

SELECT
  `Agent`
  ,SUM( IF( `Country`='UK', 1, 0 ) ) as `UK`
  ,SUM( IF( `Country`='GER', 1, 0 ) ) as `GER`
FROM `Contracts`
GROUP BY `Agent`;

How does that SUM() and IF() combination work? For each row (each agent), it counts the number of rows in `Contracts` with that agent, and for each row that have `Country`='UK' (when we're talking about the `UK` column) 1 is added to the cell's total.

Basically, this is the whole idea: Get the columns' names, add a SUM() column for each distinct name, and include those SUM()s into a select that groups rows by another (not calculated) column. One problem that arises at once - you can't reuse this select if you'd expect adding or removing countries in future.

Automating the process

So, what can be done to not have to write a select anytime the pivot table's needed? So far the only way I could think of was using two subsequent queries, the first one needed to determine the columns, and the second to get the pivot result. The second query would be generated automatically (with the first query's result included). This might be done entirely from the MySQL engine, or by using an external language.

Generating the SUM() columns

One way to somewhat ease the process (and a step ahead into automating the pivot query) would be to make MySQL to generate the SUM() columns:

SELECT CONCAT(',SUM( IF( `Country`=\'',`Country`,'\', 1, 0 ) ) AS `',`Country`,'`')
AS `Generated_Column`
FROM `Contracts`
GROUP BY `Country`;

This produces the following output:

`Generated_Column`
,SUM( IF( `Country`='GER', 1, 0 ) ) as `GER`
,SUM( IF( `Country`='UK', 1, 0 ) ) as `GER`

Once stored, this select statement can be used everytime when the pivot select is being written. However, later we'll need the result stored into a variable which is impossible unless the result is a single row. The GROUP_CONCAT() aggregate function is what we need:

SELECT GROUP_CONCAT(DISTINCT `Country` SEPARATOR ', ')
INTO @a FROM `Contracts`;

This will result in 'UK, GER', stored into @a.

Now we have to add the rest of the SQL text around `Country`, but here's a problem: in our SUM() definition `Country` is used twice. If we add `Country` second time in GROUP_CONCAT, we cannot use DISTINCT more than once (produces SQL error), but if we don't, we'll get concatenation for each row. Instead we can use a second variable to store `Country`'s value and then plate the variable where we need that value the second time:

SELECT GROUP_CONCAT(DISTINCT @b=`Country`, ' ', @b SEPARATOR ', ')
INTO @a FROM `Contracts`;

Now @a = 'UK UK, GER GER'. Let's add the rest of the text:

SELECT GROUP_CONCAT(
    DISTINCT 'SUM( IF( `Country`=\'', @b = `Country`,
    '\', 1, 0 ) ) AS `', @b, '`' SEPARATOR ', ')
INTO @a from `Contracts`;

The result is SUM( IF( `Country`='0', 1, 0 ) ) AS `GER`, SUM( IF( `Country`='1', 1, 0 ) ) AS `GER`.

Using Prepared statements (MySQL)

This is my solution:

SELECT GROUP_CONCAT(DISTINCT 'SUM( IF( `Country`=\'', @b = `Country`,'\', 1, 0 ) ) AS `', @b, '`' SEPARATOR ', ')
INTO @a from `Contracts`;
SET @a = CONCAT('SELECT `Agent`, ', @a, 'FROM `Contracts` GROUP BY `Agent`');
PREPARE s FROM @a;
EXECUTE s;
DEALLOCATE PREPARE s;

Following is a step-by-step explanation.

The only way to evaluate SQL statements from strings I know of is by using prepared statements. This is the simplified synthax:

PREPARE s FROM 'SELECT * FROM `Contracts`';
EXECUTE s;
DEALLOCATE PREPARE s;

The main purpose of prepared statements isn't exactly the ability to evaluate dynamically created SQL statements and that's the reason I'm using this feature in it's most plain variant.

Now that @a holds the SUM columns definitions (see "Generating the SUM() columns"") we can build the pivot SELECT:

SET @a = CONCAT('SELECT `Agent`, ', @a, 'FROM `Contracts` GROUP BY `Agent`');

And the rest is easy:

PREPARE s FROM @a;
EXECUTE s;
DEALLOCATE PREPARE s;

Using an external language (PHP)

Here's the solution:

$link = mysql_connect('SQL host', 'username', 'password');
$query = 'SELECT DISTINCT `Country` FROM `Contracts`;';
$result = mysql_query($query, $link);
$countries = '';
while ($row = mysql_result($result))
  $countries .= ",SUM( IF( `Country`='{$row[0]}', 1, 0 ) ) as `{$row[0]}` ";
mysql_free_result($result);
$query = 'SELECT `Agent`, ' . $countries . 'FROM `Contracts` GROUP BY `Agent`';
$result = mysql_query($query, $link);

Read below for a step-by-step explanation.

This method involves two requests to the MySQL server - one to get the country names, and the second to query the pivot table. Although any language can be used this example implements PHP.

First of all establish a MySQL connection:

$link = mysql_connect('SQL host', 'username', 'password');

Then get the countries:

$query = 'SELECT DISTINCT `Country` FROM `Contracts`;';
$result = mysql_query($query, $link);
$countries = '';
while ($row = mysql_result($result))
  $countries .= ",SUM( IF( `Country`='{$row[0]}', 1, 0 ) ) as `{$row[0]}` ";
mysql_free_result($result);

Instead of getting rows containing the plain country names we may take advantage of the previously reviewed methods to generate the columns definitions (see "Generating the SUM() columns"):

$query = "SELECT CONCAT(',SUM( IF( `Country`=\\'',`Country`,'\\', 1, 0 ) ) AS `',`Country`,'`') AS `Generated_Column` FROM `Contracts` GROUP BY `Country`;";
$result = mysql_query($query, $link);
$countries = '';
while ($row = mysql_result($result)) {
  $countries .= $countries;
}

...or even:

$query = "SELECT GROUP_CONCAT(DISTINCT 'SUM( IF( `Country`=\'', @b = `Country`,'\', 1, 0 ) ) AS `', @b, '`' SEPARATOR ', ') INTO @a from `Contracts`;";
$result = mysql_query($query, $link);
$row = mysql_result($result);
$countries = $row[0];

Now we need to build and send the main query, using the already prepared columns for countries:

$query = 'SELECT `Agent`, ' . $countries . 'FROM `Contracts` GROUP BY `Agent`';
$result = mysql_query($query, $link);

And we can display, save into file or do whatever we want with the result.

Do you have another solutions or questions? Please comment.

JavaZone trailer: JAVA 4-ever

2010-07-06T14:14:00.012+03:00

Българска версия на този пост

Hilarous video.

Config.ini vs. config.php - Combining both

2010-07-04T22:39:00.006+03:00

Прочети тази статия на български

Not long ago I wrote an article about using INI files as settings containers for PHP scripts (you can read it here - Config.ini vs. config.php).

I wrote a small PHP module which takes an INI file and produces a PHP file, containing all the INI parameters and values as constants definitions.

The module is really easy to set up and work with so if you need a quick way to add settings to your script without having to search PHP files for lines such as $database = 'not_anymore'; or define('DEFAULT_DIRECTORY', './res'); everytime you need to check or to change options, you can use my (proudly presented) PHP-INI configuration parser (its name happened to be a little longer than the PHP code it consists of). You can download it as a RAR archive from this link: Config 0.1beta.

As you can see, it's only version 0.1 and still beta (which will stay like this until at least one or two readers actually stop by this blog, happen to read this article, and on top of that decide to download and try my piece of code. And to post a comment with their opinion, maybe? Pretty please?), but I will see to further improvements, adding new features and so on. When and if I have the time for it.

Exceptions in PHP

2010-06-30T21:45:00.020+03:00

Прочети тази статия на български

In this article:

Exceptions
The Exception object
Exception subclasses
Why won't this catch the exception?

Exceptions

Exceptions and try-catch blocks are introduced in PHP version 5.

Here's an example for the syntax of try-catch:

try {
    ...
} catch (Exception $e) {
    ...
}

First, statements inside the try block are executed. If an exception is thrown PHP ignores next statements until a proper catch block is found, in which case the catch block is executed.

An interesting thing about ignoring next statements (until the next catch block) is that even statements after the try block will be ignored since catch has not been found (and executed) yet.

If there's no matching catch block until the end of the script a Fatal error occurs.

If there's no exception thrown, the whole try block is executed, and the catch one is ignored.

Consider the following example:

function display_text($text) {
    if (!$text)
        throw new Exception('Nothing to display');
    else
        echo $text;
}

try {
    display_text('First line.');
    display_text(); // An exception is thrown, so the block execution breaks here
    display_text('This will be ignored.');
}
// Any statements here would be ignored too - the exception must be caught first
catch (Exception $e) {
    echo 'Exception: ' . $e->getMessage() . "\n";
}
// script continues...

PHP allows catching exceptions from nested levels - in previous example the function we call throws the exception, but instead it could call another function (which calls another function, and so on) which throws the exception. We'll be able to catch that exception too.

The Exception object

As seen from the afore example, exceptions are thrown by creating new object and assigning a message to it (throw new Exception()). Later in the script we're able to catch it with catch (Exception $obj_name). Inside the catch block we may use the object's methods to get information about the exception.

Here's a short reference to (most of) the Exception class methods (check the PHP manual on the Exception class for more information):

Exception::__construct ( [ string $message = '' [, int $code = 0 [, Exception $previous = null ] ] ] )

Constructs the exception object (i.e. throw new Exception(...)). $message is the error (exception) message, $code is an optional error parameter, by which an error number might get defined. $previous can be used to link the exception to a previous one (for example, you may have a case when catching one exception results in more exceptions - link to previous exceptions can be linked so at the end all messages could be proceeded).

string    Exception::getMessage()
int       Exception::getCode()
Exception Exception::getPrevious()

Returns the mentioned before message, code, and link to previous exception.

string    Exception::getFile()

Returns the filename of the PHP where the exception was thrown.

int       Exception::getLine()

Returns the line number in the PHP file where the exception occured.

array     Exception::getTrace()
string    Exception::getTraceAsString()

These two methods will return more detailed information such as function name where the exception occured, arguments the function has been called with (filename and line number as provided as well). The difference is that while getTrace() does provide the information as an associated array (['Line']=>23, ['Function']=>'test()' etc.), getTraceAsString() formats that information in one string.

Short example:

...
catch (Exception $e) {
    echo 'Error: '.$e->getMessage().' in "'.$e->getFile().'" at line '.$e->getLine();
}

Exception subclasses

class MyException extends Exception {}
class MyOtherException extends Exception {}

function test($var) {
    if ($var > 0) throw new MyException('Positive number');
    if ($var < 0) throw new MyOtherException('Negative number');
    return 0;
}

try { echo test(1); }
catch (MyException $e) { echo 'The number was greater than zero.'; }
catch (MyOtherException $e) { echo 'The number was below zero.'; }

Subclasses of Exception can be defined (in fact, many predefined exception classes exist in PHP), then you may catch different descendants with different catch blocks. Since MyException and MyOtherException are child classes of Exception, instead of two catch(My... $e) we could use one catch(Exception $e) statement and still the exception will be properly caught. One method of dealing with different exceptions would be using many subclasses, and other method could make use of the $code parameter to define the type of error.

Why won't this catch the exception?

function read_first_line($filename) {
    try {
        $a = file($filename);
    } catch(Exception $e) {
        return false;
    }
    return $a[0];
}

echo read_first_line('myfile.txt');

This won't work. Still your page will yell something like 'Error: could not open stream...' in case of error instead of 'false'. This is because old PHP functions still don't take advantage of the Exceptions mechanism. The "old" error handling method is used instead but I'll write about that in another article.

IT Helpdesk (Video)

2010-06-24T14:19:00.009+03:00

Българска версия на този пост

Do you know of more such videos? Comment and share!

Counting the number of weeks in given period (MySQL)

2010-06-24T01:21:00.012+03:00

Прочети статията на български

Few days ago a friend of mine told me that he need to count number of weeks in given period. Simple division by 7 of the number of days wasn't what he needed - the weeks had to be counted as number of Monday-Sunday fragments (in addition to any incomplete weeks at the start or the end of the period). I wrote a simple stored function to do the task:

`week_count`
  (
    StartDate DATETIME,
    EndDate DATETIME,
    CountIncompleteWeeks
  )
  RETURNS INT

StartDate and EndDate define the period to count weeks from. CountIncompleteWeeks, determines if incomplete weeks have to be counted (CountIncompleteWeeks=1) or discarded (CountIncompleteWeeks=0).

The function returns number of weeks (minimum of 1) or -1 in case of error (if StartDate or EndDate aren't of proper DATETIME format, or if StartDate is bigger than EndDate).

NOTE: Both dates count towards the number of days (so if both date values are equal, that means an interval of 1 day, resp. the result will be 1 week).

CREATE FUNCTION `week_count`
   (
      StartDate DATETIME,
      EndDate DATETIME,
      CountIncompleteWeeks INT
   )
   RETURNS INT
   DETERMINISTIC
   BEGIN
      DECLARE Days, Weeks, WDay INT;
      SET Weeks = 0;
      /* Get the number of days in the given period */
      SET Days = IFNULL(DATEDIFF(EndDate, StartDate) + 1, -1);
      IF Days < 1 THEN RETURN -1; END IF;

      /* If the start date isn't Monday, subtract this incomplete week from the
         total days number (and count as 1 week if PlusIncomplete > 0) */
      SET WDay = IF(DAYOFWEEK(StartDate) = 1, 7, DAYOFWEEK(StartDate) - 1);
      IF WDay > 1 THEN
         IF CountIncompleteWeeks > 0 THEN SET Weeks = 1; END IF;
         SET Days = Days - (8 - WDay);
         -- If after subtracting no days have left, return 1.
         IF Days < 1 THEN RETURN Weeks; END IF;
      END IF;

      /* If the end date isn't Sunday, subtract this incomplete week from the
         total days number (and count as 1 week if PlusIncomplete > 0) */
      SET WDay = if(DAYOFWEEK(EndDate) = 1, 7, DAYOFWEEK(EndDate) - 1);
      IF WDay < 7
      THEN
         IF CountIncompleteWeeks > 0 THEN SET Weeks = Weeks + 1;
         SET Days = Days - WDay;

         /* If after subtracting there are no more days, return the number of weeks
            counted so far */
         IF Days < 1 THEN RETURN Weeks; END IF;
      END IF;

      /* Divide the remaining days to weeks, add any incomplete one,
         then return the result. */
      RETURN Weeks + (Days / 7);
   END;

I'd welcome any comments (if someone ever reads this post, of course) about this function. Please feel free to criticize or to propose better variants.

Config.ini vs. config.php

2010-06-23T15:34:00.017+03:00

Прочети статията на български

Recently, I was thinking - what is the best practice to load previously stored settings to your PHP script? One way is to have a PHP file containing define() statements. Then you need to include() the file in your main script and you have access to the options through constants.

Another way is to keep an INI file, and load it by using parse_ini_file() or parse_ini_string() functions. Of course, there are many other possible approaches, but for the most cases using the INI-file or constants methods are the most appropriate ones. So, in short: which is better - the INI file or the constants?

Benefits and drawbacks

One thing to consider is if it's about a script that would be used and maintained not only by its creators - a module or even a whole application. It's way more convenient for a user to modify settings in an INI file rather than a PHP one (strictly speaking, having to edit a PHP file is having to modify the source code, which isn't the best way to propose an "interface" to application settings).

Also, using INI files grants you the ability to divide your settings into sections (although this benefit is doubtful, when you can easily just add "SECTIONNAME_" to the identifiers of your constants, and if you really need a tree-style of variables you should consider using XML instead).

On the other hand, parsing INI files using core PHP functions isn't very useful: first of all, you end up with an array of strings (no int or float, not to mention another data types). And, which is of bigger significance, you just can't have any characters you wish in your values, regardless of using INI_SCANNER_NORMAL or INI_SCANNER_RAW in the parse_ini_file/string function. (This particular drawback could be avoided by supplying custom functions to parse INI data.)

And the last, but maybe the most important thing, is the speed. When speaking about a site with heavy traffic, thus frequently loading the settings into the script, the speed is of great importance. I have made the following test:
I generated 100 .ini files containing 100 key-value pairs each. I also generated 100 .php files with 100 constant definitions each. Then I executed 10 times in a row a script that loads all the files and measures the time needed to load them (of course, separate measurement for loading the .ini-s and for the constants). Here are the results on my machine (CPU: Sempron 3100+ @1.8 GHz, RAM: 768 MB DDR400; WAMP):

Seconds to load 10000 values:
Script exec.  INI        Constants
------------  ---------  ---------
1-st time     0.0709*    0.0107*
2-nd          0.0181     0.0031
3-rd          0.0183     0.0023
4-th          0.0183     0.0029
5-th          0.0187     0.0029
6-th          0.0179     0.0021
7-th          0.0182     0.0027
8-th          0.0187     0.0030
9-th          0.0184     0.0022
10-th         0.0188     0.0035

* It's funny to observe how caching of files affects performance

The conclusioin? Using include() to your script and loading configuration values as constants is MUCH faster than loading them from INI file.

A short resume

Editing configuration stored in INI format is more convenient than editing PHP file.
Defining constants by including PHP file into the main script is more safer and flexible in comparison to the INI-load method (unless using custom INI parsing functions).
Defining constants is several times faster than loading INI values.