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 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 the method to create an ODBC connection to MS Access data:

  1. OpenControl Panel
  2. Double-click onData Source (ODBC) icon
  3. Select System DSN Tab
  4. Click “,
  5. Select Microsoft Access Driver. ClickFinish.
  6. In the next interface, click “Select”to locate the database.
  7. Give this database aData Source Name (DSN).
  8. ClickOK.

Please note that this configuration must be completed on the computer where your website is located. If your computer is running the Internet Information Server (IIS), the above instructions will take effect. However, if your website is on a remote server, you must have physical access to the server, or your hosting provider must set up the DSN for you.

Connect 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 pointer type parameter.

The odbc_exec() function is used to execute an SQL statement.

Example

The following example creates a connection to a DSN named northwind without a username and password. Then, it creates and executes an SQL statement:

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

Retrieve record

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

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

odbc_fetch_row($rs)

Retrieve field from record

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

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

$compname = odbc_result($rs, 1);

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

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

Close 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 a result set, and then 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("Error in 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>