Paul M. Jones

Solar is a simple object library and application repository for PHP 5. You can view the rather extensive change log here.

This release is a major break to backwards compatibility, mostly because we now use PDO for the SQL database API abstraction (as opposed to the homegrown solution I was using in preparation for PDO). The distribution includes a number of migration documents to help users of prior versions move to the new system.

I want to specifically mention one aspect of the Solar SQL system, the Solar_Sql_Select object. This is probably not new in the PHP world, but it's the first time I've done anything like this, so I want to talk about it.

One of the problems with database portability is that you can't depend on a LIMIT to work the same way across database backends; indeed, it may not even exist as such. Different DB abstraction packages use different methods to support LIMIT emulation, either by rewriting the SELECT statement, or by supporting only the portions of a LIMIT clause available to the particular backend.

What Solar_Sql_Select does is let you programmatically build a SELECT statement, and it keeps the clause portions separated internally. It then combines those portions in a manner specific to the database backend driver, putting the appropriate LIMIT clauses in the right place. Here's a quick example; let's build this SELECT statement.

SELECT id, date, type, name
FROM example
WHERE
    date >= '2005-01-01' AND
    date <= '2005-01-31' AND
    type IN('a','b','c')
ORDER BY id
LIMIT 10,50

(The LIMIT in this case is to grab 10 rows starting at row 50.)

The equivalent Solar_Sql_Select code is:

<?php
$select = Solar::object("Solar_Sql_Select");

// the basic columns
$table = 'example';
$cols = array('id', 'date', 'type', 'name');

$select->from($table, $cols);

// WHERE clauses for the date
$select->where('date >= ?', '2005-01-01');

$select->where('date <= ?', '2005-01-31');

// WHERE clause for the type
$types = array('a', 'b', 'c');

$select->where('type IN(?)', $types);

// ORDER and LIMIT
$select->order('id');
$select->limit(10, 50);


$statement = $select->fetch('statement');
?>

Some notes:

• You can use $select->fetch() to retrieve the 'statement' as built, or actual results using 'all', 'col', 'row', 'PDOStatement', etc.
• In this example, quoting happens on-the-fly, and quoting an array returns a comma-separated string of the individually-quoted array values. However, you can also use named placeholders (:start_date, :end_date, :type_list) and then use $select->bind() to bind data to those placeholders all at once. All hail the glory of PDO. :-)

In the above Solar-based PHP code, when MySQL is the driver, the $statement contents will look something like the initial example. However, when using the Microsoft SQL driver (which does not support LIMIT, only TOP), the resulting SELECT looks something like this:

SELECT *
FROM (
    SELECT TOP 10 *
    FROM (
        SELECT TOP 60 id, date, type, name
        FROM example
        WHERE
            date >= '2005-01-01' AND
            date <= '2005-01-31' AND
            type IN('a','b','c')
        ) AS solar_limit_rev
        ORDER BY id DESC
    ) AS solar_limit
ORDER BY id ASC

Which looks like a mess, but these guys seem to think it works. The point is that by keeping the SELECT clauses separate until you build the statement, you can manipulate the individual pieces with great precision for better portability.

Another thing about Solar_Sql_Select is that paging is built in. If you wanted to grab page 5, where pages are 10 rows each, you can do this:

<?php
// instead of $select->limit(10,50) ...
$select->paging(10); // 10 rows per page

$select->limitPage(5); // limit to page 5
?>

Also, row-and-page counting is built in.

<?php

// instead of $select->fetch() ...
$total = $select->countPages();

/*
$total = array(
    'count' => number_of_rows,
    'pages' => number_of_pages
);
*/
?>

You can see more extensive Solar_Sql_Select docs here (although they are not "real" docs, just migration examples).