Class "DB" - mudmin/UserSpice5-Dev GitHub Wiki

This class defines a set of methods to interact with the database.

The following methods are available:

• Sets up the initial connection. If you include init.php in your page (or use master_form.php) then this is already done for you as a part of the initialization (using the database credentials you configured during installation) and you will never need to explicitly call this method - you will just use the connection via DB::getInstance() and the other methods below.

• Gets an instance of the class, implementing singleton logic to avoid unnecessary redundancy in resource usage. This method is used often - whenever you are going to access the database and don't already have an object of class DB in a variable available to your code. In other words, almost every time you use the database you will use this method immediately beforehand.
• Typical usage:
  $db = DB::instance();
  // now you can use the $db variable containing an object of the DB class

• Provide an interface to execute an arbitrary SQL command
• Function Arguments:
  • $sql is the string containing the SQL command to be executed
  • $bindvals (optional) is an array of values which will be bound to the query through any :val or ? placeholders found in $sql
    • It is beyond the scope of this help document to teach secure SQL usage, but values (especially when provided by a user) should always be put in SQL by means of placeholders and bound values rather than being placed directly in the SQL query string
• Return value: always an object of class DB
• Typical usage:
  $foo = $db->query("SELECT * FROM foo LEFT JOIN bar WHERE foo.a = bar.b");
$bar = $db->query("SELECT * FROM bar WHERE a = ? AND c != ? ORDER by a, b", ['a', 27]);
// use $foo->first() or $bar->results() to fetch - see below $db->query("DELETE FROM foo WHERE a = ?", [27]); $db->query("INSERT INTO foo (a, b, c) VALUES (?, ?, ?)", ['abc', 2, 'def']);`

• a shortcut to simple "SELECT * FROM single-table ... types of queries
• Function Arguments:
  • $table is the name of the table and is required
  • $where has different ways to specify it. See below.
  • $orderBy is an array of field names (or, technically, strings which evaluate to SQL values appropriate to use in the ORDER BY ... clause context)
  • $bindvals is an array of values which will be bound to the query through any :val or ? placeholders
• Return value: always an object of class DB
  • This facilitates chaining, but requires explicit checking of the DB::error() to respond properly to error conditions -- see explanation under DB::findAll() for more details.
• Typical usage:
  $fooQ = $db->queryAll("foo", ['a', '!=', '?'], ['a', 'b'], [578]);
  // corresponds to this call to DB::query():
  $fooQ = $db->query("SELECT * FROM foo WHERE a != ? ORDER BY a, b", [578]);

• findAll is identical to queryAll (above) except if an error occurs findAll() returns false rather than an object of class DB. This inconsistent return value inhibits object chaining as seen in this code (object chaining is possible in this example because we are using queryAll() instead of findAll()):
  foreach ($db->queryAll(...)->results() as $x) {
...
}
  But the findAll() return value (false on error, object on non-error) provides for a more implicit type of error checking such as this code:
  if ($db->findAll(...)) {
...
}
  Use whichever method (queryAll() or findAll()) fits your preferred style of coding.
• Return value: if the query was successful, returns an object of class DB; if an error occurred, returns false

• This is a shortcut to an SQL query such as this:
  $db->query("SELECT * FROM foo WHERE id = ?", [$id]);
• Typical usage:
  $id = $_GET['id'];
$fooR = $db->queryById("foo", $id);
• Return value: always an object of class DB

• See DB::queryById() above for an explanation of usage
• Return value: if the query was successful, returns an object of class DB; if an error occurred, returns false
• findById() is to queryById() as findAll() is to queryAll() above. They differ only according to their return value if an error occurs.
• Typical usage:
  $id = $_GET['id'];
if ($fooQ = $db->findById("foo", $id)) {
// do something with success using $fooQ->first() or $fooQ->results()
}

• an alias for DB::findAll() (see above)

• Implementation of the SQL command DELETE FROM ... WHERE ...
• See DB::queryAll() above for an explanation of the arguments
• Typical usage:
  $db->delete('foo', 'id = ?', [$id]);

• A shortcut to the SQL DELETE command when the WHERE clause is simply id = x
• Typical usage:
  $db->delete('foo', $id); // identical to the example in DB::delete() above

• An internal method, not explicitly private but not normally used

• Implementation of the SQL command INSERT INTO ...
• The $fieldlist variable represents a hash where the key is the column name and the value is the value to be inserted into that column
• Typical usage:
  $fields = ['my_varchar_column' => 'a', 'my_integer_column' => 27];
$db->insert('foo', $fields);
• Values are securely bound

• Implementation of the SQL command $db->query("UPDATE ... SET my_varchar_column = ?, my_integer_column = ? WHERE id = ?", ['abc', 27, $id]);
• See DB::insert() for an explanation of the $fieldlist argument
• Typical usage:
  $fields = ['my_varchar_column' => 'a', 'my_integer_column' => 27];
$db->update('foo', $id, $fields);
• Values are securely bound

• After a data retrieving query (something resulting an a SELECT ... SQL command) the results of that query (all rows) are fetched by using the DB::results() method
• Typical usage:
  foreach ($db->queryAll('foo')->results() as $row) {
// do stuff with $row->id and $row->other_column
}
• Or:
  if ($fooQ = $db->findAll('foo')) {
foreach ($fooQ->results() as $row) {
// do stuff with $row->id and etc
}
}

• Similar to DB::results() except only fetch the first row instead of fetching all rows.
• Typical usage:
  if ($fooQ = $db->findAll('foo', 'name = ?', ['Sally'])) {
$fooR = $fooQ->first();
echo "id for the first Sally is ".fooR->id."<br />\n";
}

• After a query this method will return true (non-zero value) or false (zero) depending on whether any rows were found or affected (it is actually an alias for DB::count())

• After a data retrieving query (something resulting an a SELECT ... SQL command) this method will return the number of rows found or affected
• Typical usage:

• This method returns false if the most recent database action has resulted in no errors, or a 3-element array containing the resulting error information (see PDOStatement::errInfo() in the PHP documentation) if an error occurred
• Typical usage:
  if ($err = $fooQ->error()) {
echo "Oops - error!<br />\n";
echo "ANSI SQL error code: ".$err[0]."<br />\n";
echo "driver specific error code: ".$err[1]."<br />\n";
echo "driver specific error message (string): ".$err[2]."<br />\n";
}

• Typical usage:
  if ($fooQ->error()) {
echo "Oops - ERROR: ".$fooQ->errorString()."!<br />\n";
}

• After an SQL INSERT ... command this method returns the value of the ID generated
• Typical usage:
  $fields = ['colx'=>'valuex', 'coly'=>27];
$db->insert('foo', $fields);
$just_generated_id = $db->lastId();
// now I can use the ID auto-generated in my code

• Return the number of queries we have made against the database during this session (as we loaded this page)

Whenever the $where argument is passed it can be defined in any of 3 ways:

1. it can be a string (in which case it is inserted directly into the SQL verbatim and the corresponding $bindvals must be used to bind values to placeholders)
2. it can be an array of 3 elements (in which case the elements correspond to
   [0=>'a', 1=>'!=', 2=>578]
   to create the SQL WHERE condition
   a != 578, or, more exactly, a != ? with a bind value of 578.)
3. it can be an array of n elements representing multiple conditions
   • This array: ['a' => 2]
     creates this SQL WHERE condition: a = 2 (using bound values)
   • This array: ['a' => ['>=', 5]]
     creates this SQL WHERE condition: a >= 5 (using bound values)
     • In other words a direct hash key => value assumes an = operator. If you want to use an operator besides = then you need to place it in a nested array on the value side.
   • This array: ['a' => 27, 'b' => 51] creates this SQL WHERE condition: a = 27 AND b = 51`
     • When multiple conditions are passed it is assumed that they will be connected with a boolean AND
   • This array: ['a' => 27, 'OR', 'b' => 51] creates this SQL WHERE condition: a = 27 OR b = 51`
     • a non-hashed element to an array (no key specified - like the 'OR' above) is treated as a boolean operator. It is put into the WHERE condition verbatim, so more complicated conditions including parentheses can be included
   • This array: ['(', 'a' => 27, 'OR', 'b' => 51, ')', 'c' => 99] creates this SQL WHERE condition: ( a = 27 OR b = 51 ) AND c = 99`
   • This array: ['a' => null, 'b' => ['!=', null]] creates this SQL WHERE condition: a IS NULL AND b IS NOT NULL`
     • a PHP value of null will assume "IS NULL" if the operator is = and IS NOT NULL if the operator is anything else