Improving Pagination

Recently I discovered a method to improve the performance of a pagination in MySQL. Generally takes two queries to the database, one for bring the data and another to find out how much data is the total. What is new, though not alter the amount of queries, is that we can make the MySQL server has to process only once.

How does this work?
In the first request, in addition to requesting the data, we say to the server to calculate the total number of rows of the query, then we do a query to get this information.

mysql> SELECT * FROM t SQL_CALC_FOUND_ROWS
-> WHERE id> 100 LIMIT 10;
mysql> SELECT FOUND_ROWS ();


This is especially useful if you use an ORDER BY clause, or a complex consultation with subqueries, multiple JOIN or many calculations.

Check MySQL Manual

Autoloading classes

Since PHP 5, it is not necessary to load all classes at the beginning of each script, but if there is a function __autoload is called when they want to use a class that does not exist. This function takes the name of the class you want and it must bear responsibility for making that class exist, in order to avoid the throwing an error because it does not exist.

This is a good step forward for PHP 5, which since version 5.1.2 also gives us the possibility to register multiple functions to do this, rather than simply just one as was previously, through spl_autoload_register.

Switch with expressions

The switch usually are very useful, but also very limited. Looking the way, we can extend its use to evaluate expressions, and call functions. What we have to do is put expressions in the case to be an argument to compare.

For example, we can do the following

switch (true) {
    case ($variable >= 0 && $variable < 3):
        echo '$variable está entre 0 y 3';
        break;

    case ($variable >= 3 && $variable < 7):
        echo '$variable está entre 3 y 7';
        break;

    case ($variable >= 7):
        echo '$variable es mayor a 7';
        break;
}

Thus, instead of using three chained IFs, we can resort to this option, so that our code is more readable.

Single and double quoted

For most people, the single and doubles quotes in PHP are equal. But fundamental differences exists. Using double quotes there are more special characters: \n \r \t \\ \$ \" \[0-7] (1,3) \x [0-9A-Fa-f] (1,2) while with single quotes we only have \\ \'.
Knowing this already, we can assume that the single quotes are faster. On the other hand, we must add that within the double quotes variables may be used without concatenate, but writing it directly on the string contents.
More information can be found in the PHP manual, in String section.

Finally, let's compare the times of each, in various circumstances.
View example

Handling dates

It is normal to have to change the format of a date to suit one type of database, or to be presented to a user. Usually, strtotime is a good tool to do so because it supports the traditional format of MySQL and many others, but the problem is usually when we get a date format dd/ mm/yy, because by default this feature takes American format mm/dd/yy and can be confused with the 01/07 January 7, instead of July 1 that we expected. The solution offered by PHP is the function strptime, which allows us to indicate the format of the date. However, this feature is quite new (since PHP 5.1.0) and is not implemented on Windows, which is quite limited. Therefore, we can create a small function, for this purpose.

function strtotime2($date)
{
if (preg_match('#^((0?[1-9])|([1-2]?[0-9])|(3[0-1]))/(0?[1-9]|(1[0-2]))/((19|20)?([0-9]{2}))$#', $date))
{
list($day, $month, $year) = explode('/', $date);
return mktime(0, 0, 0, $month, $day, $year);
} else
{
return strtotime($date);
}
}

Flexible parameters, hard returns

When programming a function, or a method of a class, always is the best thing to be able to receive any type of parameter. Analyze if is more comfortable to mix two parameters in one, using an Array, or if a data can be of several types. All these things it is necessary to have them in account so that soon it is less difficult to work with which already it is done. Another thing that we must try is always to return the same data type, even if it were not possible to be made what it was expected, then if an Array is due to return, but was an error we can return an empty Array instead of false or null. A simple example of this



function select($nombre, $valores = array(), $seleccionado = '', $atributos = array())
{
if (!is_array($valores) || count($valores) == 0)
{
return '';
}

if (is_string($nombre))
{
if (is_array($atributos))
{
$atributos['name'] = $nombre;
} else
{
$atributos .= ' name="' . htmlentities($nombre) . '"';
}
}
$out = '<select';
if (is_array($atributos))
{
foreach ($atributos as $atributo_nombre => $atributo_valor)
{
$out .= ' ' . $atributo_nombre . '="' . htmlentities($atributo_valor) . '"';
}
} else
{
$out .= ' ' . $atributos;
}
$out .= '>';
foreach ((array)$valores as $valor_clave => $valor_etiqueta)
{
$out .= '<option value="' . htmlentities($valor_clave) . '"' . ($valor_clave == $seleccionado || (is_array($seleccionado) && in_array($valor_clave,$seleccionado)) ? 'selected="selected"' : '') . '>' . htmlentities($valor_etiqueta) . '</option>';
}
return $out . '</select>';
}

echo select('select',array('a' => 'A', 'b' => 'B','c' => 'C','d' => 'D'),array('a'),array('id' => 'select', 'multiple' => 'multiple'));
echo PHP_EOL;
echo select('select',array('a' => 'A', 'b' => 'B','c' => 'C','d' => 'D'),'a','id="select"');
echo PHP_EOL;
echo select('select');