DLib PHP Library API

DLib PHP Library API

Select library file:

General database functions

This file contains all the general purpose generic functions for handling databases such as creating, opening and closing databases, general SQL functions, table update functions and other miscellaneous functions. See dbclasses.php and dbrecord.php for the OO database classes. Also, see the db_mysqli.php file for the MySQL-type specific interface.

The following code shows how a database connection is created, switched to and then opened. Records from a Stock Item table are then read (see dbclasses.php for how to define record layouts).

  $con1 = newDbConnection ("mysqli", "localhost", "dblocal", "dbuser", "abcd123", "root", "rootpswd");

  switchDatabase ($con1);

  if (dbConnect ())
    list ($ok, $stkList) = StockItem::getRecords ("cost < 0.50");

    foreach ($stkList as $stk)
      print "$stk->code - $stk->description - $stk->price\n";

    dbClose ();

Up arrowvoid addTable ($tableName, $className, [$flags], [$sync], [$udb])

Add a table definition. Returns a TableDef instance.

$tableNamestringThe table name to be used.
$classNamestringThe class name representing a database record for this table.
[$flags]integerAny TDF_xxx flags that need to be set.
[$sync]integerAny SYNC_xxx flags that need to be set.
[$udb][integer]Any DB restrictions.
Add a table called stockitems to the current table definitions and associate it with the StockItem record type.
  addTable ("stockitems", "StockItem");

Up arrowboolean closeDatabase ()

Close the currently selected database.

Result: True if database successfully closed.

Up arrowboolean dbConAll ()

Connect to all databases currently set up.

Result: True if all the connections succeeded.

Up arrowboolean dbConnect ()

Connect to the specified database optionally creating the database if allowed. If the systemval table is empty then attempt to create all the tables.

Result: True if the connect succeeded.

Set up a database connection, switch to it and then try to connect.
  $con1 = newDbConnection ("mysqli", "localhost", "dblocal", "dbuser", "abcd123");

  switchDatabase ($con1);

  if (dbConnect ())
    // Connection succeeded!

    // Do some database access here...

Up arrowvoid dbMsg ($msg, $fail)

Database message handler. This accumulates database errors into one string for later display.

$msgstringMessage to add to the database error messages string.
$failintegerFailure number.

Up arrowvoid dbUpdate ()

Check if a database update is required. This is performed once a day or every time this function is called.

Up arrowstring getCharSet ($d, $t, $f)

Read the character set of the specified field for the specified database and table.

Result: The character set string.

$dstringThe database name.
$tstringThe table name.
$fstringThe field name.

Up arrowstring getDbTrace ([$addBR])

Return the current database trace as a string.

Result: String containing a formatted copy of the current DB trace.

[$addBR]booleanAdds BR tags between lines (default is false).

Up arrowinteger newDbConnection ($type, $host, $name, $user, $pswd, [$master], [$mpswd], [$pref], [$charset])

Set up a db connection details. Use switchDatabase() to change between any connection.

Result: Connection number.

$typestringDatabase type (for calling the correct db_[type].php plug-in).
$hoststringServer host name.
$namestringDB name.
$userstringDB user name.
$pswdstringDB password.
[$master]stringDB root access user name (allows creation and dropping).
[$mpswd]stringDB root access password.
[$pref]stringDB table prefix.
[$charset]stringDB default character set.
Set up connection 1 to a local MySQL database and control over database creation (by sending the root user name and password), and connection 2 to a remote MS Access database where we also do not have control over database creation.
  $con1 = newDbConnection ("mysqli", "localhost", "dblocal", "dbuser", "abcd123", "root", "rootpswd");
  $con2 = newDbConnection ("access", "www.banana.org", "dbxyz", "dbuser", "defg987");

Up arrow[mixed] readDbArray ($className, $table, $fields, [$where], [$order], [$limit], [$groupby])

Read an array of records from the specified table into the specified class type using the specified SQL WHERE/ORDER restrictions.

Result: Element 0 = boolean success indicator. Element 1 is an array of records of the specified type.

$classNamestringRecord class name.
$tablestringTable name to be accessed.
$fieldsstringField name CSV that should be returned (prefix with '+' to force a call to specFields).
[$where]stringWHERE statement.
[$order]stringORDER BY statement.
[$limit]stringLIMIT value(s).
[$groupby]stringGROUP BY clause.

Up arrowboolean runSQL ($sql)

Execute an SQL statement. On failure, check for layout changes and, if so, update the table (not SystemVal).

Result: Returns true if SQL succeeded.

$sqlstringThe SQL statement to be executed.

Up arrowboolean switchDatabase ($con, [$connect])

Change which database we are currently accessing.

Result: Success indicator.

$conintegerA database connection number from a newDBConnection function.
[$connect]booleanTrue = attempt to connect to the database as well.
Assuming that $con1 and $con2 have been set up as connections to two separate databases and we have also performed a successful dbConnect call to both of them then we can switch between them. This example reads some records from one database and then saves them to a second before switching back to the original database. Note that we check for the record already existing in the second database and, if it is NOT found, then we set newRec - internally, this tells the system that this is a new record which creates the correct SQL to do an INSERT instead of an UPDATE statement.
  switchDatabase ($con1);
  list ($ok, $recList) = StockItem::getRecords ("cost < 0.50");

  if ($ok)
    switchDatabase ($con2);

    foreach ($recList as $rec)
      $rec2 = new StockItem ($rec->ref);

      if (!$rec2->ok)
        $rec->newRec = true;

      $rec->save ();

    switchDatabase ($con1);

Up arrow[mixed] tableDetails ($name, [$isClass])

Get table entry from the table or class name name.

Result: Returns an array of 3 items: 0 = boolean success indicator; 1 = the associated (Ex)TableDef class; 2 = a blank instance of the associated record class.

$namestringThe table or class name from which to return information.
[$isClass]booleanIf true then search via the class name instead.

Up arrowarray tableUpdateDate ([$doSet], [$dt])

Read/set the last table update date/time value. This value is always set when the system performs a check to see if the table record layouts have changed (whether or not they have actually changed).

Result: Element 0 = success of requested operation. Element 1 = the returned date/time.

[$doSet]booleanTrue to set the date/time value else it just reads the date/time value.
[$dt]stringThe new date/time value to be set (format = YYYYMMDDhhmmss).

Up arrowvoid updateTable ($table)

Update a table definition. This compares the stored record layout with the DbField entries for that record and, if differences are detected, then it attempts to automatically update the database table to bring them into sync.

$tablestringThe name of the table to be updated.

Up arrowvoid updateTables ()

Checks to see if any tables need updating.