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.
API
- dbi_connect
- dbi_close
- dbi_query
- dbi_fetch_row
- dbi_affected_rows
- dbi_update_blob
- dbi_get_blob
- dbi_free_result
- dbi_error
- dbi_escape_string
- dbi_execute
dbi_connect
Name: | dbi_connect | ||||||||
Description: | Establishes a connection to the database. | ||||||||
Parameters: |
| ||||||||
Returns: | The connection variable (needed for dbi_close) |
dbi_close
Name: | dbi_close | ||
Description: | Closes the connection to the database. | ||
Parameters: |
| ||
Returns: | result of close (true = success) |
dbi_query
Name: | dbi_query | ||||||
Description: | Makes a SQL query | ||||||
Parameters: |
| ||||||
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: |
| ||
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: |
| ||||
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: |
| ||||||
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: |
| ||||||
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: |
| ||
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: |
| ||
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: |
| ||||||||
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'; ?>
Download
Download version 1.0 below from SourceForge.net:
dbi4php-1.0.zip [20kb]
dbi4php-1.0.tar.gz [18kb]
Download the CVS nightly tarball from CVS:
Developer Resources
- Tutorial on using the tools
- SourceForge.net resources: