SQL Generator Libraries

The Db Sql Generator Libraries provide a simple interface that abstracts SQL queries from your code and provides an object-oriented approach to database interaction.

Be sure to include the library:

<?php

include('db.php');

?>

Create the Db object and connect to the database:

$db = new Db('connectionNameOrString', 'username', 'password');

To copy connection, table, and search information:

$db2 = new Db($db);

Select a table to perform actions on:

$db->from('tableName');

To display as an HTML table:

$db->tohtml();

To retrieve generated SQL command:

$db->tosql();

To retrieve a result as an array:

$db->toarray();

To retrieve the odbc_result object:

$db->result();

To count the number of records to be returned:

$db->count();

To search for a string on a column:

$db->search('columnName', 'value');

To search for values on multiple columns:

$db->search('column1Name', 'column1Value');
$db->search('column2Name', 'column2Value');

or

$db->search(array('column1Name', 'column2Name'),
  array('column1Value', 'column2Value');

To search for a number (e.g. 1) on a column:

$db->search('columnName', 1);

To match a value in a set of values:

$db->search('columnName', array('value1', 'value2'))

To match a value in a subquery:

$db->search('columnName', $db2);

To select a column:

$db->select('columnName');

To select multiple columns:

$db->select('column1Name');
$db->select('column2Name');

or

$db->select(array('column1Name', 'column2Name'));

To sort by a value:

$db->sort('columnName');

To sort by multiple values:

$db->sort('column1Name');
$db->sort('column2Name');

or

$db->sort(array('column1Name', 'column2Name');

To group by a value:

$db->group('columnName');

To group by multiple values:

$db->group('column1Name');
$db->group('column2Name');

or

$db->group(array('column1Name', 'column2Name');

From, search, select, group, and sort can be chained for condensed programming:

$db = new Db('connectionNameOrString');
$db->from('tableName')->select('column1Name')->search('columnName', 'columnValue')->sort('columnName');

Table, buildsql, and toarray can be appended to a chain to return a result:

$db2 = new Db($db);
$db2->from('table2Name')->search('columnName', $db)->table();

Call from, search, select, group, and sort without parameters to clear previously entered values:

$db->search();

To update the currently selected records with new values:

$db->update(array(
  'column1Name' => 'column1Value',
  'column2Name' => 'column2Value'
));

To insert a new value into the table:

$db->insert(array(
  'column1Name' => 'column1Value',
  'column2Name' => 'column2Value'
));

To delete all currently selected records:

$db->delete();

Warning: if you do not search() prior to calling delete() all records will be deleted from the table.