PHP

What is ODBC?

ODBC (Open Database Connectivity) is a standard programming interface for accessing database management systems. An application talks to the ODBC Driver Manager, which loads the driver for the particular database. With an ODBC connection you can connect to any database, on any computer in your network, as long as an ODBC driver and connection are available – Microsoft Access, Microsoft SQL Server, Oracle, MySQL/MariaDB, PostgreSQL, Excel files and many more.

Figure 37.1 – How PHP reaches a database through ODBC

A DSN (Data Source Name) is a named configuration that stores which driver to use and where the database is. The PHP program only needs the DSN name.

Enabling ODBC in XAMPP

The ODBC extension is part of PHP for Windows. If function_exists('odbc_connect') returns false, open Config → PHP (php.ini) in the XAMPP Control Panel, make sure the following line is not commented out (no ; at the beginning) and restart Apache:

extension=odbc

Creating an ODBC Connection on Windows

To an MS Access database

1. Open Control Panel → Administrative Tools (Windows 10/11: Windows Tools), or search the Start menu for "ODBC Data Sources (64-bit)".
2. Choose the System DSN tab (a system DSN is visible to all users and to the Apache service).
3. Click Add.
4. Select Microsoft Access Driver (\.mdb, \.accdb) and click Finish.
5. In the next screen click Select to locate the database file.
6. Give the database a Data Source Name (DSN), e.g. northwind.
7. Click OK.

To an Oracle database

1. Open the ODBC Data Sources administrator.
2. Choose the System DSN tab and click Add.
3. Select the Oracle ODBC driver (in older Windows versions Microsoft ODBC for Oracle) and click Finish.
4. Give the database a Data Source Name (DSN), e.g. oraphp, and the Oracle service name.
5. Click OK.

To a MySQL or MariaDB database

Install MySQL Connector/ODBC or MariaDB Connector/ODBC, then add a System DSN with the driver MariaDB ODBC … Driver (or MySQL ODBC … Driver), server localhost, user root and the database name.

The DSN used for the outputs in this chapter

The programs in this chapter were run against a DSN named northwind that points (through MariaDB Connector/ODBC and unixODBC on Linux) to a MariaDB copy of the classic Northwind customers table:

[northwind]
Description=Northwind sample database (MariaDB)
Driver=MariaDB Unicode
Server=localhost
Database=northwind

MariaDB [northwind]> SELECT CustomerID, CompanyName, ContactName, City FROM customers;
+------------+--------------------------+------------------+-------------+
| CustomerID | CompanyName              | ContactName      | City        |
+------------+--------------------------+------------------+-------------+
| ALFKI      | Alfreds Futterkiste      | Maria Anders     | Berlin      |
| ANATR      | Ana Trujillo Emparedados | Ana Trujillo     | Mexico D.F. |
| BHOPL      | Bhopal Book House        | Ramesh Gupta     | Bhopal      |
| BONAP      | Bon app                  | Laurence Lebihan | Marseille   |
| INDOR      | Indore Traders           | Sushma Jain      | Indore      |
+------------+--------------------------+------------------+-------------+
5 rows in set

Connecting to an ODBC Data Source

odbc_connect() connects to an ODBC data source. It takes four parameters: the data source name, user name, password and an optional cursor type. It returns a connection resource (or false). odbc_exec() executes an SQL statement and returns a result identifier.

The classic example connects to an MS Access DSN called northwind with no user name and no password (Access files need none), then creates and executes an SQL statement:

$conn = odbc_connect('northwind', '', '');
$sql  = "SELECT * FROM customers";
$rs   = odbc_exec($conn, $sql);

For an Oracle DSN called oraphp with the user "system" and password "Manager":

$conn = odbc_connect('oraphp', 'system', 'Manager');
$sql  = "SELECT * FROM customers";
$rs   = odbc_exec($conn, $sql);

Our northwind DSN points to MariaDB, which needs a database user, so the programs pass root and the empty XAMPP password:

<?php
$conn = odbc_connect('northwind', 'root', '');
if (!$conn) {
    exit("Connection Failed: " . odbc_errormsg());
}
echo "Connected to the ODBC data source 'northwind'.<br>";
$rs = odbc_exec($conn, "SELECT COUNT(*) AS total FROM customers");
odbc_fetch_row($rs);
echo "Number of customers: " . odbc_result($rs, "total");
odbc_close($conn);
?>

Connected to the ODBC data source 'northwind'.
Number of customers: 5

Retrieving Records

odbc_fetch_row() moves to the next record of the result set. It returns true if it was able to return a row, otherwise false. It takes two parameters: the ODBC result identifier and an optional row number.

odbc_fetch_row($rs)

Retrieving fields from a record

odbc_result() reads a field from the current record. It takes two parameters: the ODBC result identifier and a field number or name. The line below returns the value of the first field:

$compname = odbc_result($rs, 1);

and this line returns the value of the field called CompanyName:

$compname = odbc_result($rs, "CompanyName");

Closing an ODBC connection

odbc_close() closes an ODBC connection:

odbc_close($conn);

An ODBC Example

The following example first creates a database connection, then a result set, and then displays the data in an HTML table:

<html>
<body style="font-family:Arial;font-size:13px">
<?php
$conn = odbc_connect('northwind', 'root', '');
if (!$conn) {
    exit("Connection Failed: " . $conn);
}
$sql = "SELECT * FROM customers";
$rs = odbc_exec($conn, $sql);
if (!$rs) {
    exit("Error in SQL");
}
echo "<table border='1' cellpadding='4' style='border-collapse:collapse'><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";
while (odbc_fetch_row($rs)) {
    $compname = odbc_result($rs, "CompanyName");
    $conname = odbc_result($rs, "ContactName");
    echo "<tr><td>$compname</td>";
    echo "<td>$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>
</body>
</html>

OUTPUT browser · http://localhost/phpbook/ch37/customers.php

More ODBC Functions

Prepared statements and fetch_array

<?php
$conn = odbc_connect('northwind', 'root', '');
$stmt = odbc_prepare($conn, "SELECT CustomerID, CompanyName, City FROM customers WHERE Country = ?");
odbc_execute($stmt, [$_GET['country'] ?? 'India']);
echo "Columns: " . odbc_num_fields($stmt) . " (";
for ($i = 1; $i <= odbc_num_fields($stmt); $i++) {
    echo odbc_field_name($stmt, $i) . ($i < odbc_num_fields($stmt) ? ", " : "");
}
echo ")<br>";
while ($row = odbc_fetch_array($stmt)) {
    echo htmlspecialchars("{$row['CustomerID']}: {$row['CompanyName']} – {$row['City']}") . "<br>";
}
odbc_close($conn);
?>

Columns: 3 (CustomerID, CompanyName, City)
BHOPL: Bhopal Book House – Bhopal
INDOR: Indore Traders – Indore

Handling ODBC errors

<?php
$conn = odbc_connect('northwind', 'root', '');
$rs = @odbc_exec($conn, "SELECT * FROM suppliers");      // table does not exist
if (!$rs) {
    echo "SQLSTATE: " . odbc_error($conn) . "<br>";
    echo "Message: " . odbc_errormsg($conn);
}
odbc_close($conn);
?>

SQLSTATE: S0002
Message: [ma-3.1.15][10.11.14-MariaDB-0ubuntu0.24.04.1]Table 'northwind.suppliers' doesn't exist