PHP Database ODBC

ODBC is an Application Programming Interface (API) that enables us to connect to a data source (such as an MS Access database).

Create an ODBC Connection

Through an ODBC connection, you can connect to any database on any computer in your network, as long as the ODBC connection is available.

This is how to create an ODBC connection to an MS Access database:

  1. Open in the Control PanelAdministrative Toolsicon.
  2. Double-click theData Sources (ODBC)icon.
  3. SelectSystem DSNtab.
  4. In the System DSN tab, clickSelect。
  5. SelectMicrosoft Access Driver. ClickFinish。
  6. On the next screen, clickSelectto locate the database.
  7. Give the database aData Source Name (DSN)。
  8. ClickOK。

Please note that this configuration must be done on the computer where your website is hosted. If you are running Internet Information Services (IIS) on your computer, the instructions above will take effect, but 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 has four parameters: the 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 the DSN named northwind, with no username and password. Then it creates and executes an SQL statement:

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

Fetch Records

The odbc_fetch_row() function is used to return records from a result set. If a row can be returned, the function returns true; otherwise it returns false.

This function has two parameters: the ODBC result identifier and an optional row number:

odbc_fetch_row($rs)

Fetch Fields from a Record

The odbc_result() function is used to read fields from a record. This function has two parameters: the ODBC result identifier and the field number or name.

The following line of code returns the value of the first field from the record:

$compname=odbc_result($rs,1);

The following line of code returns the value of the field named "CompanyName":

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

Close the ODBC Connection

The odbc_close() function is used to close the ODBC connection.

odbc_close($conn);

ODBC Example

The following example shows how to first create a database connection, then create a result set, and then display the data in an HTML table.

<html>
<body>

<?php
$conn=odbc_connect('northwind','','');
if (!$conn)
{
    exit("连接失败: " . $conn);
}

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

if (!$rs)
{
    exit("SQL 语句错误");
}
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>
Other Extensions