Back to the module list

OO Database Layer

Allow to manage a database connection by object-oriented methods Use an abstraction layer to manage the database engines for MySQL, SQLite or PostgreSQL All the requests are "preparated" before use. They are not sensible to SQL injection

Definition of the tables

$table1 = new dblayeroo ($dbconfig["dsn"], $dbconfig["username"],
                         $dbconfig["password"], $dbconfig["driver_options"]);
$table1->table ("groupedoo");
$table1->fields (array ("group"=>array ("varchar(255)", "not null"),
                        "object"=>array ("varchar(255)", "not null"),
                        "where"=>array ("varchar(255)", "not null"),
                        "with space"=>array ("varchar(255)")));
$table1->unique (array ());
$table1->primary ("group");
');

Get data from database: the SELECT method

Select all the rows

$res = $table1->select ()->execute ();

Select with one WHERE clause

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->execute ();

The where clause allow the operators: "=", "<=", ">=", "!=", "NOT LIKE", "LIKE", "IS NULL", "REGEXP", "NOT REGEXP"

Select with multiple WHERE clauses

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

The clauses are by default with AND To add a OR clause

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAddOr ()
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

It is also possible to add parenthesis with

$res = $table1->select ()
              ->whereAddParenthesisOpen ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAddOr ()
              ->whereAdd ("user", "=", "nobody")
              ->whereAddParenthesisClose ()
              ->execute ();

Return only two column

// The column order is defined by the order of adding. The first one is the
// first asked to the database
// The column can be defined by string separated by comma or array
$res = $table1->select ()
              ->displayAdd ("col1,col2")
              ->execute ();

It is also possible to add the group functions in "AVG", "COUNT", "GROUP_CONCAT", "MAX", "MIN","SUM".

$res = $table1->select ()
              ->displayAdd ("group_concat(col2)")
              ->execute ();

It is also possible to limit the result to DISTINCT elements

$res = $table1->select ()
              ->displayAdd ("DISTINCT col2")
              ->execute ();

Add limits

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->limitLines (3)
              ->execute (); ');

Or get only the 3 lines starting at 5th position

$res = $table1->select ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->limit (5, 3)
              ->execute ();

Insert data in the database: the INSERT method

$res = $table1->insert ()
              ->setValues (array ("field1"=>"val1",
                                  "field2"=>"val2"))
              ->execute ();

Attention: can only add one row by call If you call multiple times setValues, only the last one is recorded

Update data in the database: the UPDATE method

$res = $table1->update ()
              ->setValues (array ("field1"=>"val1",
                                  "field2"=>"val2"))
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

If there is no whereAdd call, all the existing rows are overwrited

Remove data from the database: the DELETE method

$res = $table1->delete ()
              ->whereAdd ("gecos", "LIKE", "firstname%")
              ->whereAdd ("user", "=", "nobody")
              ->execute ();

If you don't specify the whereAdd clause, all the table is cleared

Join two tables

The library allow to join Inner, Left, Right. It need two dblayeroo objects, one for each table.

$res = $table1->select ()
              ->joinInner ($table2, array ("fieldATable1"=>"fieldBTable2",
                                           "fieldCTable1"=>"fieldDTable2"))
              ->execute ();