PHP Database ODBC
ODBC is an Application Programming Interface (API) that allows us to connect to a data source, such as an MS Access database.
Creating an ODBC Connection
With an ODBC connection, you can connect to any database on any computer in your network, as long as the ODBC connection is available.
Here's how to create an ODBC connection to an MS Access database:
- Open the Administrative Tools icon in the Control Panel.
- Double-click the Data Sources (ODBC) icon.
- Select the System DSN tab.
- Click Add in the System DSN tab.
- Choose the Microsoft Access Driver. Click Finish.
- In the next interface, click Select to locate the database.
- Give the database a Data Source Name (DSN).
- Click OK.
Note that this configuration must be done on the computer where your website is hosted. If your computer is running Internet Information Services (IIS), these instructions will work. However, if your website is on a remote server, you must have physical access to that server or ask your hosting provider to set up the DSN for you.
Connecting to ODBC
The odbc_connect()
function is used to connect to an ODBC data source. This function takes four parameters: data source name, username, password, and an optional cursor type.
The odbc_exec()
function is used to execute SQL statements.
Example
The following example creates a connection to a DSN named northwind, with no username and password. It then creates and executes an SQL statement:
$conn = odbc_connect('northwind', '', '');
$sql = "SELECT * FROM customers";
$rs = odbc_exec($conn, $sql);
Retrieving Records
The odbc_fetch_row()
function is used to return records from the result set. If the function can return a row, it returns true; otherwise, it returns false.
This function takes two parameters: the ODBC result identifier and an optional row number:
odbc_fetch_row($rs)
Retrieving Fields from Records
The odbc_result()
function is used to read fields from records. This function takes two parameters: the ODBC result identifier and the field number or name.
The following line returns the value of the first field:
$compname = odbc_result($rs, 1);
The following line returns the value of the field named "CompanyName":
$compname = odbc_result($rs, "CompanyName");
Closing an ODBC Connection
The odbc_close()
function is used to close an ODBC connection.
odbc_close($conn);
ODBC Example
The following example demonstrates how to first create a database connection, then create a result set, and finally display the data in an HTML table.
<html>
<body>
<?php
$conn = odbc_connect('northwind', '', '');
if (!$conn) {
exit("Connection Failed: " . $conn);
}
$sql = "SELECT * FROM customers";
$rs = odbc_exec($conn, $sql);
if (!$rs) {
exit("SQL Statement Error");
}
echo "<table><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>