9. Working with Other Databases
Apart from MySQL, PHP supports almost all relevant (and some not-so-relevant ones, as well) databases on Earth. This chapter features the basic techniques used with many of those systems, especially connecting to the database, executing SQL statements, and retrieving the return data. As in Chapter 8, “Working with MySQL Databases,” we are using a sample database called phrasebook and a sample schema/table from within it called quotes, with the following four fields:
• id—An integer value that is increased by one for each new entry entered into the database. Depending on the database system, the data type is either called IDENTITY, auto_increment, or something similar.
• quote—The quote, as a VARCHAR(255); this length works with all systems.
• author—The person who produced the quote, as a VARCHAR(50).
• year—The year the quote was produced, of type INT.
Connecting to SQLite
@sqlite_open('quotes.db', 0666, $error)
$db = new SQLite3('quotes.db'),
<?php
if ($db = @sqlite_open('quotes.db', 0666, $error)) {
echo 'Connected to the database.';
sqlite_close($db);
} else {
echo 'Connection failed: ' . htmlspecialchars($error);
}
?>
Connecting to SQLite (sqlite_open.php)
Starting with PHP 5, SQLite is bundled with the scripting language. This is a lightweight, file-based database. That allows very fast reading (in many cases, even faster than when using a “real” database), but writing can sometimes take longer than with other systems because file locking is an issue. However, PHP 4 users can also use SQLite because a PECL (PHP Extension Community Library) module is available (http://pecl.php.net/).
If you use PHP 5.0-5.3, SQLite support is already included. In PHP 5.4, the SQLite extension (ext/sqlite) has been moved to PECL. As an alternative, PHP 5.4 (and PHP 5.3, as well) offers the ext/sqlite3 extension, which is also covered in this chapter.
Windows users have to use (or compile) php_sqlite.dll, copy it to PHP’s extension folder, and then load it using extension=php_sqlite.dll in their php.ini for ext/sqlite or extension=php_sqlite3.dll for ext/sqlite3.
When using ext/sqlite, you can connect to a SQLite data source using sqlite_open(). As the first parameter, you provide the name of the database file; it gets created if it doesn’t exist yet. For this to work, the PHP process needs read and write privileges to the file. The second parameter is the file open mode (0666 is recommended); however, as of this writing, this parameter is ignored. The third parameter is a variable that contains any error messages (such as insufficient rights).
Just like ext/mysqli, the SQLite extension under PHP 5 can also be accessed using an object-oriented programming (OOP) approach:
<?php
$db = new SQLiteDatabase('quotes.db'),
echo 'Connected to the database.';
$db->close();
?>
For the sake of backward compatibility, this chapter uses the functional approach for the subsequent phrases.
The ext/sqlite3 extension uses a mandatory OOP application programming interface (API). Here, the open() method of the SQLite3 class would open a database file. Because you need to instantiate that class anyway, a quicker approach is to provide the filename in the class constructor, which in turn would open the database, as well. Error handling could be achieved with a try-catch-block:
<?php
try {
$db = new SQLite3('quotes.db'),
echo 'Connected to the database.';
$db->close();
} catch ($ex) {
echo 'An error has occurred.';
}
?>
Connecting to SQLite3 (sqlite3_open.php)
Sending SQL to SQLite
sqlite_exec()
$db->exec()
<?php
if ($db = @sqlite_open('quotes.db', 0666, $error)) {
sqlite_exec($db, sprintf(
'INSERT INTO quotes (quote, author, year)
VALUES ('%s', '%s', '%s')',
sqlite_escape_string($_POST['quote']),
sqlite_escape_string($_POST['author']),
intval($_POST['year'])));
echo 'Quote saved.';
sqlite_close($db);
} else {
echo 'Connection failed: ' . htmlspecialchars($error);
}
?>
Sending SQL to SQLite (sqlite_exec.php; excerpt)
The PHP function sqlite_exec() sends a SQL statement to the database using ext/sqlite. As the first parameter, the database handle—returned by sqlite_open()—is used; the second parameter is the SQL string. To avoid SQL injection, the PHP function sqlite_escape_string() escapes dangerous characters in dynamic data. The preceding code implements this for the sample table quotes that have also been used in the MySQL phrases in Chapter 8.
Users of ext/sqlite3 need to execute the exec() method of the SQLite3 class:
<?php
try {
$db = new SQLite3('quotes.db'),
$db->exec(sprintf(
'INSERT INTO quotes (quote, author, year) VALUES ('%s', '%s', '%s')',
$db->escape_string($_POST['quote']),
$db->escape_string($_POST['author']),
intval($_POST['year'])));
echo 'Quote saved.';
$db->close();
} catch ($ex) {
echo 'An error has occurred.';
}
?>
Sending SQL to SQLite3 (sqlite3_exec.php; excerpt)
If a table contains an identity column (data type INTEGER PRIMARY KEY when using SQLite), calling the sqlite_last_insert_rowid() function (for ext/sqlite) or the lastInsertRowID() method (for ext/sqlite3) after sending a SQL statement returns the value this column has for the new entry in the database.
Retrieving Results of a Query to SQLite
$result = sqlite_query($db, 'SELECT * FROM quotes'),
sqlite_fetch_array($result);
$result = $db->query('SELECT * FROM quotes'),
$result->fetchArray();
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
try {
$db = new SQLite3('quotes.db'),
$result = $db->query('SELECT * FROM quotes'),
while ($row = $result->fetchArray($result)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row['id']),
htmlspecialchars($row['quote']),
htmlspecialchars($row['author']),
htmlspecialchars($row['year'])
);
}
$db->close();
} catch ($ex) {
echo 'An error has occurred.';
}
?>
</table>
Retrieving Data from SQLite3 (sqlite3_fetch.php; excerpt)
The function sqlite_exec() and method exec() from the previous phrase are very quick performing; however, it is not possible to access return values from the SQL statement sent with it. For this, sqlite_query() (ext/sqlite) or query() (ext/sqlite3) must be used. This function or method returns a handle of the resultset of the query. The following functions and methods can then be used to iterate over the resultset:
• The sqlite_fetch_arrray() function and fetchArray() method return the current row in the resultset as an associative array (field names become keys) and moves farther to the next row.
• sqlite_fetch_object() (no equivalent exists for ext/sqlite3) returns the current row in the resultset as an object (field names become properties) and moves farther to the next row.
• sqlite_fetch_all() (again, no equivalent exists for ext/sqlite3) returns the complete resultset as an array of associative arrays.
The preceding listing shows how to access all data within the resultset using the fetchArray() method; the following code does roughly the same, this time with sqlite_fetch_object(). A while loop calls this method or function as long as it returns something other than false (which means that there is no data left):
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
if ($db = @sqlite_open('quotes.db', 0666, $error)) {
$result = sqlite_query($db, 'SELECT * FROM quotes'),
while ($row = sqlite_fetch_object($result)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row->id),
htmlspecialchars($row->quote),
htmlspecialchars($row->author),
htmlspecialchars($row->year)
);
}
sqlite_close($db);
} else {
printf('<tr><td colspan="4">Connection failed: %s</td></tr>',
htmlspecialchars($error));
}
?>
</table>
Retrieving Data from SQLite (sqlite_fetch.php; excerpt)
Using sqlite_fetch_all() reads the whole resultset into memory simultaneously, if you are using ext/sqlite; ext/sqlite3 does not support this feature. So, if you do not have much data, this is the best-performing method. If you have more data, an iterative approach using sqlite_fetch_array() and sqlite_fetch_object() might be better.
Using Prepared Statements with SQLite
$stmt = $db->prepare('INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)'),
$stmt->bindValue(':quote', $_POST['quote']);
<?php
try {
$db = new SQLite3('quotes.db'),
$stmt = $db->prepare('INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)'),
$stmt->bindValue(':quote', $_POST['quote'], SQLITE3_TEXT);
$stmt->bindValue(':author, $_POST['author'], SQLITE3_TEXT);
$stmt->bindValue(':year', intval($_POST['year']), SQLITE3_INTEGER);
$db->close();
} catch ($ex) {
echo 'An error has occurred.';
}
?>
Retrieving Data from SQLite3 (sqlite3_fetch.php; excerpt)
Prepared statements separate SQL commands from data by supporting placeholders within SQL. In a second step, values can be assigned to those placeholders.
When using ext_sqlite3, prepared statements can be used. In the first step, the prepare() method creates a SQLite3Stmt object representing a statement. Placeholders are denoted by a colon and the placeholder’s name:
$stmt = $db->prepare('INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)'),
Then, the statement object’s bindValue() method assigns a value to a specific placeholder. You provide the name of the placeholder, the value to be assigned, and the data type to be used:
$stmt->bindValue(':quote', $_POST['quote'], SQLITE3_TEXT);
Currently, ext/sqlite3 supports these data types:
• SQLITE3_BLOB—BLOB data (binary large object)
• SQLITE3_FLOAT—A float value
• SQLITE3_INTEGER—An integer value
• SQLITE3_NULL—A NULL value
• SQLITE3_TEXT—A string
The bindValue() method binds the value you provide to the placeholder. Instead, you could also use the bindParam() method, which binds the parameter you provide to the statement placeholder. Note that in that case you cannot use a value, because you need to provide a parameter that may be passed by reference. The following code would not work:
$stmt->bindParam(':year', 2012, SQLITE3_INTEGER);
Connecting to PostgreSQL
@pg_connect()
<?php
if ($db = @pg_connect('host=localhost port=5432 dbname=phrasebook user=postgres password=abc123')) {
echo 'Connected to the database.';
pg_close($db);
} else {
echo 'Connection failed.';
}
?>
Connecting to PostgreSQL (pg_connect.php)
PostgreSQL has a growing fan base. Some even say this is because with version 8.0 finally came a native Windows version. However, most production systems that use PostgreSQL are hosted on Linux or UNIX, nevertheless.
After you install the database, you can use Web-based administration software such as phpPgAdmin (http://sourceforge.net/projects/phppgadmin) or other tools such as the graphical user interface (GUI) application pgAdmin (www.pgadmin.org/) to administer the database. Alternatively, you can use the command-line tool. To allow PHP to access the PostgreSQL installation, Windows users must load the extension with the entry extension (php_pgsql.dll) in php.ini; UNIX/Linux users configure PHP with the switch --with-pgsql=/path/to/pgsql.
Then, pg_connect() connects to the data source. You have to provide a connection string that contains all important data, including host, port, name of the database, and user credentials.
Sending SQL to PostgreSQL
pg_query()
<?php
if ($db = @pg_connect('host=localhost port=5432 dbname=phrasebook user=postgres password=abc123')) {
pg_query($db, sprintf(
'INSERT INTO quotes (quote, author, year) VALUES ('%s', '%s', '%s')',
pg_escape_string($_POST['quote']),
pg_escape_string($_POST['author']),
intval($_POST['year'])));
echo 'Quote saved.';
pg_close($db);
} else {
echo 'Connection failed.';
}
?>
Sending SQL to PostgreSQL (pg_query.php; excerpt)
The function pg_query() sends SQL to the PostgreSQL installation. Again, escaping potentially dangerous characters such as single quotes is a must; this can be done with the pg_escape_string() function. In this code, you see the PHP portion of the script that accepts funny (or not-so-funny) phrases in an HTML form and writes them to the database.
Retrieving the value in the identity column after the last INSERT statement is a bit tricky. The PostgreSQL term for such a data type is SERIAL, which automatically creates a sequence. To get the sequence’s value, you can use pg_last_oid() to retrieve the oid (object id) of this value. Then, execute a SELECT id FROM quotes WHERE oid=<oid>, when <oid> is the oid you just retrieved. This then returns the desired value.
Updating Data in PostgreSQL
pg_insert()
<?php
if ($db = @pg_connect('host=localhost port=5432 dbname=phrasebook user=postgres password=abc123')) {
$data = array(
'quote' => pg_escape_string($_POST['quote']),
'author' => pg_escape_string($_POST['author']),
'year' => intval($_POST['year'])
);
pg_insert($db, 'quotes', $data);
echo 'Quote saved.';
pg_close($db);
} else {
echo 'Connection failed.';
}
?>
Sending SQL to PostgreSQL (pg_insert.php; excerpt)
Another way to insert or update data in PostgreSQL comes in the form of the functions pg_insert() and pg_update(). The first parameter must be the database handle, the second parameter is the table to be inserted into/updated, and the third parameter contains some data in the form of an associative array. (Column names are the keys.) In the event of an UPDATE SQL statement, the update condition must also be submitted as an array in the fourth parameter of the function. The preceding code shows how to insert data.
Retrieving Results of a Query to PostgreSQL
$result = pg_query();
pg fetch row($result);
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
if ($db = @pg_connect('host=localhost port=5432 dbname=phrasebook user=postgres password=abc123')) {
$result = pg_query($db, 'SELECT * FROM quotes'),
while ($row = pg_fetch_row($result)) {
vprintf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
$row
);
}
pg_close($db);
} else {
echo '<tr><td colspan="4">Connection failed.</td></tr>';
}
?>
</table>
Retrieving Data from PostgreSQL (pg_fetch.php; excerpt)
The return value of a call to pg_query() is a pointer to a resultset that can be used with these functions:
• pg_fetch_assoc() returns the current row in the resultset as an associative array.
• pg_fetch_object() returns the current row in the resultset as an object.
• pg_fetch_row() returns the current row in the resultset as a numeric array.
• pg_fetch_all() returns the complete resultset as an array of associative arrays.
The preceding code uses pg_fetch_row() to read out all data from the quotes table.
Alternatively, pg_select() works similarly to pg_insert() and pg_update(). Just provide a database handle, a table name, and maybe a WHERE clause in the form of an array, and you get the complete resultset as an array of (associative) arrays:
$data = pg_select($db, 'quotes'),
Connecting to Oracle
@oci_connect()
<?php
if ($db = @oci_connect('scott', 'tiger', 'orcl')) {
echo 'Connected to the database.';
oci_close($db);
} else {
echo 'Connection failed.';
}
?>
Connecting to Oracle (oci_connect.php)
Two PHP extensions are available for Oracle, but only one is actively maintained and also works with more recent versions of the relationship database management system (RDBMS). To install it, configure PHP with the switch --with--oci8. The environment variable ORACLE_HOME must be set so that PHP can find the client libraries. Windows users need the php.ini directive extension=php_oci8.dll. In addition, PHP requires read access to the client libraries (which need to be installed separately). Then, oci_connect() tries to establish a connection to the server. The order of the parameters is a bit strange: first username and password and then the name of the service (that has been configured using the configuration assistant or is part of the tnsnames.ora file). The return value is a handle to the connection and is required by further operations in the database.
Sending SQL to Oracle
oci_execute()
<?php
if ($db = @oci_connect('scott', 'tiger', 'orcl')) {
require_once 'stripFormSlashes.inc.php';
$sql = 'INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)';
$stmt = oci_parse($db, $sql);
oci_bind_by_name($stmt, ':quote', $_POST['quote']);
oci_bind_by_name($stmt, ':author', $_POST['author']);
oci_bind_by_name($stmt, ':year', intval($_POST['year']));
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
echo 'Quote saved.';
oci_close($db);
} else {
echo 'Connection failed.';
}
?>
Sending SQL to Oracle (oci_execute.php; excerpt)
This section again uses the quotes table, which also includes an identity column; however, this is a bit more complicated to implement with Oracle. Refer to the script quotes.oracle.sql in the download archive for more information.
To send SQL to Oracle, two steps are required. First, a call to oci_parse() parses a SQL string and returns a resource that can then be executed using oci_execute(). The second parameter of oci_execute() is quite important. Several constants are allowed, but most of the time, OCI_DEFAULT is used. Despite the name, that’s not the default value, but means “no autocommit.” In contrast, OCI_COMMIT_ON_SUCCESS commits the pending transaction when no error has occurred. And this is, indeed, the default value.
Unfortunately, there is no such thing as oci_escape_string() to escape special characters for use in a SQL statement. Therefore, prepared statements are a must—but are also very easy to implement. For this, the SQL statement must contain placeholders that start with a colon:
$sql = 'INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)';
Then, these placeholders have to be filled with values. For this, oci_bind_by_name() must be used:
oci_bind_by_name($stmt, ':quote', $_POST['quote']);
The preceding code sends some form data to the database. No need to worry about special characters because oci_bind_by_name() takes care of that.
When you are using OCI_DEFAULT as the commit mode, the changes must be written to the database using oci_commit($db); oci_rollback($db) performs a rollback.
By the way, if you want to retrieve the autovalue of the most recent INSERT operation, you have to do it within a transaction and execute SELECT quotes_id.CURVAL AS id FROM DUAL, where quotes_id is the name of the sequence you are using.
Retrieving Results of a Query to Oracle
oci_fetch_object($stmt)
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
if ($db = @oci_connect('scott', 'tiger', 'orcl')) {
$stmt = oci_parse($db, 'SELECT * FROM quotes'),
oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
while ($row = oci_fetch_object($stmt)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row->ID),
htmlspecialchars($row->QUOTE),
htmlspecialchars($row->AUTHOR),
htmlspecialchars($row->YEAR)
);
}
oci_close($db);
} else {
echo '<tr><td colspan="4">Connection failed.</td></tr>';
}
?>
</table>
Retrieving Data from Oracle (oci_fetch.php; excerpt)
You have several ways to access the return values of a SQL query, but the following functions are used most often in practice:
• oci_fetch_assoc() returns the current row in the resultset as an associative array.
• oci_fetch_object() returns the current row in the resultset as an object.
• oci_fetch_row() returns the current row in the resultset as a numeric array.
• oci_fetch_all() returns the complete resultset as an array of associative arrays. However, five parameters are required: the statement object from oci_parse(), the array that is used for the return data, the number of lines to skip, the maximum number of rows to be returned (-1 means infinite), and whether to return a numeric (OCI_NUM) or associative (OCI_ASSOC) array.
The listing in this phrase uses a while loop and oci_fetch_object() to retrieve all data in the table.
Oracle always returns column names in uppercase. Therefore, you have to use uppercase object properties or uppercase associative array keys when accessing the return values of a SQL query.
Connecting to MSSQL
@sqlsrv_connect()
<?php
$config = array('UID' => 'user', 'PWD' => 'password', 'Database' => 'phrasebook'),
if ($db = @sqlsrv_connect('(local)SQLEXPRESS', $config)) {
echo 'Connected to the database.';
sqlsrv_close($db);
} else {
echo 'Connection failed.';
}
?>
Connecting to MSSQL (sqlsrv_connect.php)
The Microsoft SQL engine comes in two flavors: the fully featured (and fully priced) Microsoft SQL Server (short: MSSQL) and the free edition, the Microsoft SQL Server Express Edition, available for free at www.microsoft.com/sqlserver/en/us/editions/express.aspx. Both versions are supported by PHP because they are compatible with each other.
PHP ships with an extension for MSSQL, ext/mssql. However it is quite old and not actively maintained anymore. Instead, Microsoft provides a new (Windows-only) extension, ext/sqlsrv, which is the recommended one to use.
To make it work, you need to add the extension using extension=<filename>.dll in your php.ini. Also, you need the extension itself. Depending on which version of PHP you are using, you need a specific version. The PHP manual page http://php.net/sqlsrv.installation points you to the correct download page. If possible, you should use at least version 3 of the extension, which uses the Microsoft SQL Server 2012 Native Client libraries; version 2 requires the Microsoft SQL Server 2008 R2 Native Client libraries. Microsoft describes the up-to-date system requirements at http://msdn.microsoft.com/en-us/library/cc296170.aspx.
To connect to MSSQL, call mssql_connect() and provide the name of the server ((local) serves as shortcut for localhost); if you are using a named instance of Microsoft SQL Server (for example, SQLEXPRESS, which is installed with the MSSQL Express Edition by default), you append it to the server name, separated by a backslash: (local)SQLEXPRESS. As the second argument, you can provide an array with connection information. The following array keys are supported by the extension:
• Database—Name of the database
• PWD—Password
• UID—Username
If you want to authenticate against the database with the current user (sometimes also called trusted connection), just omit the UID and PWD configuration settings.
Sending SQL to MSSQL
sqlsrv_query()
<?php
$config = array('UID' => 'user', 'PWD' => 'password', 'Database' => 'phrasebook'),
if ($db = @sqlsrv_connect('(local)SQLEXPRESS', $config)) {
ini_set('magic_quotes_sybase', 'On'),
sqlsrv_query($db, sprintf(
'INSERT INTO quotes (quote, author, year) VALUES ('%s', '%s', '%s')',
addslashes($_POST['quote']),
addslashes($_POST['author']),
intval($_POST['year'])));
echo 'Quote saved.';
sqlsrv_close($db);
} else {
echo 'Connection failed.';
}
?>
Sending SQL to MSSQL (sqlsrv_execute.php; excerpt)
The function sqlsrv_query() sends a SQL statement to the MSSQL installation. The parameter order is similar to the one from ext/mysqli: first the database handle, then the SQL command.
Another important point is escaping special characters. In MSSQL, single quotes must not be escaped using a backslash, but double quotes are the way to go:
INSERT INTO quotes (quote, author, year) VALUES ('Ain''t Misbehavin''', 'Louis Armstrong', 1929)
To achieve this, you can use addslashes(). However, first, you must configure it to behave so that MSSQL/MSDE-compatible strings are returned:
ini_set('magic_quotes_sybase', 'On'),
$author = addslashes($_POST['author']);
One word of warning, though: The magic_quotes_sybase setting has been deprecated in PHP 5.3 and removed in PHP 5.4. If you are using user information in SQL statements, you should use prepared statements instead (two phrases onward).
The listing at the beginning of this phrase sanitizes some form data and writes it to the (by now) well-known sample database.
Retrieving Results of a Query to MSSQL
$result = sqlsrv_query();
sqlsrv_fetch_object($result);
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
$config = array('UID' => 'user', 'PWD' => 'password', 'Database' => 'phrasebook'),
if ($db = @sqlsrv_connect('(local)SQLEXPRESS', $config)) {
$result = sqlsrv_query($db, 'SELECT * FROM quotes'),
while ($obj = sqlsrv_fetch_object($result)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row->id),
htmlspecialchars($row->quote),
htmlspecialchars($row->author),
htmlspecialchars($row->year)
);
}
sqlsrv_close($db);
} else {
echo '<tr><td colspan="4">Connection failed.</td></tr>';
}
?>
</table>
Retrieving Data from MSSQL (sqlsrv_fetch.php; excerpt)
Finally, there is, of course, a way to retrieve all data in the resultset. A while loop comes into play, using one of these functions:
• sqlsrv_fetch_array() returns the current row in the resultset as an associative array and/or a numerically indexed array; by default, it returns both.
• sqlsrv_fetch_object() returns the current row in the resultset as an object.
Using Prepared Statements with MSSQL
$stmt = sqlsrv_query();
sqlsrv_execute($stmt);
<?php
$config = array('UID' => 'user', 'PWD' => 'password', 'Database' => 'phrasebook'),
if ($db = @sqlsrv_connect('(local)SQLEXPRESS', $config)) {
$year_as_int = intval($_POST['year']);
$data = array(&$_POST['quote'], &$_POST['author'], &$year_as_int);
$stmt = sqlsrv_query(
$db,
'INSERT INTO quotes (quote, author, year) VALUES (?, ?, ?)',
$data);
sqlsrv_execute($stmt);
echo 'Quote saved.';
sqlsrv_close($db);
} else {
echo 'Connection failed.';
}
?>
Retrieving Data from MSSQL with Prepared Statements (sqlsrv_prepare.php; excerpt)
Because there is no designated escaping functions in ext/sqlsrv, prepared statements are the only viable way to use user-supplied data in SQL statements and to avoid SQL injection. As usual, two steps must be taken. First, you create a statement object (the function to be used here is sqlsrv_prepare()) with placeholders, and then you execute this statement. While preparing the statement, you also have to provide the data for the placeholders.
The ext/sqlsrv extensions expects a question mark (?) for each placeholder. The values for those placeholders are provided in an array, in the exact order as the placeholders in the SQL statement. These values can be passed by reference.
Using MSSQL without Windows
$db = @mssql_connect();
$result = mssql_query();
mssql_fetch_assoc($result);
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
if ($db = @mssql_connect('localhost', 'user', 'password')) {
mssql_select_db('phrasebook', $db);
$result = mssql_query('SELECT * FROM quotes', $db);
while ($row = mssql_fetch_assoc($result)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row['id']),
htmlspecialchars($row['quote']),
htmlspecialchars($row['author']),
htmlspecialchars($row['year'])
);
}
mssql_close($db);
} else {
echo '<tr><td colspan="4">Connection failed.</td></tr>';
}
?>
</table>
Retrieving Data from MSSQL with ext/mssql (mssql_fetch.php; excerpt)
There is one remaining reason to use the deprecated ext/mssql extension: In a heterogeneous network, UNIX/Linux systems can access MSSQL installations and connect to them using the old MSSQL extension. For this to work, you have to download the FreeTDS library from www.freetds.org/ and install it after unpacking the distribution with this command:
./configure --prefix=/usr/local/tds --with-tdsver=4.2
make
sudo make install
Then, reconfigure PHP with the switch --with-sybase=/usr/local/freetds.
Finally, you can connect to the server using mssql_connect() and select the database to be used using mssql_select_db(), similar to the API used by other database extensions. After executing a SQL query with mssql_query(), you can retrieve return data with one of the following functions:
• mssql_fetch_assoc() returns the current row in the resultset as an associative array.
• mssql_fetch_object() returns the current row in the resultset as an object.
• mssql_fetch_row() returns the current row in the resultset as a numeric array.
MSSQL supports two modes to authenticate users: SQL authentication and Windows authentication. The latter checks whether the current Windows user has sufficient rights to access the database. In a controlled environment, this might be a better idea than using username and password. However, you first have to find out which user is used. For instance, Microsoft Internet Information Services (IIS) Web server software normally uses the Internet guest account (that is, IUSR_<machinename>). Therefore, this user requires privileges in the database.
To use Windows authentication—sometimes also called trusted connection—you need the following php.ini directive:
mssql.secure_connection = On
Connecting to Firebird
ibase_connect()
<?php
if ($db = ibase_connect('localhost:/tmp/quotes.gdb', 'user', 'password')) {
echo 'Connected to the database.';
ibase_close($db);
} else {
echo 'Connection failed.';
}
?>
Connecting to InterBase/Firebird (ibase_connect.php)
The Firebird database is currently more an insider’s tip than a widely in use database, but it is getting more users and may be an alternative to an established RDBMS. The origins of this database lie in Borland’s InterBase product. Therefore, the extension is called ibase or interbase. So, Windows users need extension=php_interbase.dll in their php.ini, whereas “self-compilers” must configure PHP with the switch --with-interbase=/path/to/firebird. Then, Firebird supports two modes: a file mode comparable to SQLite and a server mode. For the sake of interoper-ability and for an easy deployment, this section uses the file mode.
This section also uses .gdb files that are compatible with both Firebird and InterBase; the new Firebird format has the extension .fdb. After this file is created, ibase_connect() connects to the file or database. For the host, you have to provide a string in the format 'localhost:/path/to/file.gdb' when using TCP/IP or the local filename (the listings assume that the file resides in /tmp on the local machine); you also need a username and a password.
Sending SQL to Firebird
ibase_execute()
<?php
if ($db = ibase_connect('localhost:/tmp/quotes.gdb', 'user', 'password')) {
require_once 'stripFormSlashes.inc.php';
$sql = 'INSERT INTO quotes (id, quote, author, qyear) ' .
'VALUES (GEN_ID(quotes_gen, 1), ?, ?, ?)';
$stmt = ibase_prepare($db, $sql);
ibase_execute($stmt,
$_POST['quote'], $_POST['author'], intval($_POST['year']));
echo 'Quote saved.';
ibase_close($db);
} else {
echo 'Connection failed.';
}
?>
Sending SQL to InterBase/Firebird (ibase_execute.php; excerpt)
The function ibase_query() can be used to send a SQL string to the database. However, there is no ibase_escape_string(); so, to be safe from SQL injection, a prepared statement must be used. Here, the function ibase_prepare() comes into play: It parses a SQL statement (with question marks as placeholders) and returns a statement object. Then, ibase_execute() executes this statement and retrieves the values for the placeholders as additional parameters.
The preceding code contains two specialities of Firebird. First, the identity column is driven by a generator in the database; the call to GEN_ID(quotes_gen, 1) enters the next available value in this column when inserting a new field. Also, the word year is reserved within Firebird, so the column’s name is qyear.
Retrieving Results of a Query to Firebird
$result = ibase_query();
ibase fetch object($result);
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
if ($db = ibase_connect('//CHRISTIAN2003/tmp/quotes.gdb', 'user', 'password')) {
$result = ibase_query($db, 'SELECT * FROM quotes'),
while ($row = ibase_fetch_object($result)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row->ID),
htmlspecialchars($row->QUOTE),
htmlspecialchars($row->AUTHOR),
htmlspecialchars($row->QYEAR)
);
}
ibase_close($db);
} else {
echo '<tr><td colspan="4">Connection failed.</td></tr>';
}
?>
</table>
Retrieving Data from InterBase/Firebird (ibase_fetch.php; excerpt)
No matter whether you are using ibase_query or ibase_execute(), at the end, you have a handle for the resultset, which you can iterate with ibase_fetch_assoc() (which returns an associative array) or ibase_fetch_object() (which returns an object). This code uses the latter method.
Keep in mind that Firebird and InterBase return column names in uppercase, so the object properties (and the keys in the associative arrays) are uppercase, too.
Connecting via PDO
try {
$db = new PDO('sqlite:PDOquotes.db'),
}
<?php
try {
$db = new PDO('sqlite:PDOquotes.db'),
echo 'Connected to the database.';
} catch (PDOException $ex) {
echo 'Connection failed: ' . htmlspecialchars($ex->getMessage());
}
?>
Connecting via PDO (pdo_connect.php)
A new development from some core PHP developers and one of the key features of PHP 5.1 has been PDO, short for PHP Data Objects. There are several abstraction classes in PHP, but PDO will eventually become the official one. As of PHP 5.3, PDO is part of the PHP core, so Windows users do not need to reference a specific DLL in php.ini. (In older PHP versions, extension=php_pdo.dll does the trick.)
Apart from having access to PDO itself, a driver for the database to be used must be loaded, as well. As of this writing, the following drivers are available:
• PDO_CUBRID for Cubrid
• PDO_DBLIB for Microsoft SQL Server (based on the old ext/mssql extension)
• PDO_FIREBIRD for InterBase/Firebird
• PDO_IBM for DB2
• PDO_INFORMIX for Informix
• PDO_MYSQL for MySQL 3.x–5.x
• PDO_OCI for Oracle
• PDO_ODBC for ODBC
• PDO_SQLITE for SQLite versions 2 and 3
• PDO_SQLSRV for Microsoft SQL Server (based on the new ext/sqlsrv extension)
• PDO_4D for 4D
To make this as portable and easy to deploy as possible, the following phrases use the SQLite driver; however, other drivers and database systems are just as good. Whatever system you choose, download and install the driver in your php.ini. If you are using a PHP version earlier than PHP 5.3, make sure that you load the driver after PDO—in newer PHP versions, you do not need to specifically load PDO.
PDO exposes an object-oriented approach. All you need is a suitable data source name (DSN), a user, and a password (and possibly other options). The preceding code connects to/creates a SQLite version 3 file.
Sending SQL via PDO
$stmt = $db->prepare($sql);
$stmt->execute();
<?php
try {
$db = new PDO('sqlite:PDOquotes.db'),
$sql = 'INSERT INTO quotes (quote, author, year) VALUES (:quote, :author, :year)';
$stmt = $db->prepare($sql);
$stmt->bindValue('quote', $_POST['quote']);
$stmt->bindValue('author', $_POST['author']);
$stmt->bindValue('year', intval($_POST['year']));
$stmt->execute();
echo 'Quote saved.';
} catch (PDOException $ex) {
echo 'Connection failed: ' . htmlspecialchars($ex->getMessage());
}
?>
Sending SQL via PDO (pdo_execute.php; excerpt)
To send SQL via PDO, a statement must be executed using the query() method. As always, you need a way to escape special characters. This can, once again, be done using prepared statements. First, a SQL query can be parsed using a method called prepare(), whereas placeholders start with a colon. Then, the bindValue() method binds a value to a placeholder name (bindParam() would also work, but requires a value that may be used by reference). Finally, the execute() method sends the statement to the database.
Retrieving Results of a Query via PDO
$result = $db->query();
$result->fetch(PDO_FETCH_ASSOC);
<table>
<tr><th>#</th><th>Quote</th><th>Author</th><th>Year</th></tr>
<?php
try {
$db = new PDO('sqlite:PDOquotes.db'),
$result = $db->query('SELECT * FROM quotes'),
while ($row = $result->fetch(PDO_FETCH_ASSOC)) {
printf(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
htmlspecialchars($row['id']),
htmlspecialchars($row['quote']),
htmlspecialchars($row['author']),
htmlspecialchars($row['year'])
);
}
} catch (PDOException $ex) {
echo 'Connection failed: ' . htmlspecialchars($ex->getMessage());
}
?>
</table>
Retrieving Data via PDO (pdo_fetch.php; excerpt)
Finally, reading out results from a SQL query with PDO is done using the standard approach: Send the SELECT query to the server and then use a while loop to iterate over the results. Here, the iteration is done using the fetch() method. You can provide as a parameter constants such as PDO_FETCH_ASSOC (which returns an associative array) or PDO_FETCH_OBJ (which returns an object). Alternatively, you can use the fetchAll() method and get an array of arrays so that you have all the data at once.
This code uses fetch() and PDO_FETCH_ASSOC to read out all data from the data source.