What is PHP ODBC ?

ODBC stands for open database connectivity. It is an Application Programming Interface (API) that allows you to connect to a data source (e.g. an MS Access database).

ODBC provides a data abstraction layer that is particularly useful for accessing some of Microsoft’s products — such as Access, Excel, MS SQL Server, and others — through a common interface. It’s like the PEAR DB abstraction class.

How does it works ?

ODBC works by acting as a twofold interface or connector design: First, as a programming language system to an ODBC system and second, as an ODBC system to a data storage system. Therefore, ODBC requires a programming-language-to-ODBC driver (for example, the PHP-ODBC Library) and an ODBC-to-data-storage-system driver (such as the MySQL-ODBC Library). This is in addition to the ODBC system itself, which handles the configuration of data sources and allows the ambiguity of data sources and programming languages.

In order to use it we have to first establish a ODBC connection to a database. With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.

Here is how to create a connection to a MS Excel database:

•Open the Administrative Tools icon in your Control Panel.

•Double-click on the Data Sources (ODBC) icon inside.

•Choose the System DSN tab.

•Click on Add in the System DSN tab.

•Select the Microsoft Excel Driver. Click Finish.

•In the next screen, click Select to locate the database.

•Give the database a Data Source Name (DSN).

• Click OK.

How to use it in our code ?

There are four main types of ODBC functions: functions for connection, querying, fetching data, and error reporting.

Connection functions: The odbc_connect() function is used to connect to an odbc data source.It takes four parameters: •The data source name , •username, •password and •an optional cursor name

$conn=odbc_connect('DSN name','username','password');

Query functions: The odbc_exec function is used to create a sql and execute it.

$rs=odbc_exec($conn,$sql);

Fetching functions:

The odbc_fetch_row() takes in a result set from a query and shifts an iterator pointer from one row to the next. This function is often used in conjunction with the odbc_result() function to fetch various cells:

odbc_fetch_row($resultset);

The odbc_result() takes in a $resultset and a column name string and returns the value of a cell. This is used in conjunction with the odbc_fetch_row() function to point at a particular row in the result set:

$value = odbc_result($resultset,"columnname");

The odbc_fetch_array() function is somewhat similar in that it is an iterator function used to fetch data from the result set of a query. However, this time, it returns an array that represents the row with the column name as the key and the cell as the value:

$rowarray = odbc_fetch_array($resultset);

Similar to odbc_fetch_array(), the odbc_fetch_object() instead fetches an object-oriented structure representing the row. It places column names as object properties and cell values as the property values:

$rowobject = odbc_fetch_object($resultset);

This function is useful for printing a set of results in HTML. It is a simple rendering of the results but can be useful when prototyping or debugging:

odbc_result_all($resultset);

The odbc_num_fields() function is a rather nice function that simply takes in a result set and tells you the number of rows in it:

$numberofrows = odbc_num_rows($resultset);

Closing function:

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

odbc_close($conn);

Therefore, ODBC can be a great technique for maximizing universal connectivity. It enhances efficiency and allows you to expand applications to work with new forms of data, such as the web-based linked data. It does come with its downsides, however: In order to achieve universal connectivity, you must carefully choose the way you build your SQL queries because only a subset of all available SQL commands can be used across all database management systems.

Hopefully, this blog has provided what you need for starting to work with databases through ODBC using the PHP programming language.