Blog

How to Write Code for Any Database with PHP ADODB

Tags: , , , , ,
You are currently viewing How to Write Code for Any Database with PHP ADODB
Credits

Database abstraction layer is a software with which database tasks (connect, execute queries etc) can be performed with more than one databases. So, in most cases, the software application can be functioning with many databases without modification to source code.

When our application requirements include operation with more than one databases, a database abstraction layer must be used. There are various solutions as PDO, Pear MDB2, php ADODB and more. You can also read a comparison here.

php ADODB is an active Open Source project and it is always a good solution. Detailed documentation is available here.

Installation

Download and unpack in a folder accessible by the web server.

In order to use php ADODB in a php script, set

        require_once ADODB_PATH . '/adodb.inc.php'

(where ADODB_PATH is a constant, which is holding the path to ADODB library).

Connect

For most supported databases, Data source name (DSN) method can be used:

This is an example, which can be used for DSN connections with MySQL, PostGreSQL or Firebird. In order to create DSN for other Databases, see DSN support.

/**
 * $DBType
 *
 * for MySQL choose one of 'mysqli', 'pdo_mysql'
 *           (avoid to use old MySQL drivers 'mysqlt, 'mysql')
 *
 * for PostGreSQL use 'postgres'
 *
 * for Firebird use 'firebird'
 *
 * for other Databases, see http://www.phplens.com/lens/adodb/docs-adodb.htm#dsnsupport
 */
$DBType = 'mysqli';

$DBServer = 'localhost'; // server name or IP address
$DBUser = 'DB_USER';
$DBPass = rawurlencode('DB_PASSWORD');
$DBName = 'DB_NAME';

// 1=ADODB_FETCH_NUM, 2=ADODB_FETCH_ASSOC, 3=ADODB_FETCH_BOTH
$dsn_options='?persist=0&fetchmode=2';

$dsn = "$DBType://$DBUser:$DBPass@$DBServer/$DBName$dsn_options";

$conn = NewADOConnection($dsn);

Select

Use the following syntax:

$sql='SELECT col1, col2, col3 FROM table1 WHERE condition';

$rs=$conn->Execute($sql);

if($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $rows_returned = $rs->RecordCount();
}

Iterate over recordset

Using column names (“fetchmode=2”) – recommended

$rs->MoveFirst();
while (!$rs->EOF) {
        print $rs->fields['col1_name'].' '.$rs->fields['col2_name'].'';
        $rs->MoveNext();
}

Using column index (“fetchmode=1”)

$rs->MoveFirst();
while (!$rs->EOF) {
        print $rs->fields[0].' '.$rs->fields[1].'';
        $rs->MoveNext();
}

Store values to array

$rs=$conn->Execute($sql);

if($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $arr = $rs->GetRows();
}

Store first row values to array

$arr = $conn->GetRow($sql);

if($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
}

Record count

$rows_returned = $rs->RecordCount();

Move inside recordset

$rs->Move(10);

Select Limit

Use the following syntax:

$rs=$conn->SelectLimit($sql,10,3); // return 10 records with offset 3

Insert

Use the following syntax:

$v1=$conn->qstr('col1_value');

$sql="INSERT INTO tbl (col1_varchar, col2_number) VALUES ($v1,1)";

if($conn->Execute($sql) === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $last_inserted_id = $conn->Insert_ID();
        $affected_rows = $conn->Affected_Rows();
}

Update

Use the following syntax:

$v1=$conn->qstr('col1_value');

$sql="UPDATE tbl SET col1_varchar=$v1, col2_number=1 WHERE id>10";

if($conn->Execute($sql) === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $affected_rows = $conn->Affected_Rows();
}

Delete

Use the following syntax:

$sql="DELETE FROM tbl WHERE id>10";

if($conn->Execute($sql) === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $affected_rows = $conn->Affected_Rows();
}

Transactions

Traditional approach:

$conn->BeginTrans();

if(!$conn->Execute($sql1)) {
        $err = $conn->ErrorMsg();
        $conn->RollbackTrans();
        trigger_error('Wrong SQL: ' . $sql1 . ' Error: ' . $err . '<br>', E_USER_ERROR);
}
if(!$conn->Execute($sql2)) {
        $err = $conn->ErrorMsg();
        $conn->RollbackTrans();
        trigger_error('Wrong SQL: ' . $sql2 . ' Error: ' . $err . '<br>', E_USER_ERROR);
}
if(!$conn->Execute($sql3)) {
        $err = $conn->ErrorMsg();
        $conn->RollbackTrans();
        trigger_error('Wrong SQL: ' . $sql3 . ' Error: ' . $err . '<br>', E_USER_ERROR);
}

echo 'Transaction completed!';

$conn->CommitTrans();

Smart transactions:

$conn->StartTrans();

if(!$conn->Execute($sql1)) {
        echo 'Wrong SQL: ' . $sql1 . ' Error: ' . $conn->ErrorMsg() . '<br>';
}
if(!$conn->Execute($sql2)) {
        echo 'Wrong SQL: ' . $sql2 . ' Error: ' . $conn->ErrorMsg() . '<br>';
}
if(!$conn->Execute($sql3)) {
        echo 'Wrong SQL: ' . $sql3 . ' Error: ' . $conn->ErrorMsg() . '<br>';
}

echo $conn->HasFailedTrans() ? 'Transaction failed...' : 'Transaction completed!';

$conn->CompleteTrans();

WARNING: some MySQL statements cause an implicit commit, so the cannot be used inside a transaction. For example, you cannot rollback MySQL CREATE TABLE or TRUNCATE TABLE inside a transcaction. A useful comparison is available here.

Quoting and escaping strings

You have probably noticed that every string value is escaped before inserted to database as special characters may break SQL and, moreover, to prevent SQL injection.

$safe_string = $conn->qstr($string);

Example: bla"blabla will be converted to bla"bla\bla.

However, this is not required, if we use Prepared statements (see below).

Prepared statements

Prepared statements can be implemented using the following syntax:

$sql='SELECT * FROM customers WHERE firstname = ? AND lastname = ?';
$a_bind_params = array('John', 'Doe');
$rs = $conn->Execute($sql, $a_bind_params);

if($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->ErrorMsg(), E_USER_ERROR);
} else {
        $arr = $rs->GetRows();
}

In this example, we are using “SELECT”, but any king of SQL statement can be used as prepared statement (SELECT, UPDATE, INSERT etc).

Concerning the popular MySQL database, we need to know that php mysql extension does not support prepared statements. But, mysqli and PDO support prepared statements.

Prepared Statements are much more effective to sanitize user input, so we do not need to Escape SQL strings.

If you’re not familiar with the use of Prepared Statements, you should do it, as it is very important for web applications security.

Free memory

Optional:

$rs->Close();

Disconnect

Optional:

$conn->Close();

Remarks

ADOdb C extension for PHP

ADOdb C extension for PHP is useful, as it increases Execute performance. It can also speed-up the movement inside a recordset, which however requires a different syntax (eg adodb_movenext($rs) instead of $rs->MoveNext()).

Install on Debian server:

        apt-get install php5-adodb

Compile from source code on other Linux distributions: see here.

Code completion fails

Code completion is not functioning, due to ADOdb phpdoc syntax errors. There are workarounds available for PhpStorm and Netbeans.