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).

EXAMPLE

  $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 ();
  }

File Version: 7.2.8 - 21 Nov, 2023

Class/Function/Method List

addTable
closeDatabase
dbConAll
dbConnect
dbMsg
dbUpdate
getCharSet
getDbTrace
newDbConnection
readDbArray
runSQL
switchDatabase
tableDetails
tableUpdateDate
updateTable
updateTables

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

Add a table definition. Returns a TableDef instance.

Param	Type	Details
$tableName	string	The table name to be used.
$className	string	The class name representing a database record for this table.
[$flags]	integer	Any TDF_xxx flags that need to be set.
[$sync]	integer	Any SYNC_xxx flags that need to be set.
[$udb]	[integer]	Any DB restrictions.

EXAMPLE

Add a table called stockitems to the current table definitions and associate it with the StockItem record type.

  addTable ("stockitems", "StockItem");

boolean closeDatabase ()

Close the currently selected database.

Result: True if database successfully closed.

boolean dbConAll ()

Connect to all databases currently set up.

Result: True if all the connections succeeded.

boolean 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.

EXAMPLE

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...
  }

void dbMsg ($msg, $fail)

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

Param	Type	Details
$msg	string	Message to add to the database error messages string.
$fail	integer	Failure number.

void dbUpdate ()

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

string getCharSet ($d, $t, $f)

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

Result: The character set string.

Param	Type	Details
$d	string	The database name.
$t	string	The table name.
$f	string	The field name.

string getDbTrace ([$addBR])

Return the current database trace as a string.

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

Param	Type	Details
[$addBR]	boolean	Adds BR tags between lines (default is false).

integer 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.

Param	Type	Details
$type	string	Database type (for calling the correct db_[type].php plug-in).
$host	string	Server host name.
$name	string	DB name.
$user	string	DB user name.
$pswd	string	DB password.
[$master]	string	DB root access user name (allows creation and dropping).
[$mpswd]	string	DB root access password.
[$pref]	string	DB table prefix.
[$charset]	string	DB default character set.

EXAMPLE

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");

[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.

Param	Type	Details
$className	string	Record class name.
$table	string	Table name to be accessed.
$fields	string	Field name CSV that should be returned (prefix with '+' to force a call to specFields).
[$where]	string	WHERE statement.
[$order]	string	ORDER BY statement.
[$limit]	string	LIMIT value(s).
[$groupby]	string	GROUP BY clause.

boolean 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.

Param	Type	Details
$sql	string	The SQL statement to be executed.

boolean switchDatabase ($con, [$connect])

Change which database we are currently accessing.

Result: Success indicator.

Param	Type	Details
$con	integer	A database connection number from a newDBConnection function.
[$connect]	boolean	True = attempt to connect to the database as well.

EXAMPLE

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);
  }

[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.

Param	Type	Details
$name	string	The table or class name from which to return information.
[$isClass]	boolean	If true then search via the class name instead.

array 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.

Param	Type	Details
[$doSet]	boolean	True to set the date/time value else it just reads the date/time value.
[$dt]	string	The new date/time value to be set (format = YYYYMMDDhhmmss).

void 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.

Param	Type	Details
$table	string	The name of the table to be updated.

void updateTables ()

Checks to see if any tables need updating.