dbi4php - k5n.us

Table of Contents

About dbi4php

dbi4php is a database abstraction layer for PHP. It is simple to use (a single file with a simple API) and lean enought to not bloat your PHP application.

It provides a common API for accessing a variety of database systems. This allows you to develop your application without limiting it to working on just a single database. You also won’t need to keep looking at the PHP documentation pages to find the correct function name for a particular database since they don’t use a common naming scheme. (For example, MySQL uses mysql_connect while SQLite uses sqlite_open.)

The dbi4php tools has actually been around since the year 2000 as part of the WebCalendar project. So, it is a very stable tool that has been tested and used in production environments for years.

Currently supported database systems include:

MySQL
MS SQL Server
Oracle
PostgreSQL
ODBC
Interbase
SQLite
IBM DB2

License

All software is licensed under the GNU Lesser General Public License.

If you are interested in obtaining an exception to this license, then please contact us with details of your request.

dbi_connect

Name:

dbi_connect

Description:

Establishes a connection to the database.

Parameters:

host:	Database server name (or localhost)
login:	Database user login (if required)
password:	Database user password (if required)
database:	Database tablespace name

Returns:

The connection variable (needed for dbi_close)

dbi_close

Name:

dbi_close

Description:

Closes the connection to the database.

Parameters:

conn:	The connection variable returned from dbi_connect

Returns:

result of close (true = success)

dbi_query

Name:

dbi_query

Description:

Makes a SQL query

Parameters:

sql:	The SQL to execute. This can be an INSERT, DELETE, UPDATE, or any other valid SQL request.
fatalOnError*:	Should the application exit on a fatal error?
showError*:	Should the message from the database be shown to the user?

Returns:

On success, this will return a query result variable. On failure, this will return false.

dbi_fetch_row

Name:

dbi_fetch_row

Description:

Fetches the next row of data from the database

Parameters:

res:	The resource variable returned from dbi_query or dbi_execute

Returns:

Returns a row of data from the database or false if there are no more rows.

dbi_affected_rows

Name:

dbi_affected_rows

Description:

Returns the number of rows affected from the previous dbi_query or dbi_execute call.

Parameters:

conn:	The database connection returned from dbi_open
res:	The query resource variable returned from either dbi_query or dbi_execute

Returns:

Returns the number of rows affected from the previous dbi_query or dbi_execute call.

dbi_update_blob

Name:

dbi_update_blob

Description:

Updates a blob column in the database

Parameters:

table:	The table name that contains the blob
column:	The column name of the blob
key:	The key to identify the proper row in the table. (Example: "id=4" or "id=4 AND user=’craig’")

Returns:

Returns true on success

dbi_get_blob

Name:

dbi_get_blob

Description:

Gets the binary data of a blob column in the database

Parameters:

table:	The table name that contains the blob
column:	The column name of the blob
key:	The key to identify the proper row in the table. (Example: "id=4" or "id=4 AND user=’craig’")

Returns:

Returns the binary data on success and false on failure.

dbi_free_result

Name:

dbi_free_result

Description:

Frees up the query result resource

Parameters:

res:	The result variable returned from either dbi_query or dbi_execute

Returns:

Returns true on succes, false on failure

dbi_error

Name:	dbi_error
Description:	Returns the error message from the last error
Parameters:	None
Returns:	Returns the error message from the last error

dbi_escape_string

Name:

dbi_escape_string

Description:

Inserts the proper escape sequence for a SQL value for use with dbi_query. You do not need to use this function with dbi_execute since values do not need escaping for that function.

Parameters:

string:	The string to escape

Returns:

Returns the properly escaped string value

dbi_execute

Name:

dbi_execute

Description:

Executes the specified SQL query. This function is very similar to dbi_query. However, it uses prepared statements instead of a standard SQL string. You do not need to escape characters (quotations, for example) when calling this function. Because of this, this function is more secure than dbi_query and should be the preferred way to make queries.

Parameters:

sql:	The SQL command with ‘?’ as place holders for values. For example: INSERT INTO xxx VALUES ( ?, ?, ? )
params:	An array of parameters that correspond to the ‘?’ place holders in the sql parameter. The values do not need to be escaped (unlike dbi_query) for quotes and other characters.
fatalOnError*:	Should the application exit on a fatal error?
showError*:	Should the message from the database be shown to the user?

Returns:

On success, this will return a query result variable. On failure, this will return false.

Tutorial

The dbi4php interface is very simple to use. In most apps, you will want to isolate your dbi_connect and dbi_close calls into a function call. Additionally, you may want to load your database settings (hostname, login, password, database) from a data file. (Look at the WebCalendar code for an example of this.

Open, Query and Close:

<?php
// Db settings
$db_type = 'mysql';
$db_host = 'localhost';
$db_login = 'webcalendar';
$db_password = 'webcal01';
$db_database = 'intranet';

// Include dbi4php file
require_once 'dbi4php.php';

print "<html><body><h1>Simple Demo</h1>\n";

// Open db connection
$c = dbi_connect ( $db_host, $db_login,
  $db_password, $db_database );
if ( ! $c ) {
  print "Error connecting to database: " . dbi_error () .
    "</body></html>\n";
  exit;
}

// Execute SQL query
$res = dbi_query ( "SELECT name FROM users ORDER BY name" );
if ( ! $res )  {
  print "Database error: " . dbi_error () .
    "</body></html>\n";
  exit;
}

print "<ul>\n";

// Loop: get each row
while ( $row = dbi_fetch_row ( $res ) ) {
  // Print out user(row)
  print "<li>" . htmlspecialchars ( $row[0] ) .
    "</li>\n";
}

print "</ul>\n";

// Free result from dbi_query
dbi_free_result ( $res );

// Close connection
dbi_close ( $c );

print "</body></html>\n";
?>

Using dbi_execute with URL parameter:

<?php
// Include dbi4php file
require_once 'dbi4php.php';
// Include common php file that sets db parameters and opens
// db connection
require_once 'startup.php';

$error = '';

print "<html><body><h1>Demo</h1>\n";

// Get "id" parameter if passed in with URL and
// make sure it is just an integer
$id = $_GET['id'];
if ( empty ( $id ) )
  $error = 'No user id specified';
else if ( ! preg_match ( "/^\d+$/", $id ) ) {
  $error = 'Invalid id';
}

if ( empty ( $error ) ) {
  // Build SQL for use in dbi_execute
  $sql = 'SELECT name FROM user WHERE id = ?';
  $params = arary ( $id );
  // Execute query
  $res = dbi_execute ( $sql, $params )
  if ( $res ) ) {
    echo "<p>\n";
    if ( $row = dbi_fetch_row ( $res ) ) {
      echo "User name: " . htmlspecialchars ( $row[0] );
    } else {
      $error = "No such user";
    }
    echo "</p>\n";
    dbi_free_result ( $res );
  } else {
    $error = "Database error: " . dbi_error ();
  }
}

if ( ! empty ( $error ) ) {
  echo "<h2>Error</h2><p>" . $error . "</p>\n";
}

echo "</body></html>\n";
// Close connection in common file
require_once 'shutdown.php';
?>