SQL Basics in Solar

The Solar_Sql package contains classes for connecting to and querying SQL databases. It uses PDO-based adapter classes so that you can interact with MySQL, PostgreSQL, and SQLite. (Support for Microsoft SQL and Oracle is forthcoming.)

All Solar_Sql_Adapter functions are supported on all databases, so you never need to know the specifics of a particular database backend. If your Solar_Sql calls work on one database, they should work on all databases (with some exceptions for how you write your queries; more on that in the "Portability" section below).

To get started, configure and instantiate a Solar_Sql object using the available Solar_Sql_Adapter::$_config keys.

<?php
// load the Solar environment
require 'Solar.php';
Solar::start('/path/to/config/Solar.config.php');

// configure
$config = array(
    'adapter' => 'Solar_Sql_Adapter_Mysql',
    'host'    => '127.0.0.1',
    'user'    => 'username',
    'pass'    => 'password',
    'name'    => 'database_name',
);

// instantiate
$sql = Solar::factory('Solar_Sql', $config);
?>

If set up your config file like this ...

<?php
$config = array();

$config['Solar_Sql'] = array(
    'adapter' => 'Solar_Sql_Adapter_Mysql',
    'host'    => '127.0.0.1',
    'user'    => 'username',
    'pass'    => 'password',
    'name'    => 'database_name',
);

return $config;
?>

... then Solar_Sql will use those as the default configuration keys.

Once you have a Solar_Sql_Adapter object, you can start issuing queries directly and getting PDOStatement objects in return:

<?php
$sql = Solar::factory('Solar_Sql');
$pdoStatement = $sql->query('SELECT * FROM table_name');
?>

Methods At-A-Glance

To quote values so they can be used safely in SQL commands, use ...

For direct-execution of commands, use...

Solar_Sql_Adapter::query()

To fetch results, use ...

To conveniently manipulate data in individual tables, use...

For auto-increment values and database-portable sequences, use...

For database-portable transactions, use...

For database-portable table/column/index modification, use...