PHP provides support for Open DataBase Connectivity (ODBC) which makes it possible to access any ODBC compliant database management system (DBMS) as long as a Data Source Name (DSN) is available on your system or network or a DSN-less connection string is available. This includes access to relational databases such as Oracle, DB2, MS SQL Server, MySQL, and MS Access. Since PHP includes DSN-less functions for accessing MySQL databases that do not require ODBC, this section will use MS Access to demonstrate ODBC methods. The examples can also be used with other ODBC compliant DBMS.
ODBC DSN Connections
To connect to our database using ODBC we must first create a system data source name.
Here is how to create an ODBC connection to a MS Access Database:
The DSN configuration has to be setup on the computer where your web site is located. If you are running your site on a remote server, you must setup the configuration on the server.
After the ODBC Connection is establish, you can use PHP odbc functions to connect to your database and retrieve records. The PHP odbc functions are described below:
ODBC DSN-less Connections
DSN less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. Instead of using a DSN to connect to a database, the developer specifies the necessary information right in the application. With a DSN-less connection the developer is free to use connection standards other than ODBC, such as OLE DB. DSN-less connections should be used when you do not have access to the server to register DSNs yourself.
For Microsoft Access, the following connection string is used to create DSN-less connections:
Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb
odbc_connect(dsn/dsn-less connection string,username,password) - function used to connect to an ODBC datasource. The function takes four parameters: the data source name or a dsn-less connection string, username, password, and an optional cursor type. In cases where a username and password, and cursor type are not required, the parameters can be replaced with a null string - ''. The connection id returned by this functions is needed by other ODBC functions. You can have multiple connections open at once as long as they either use different db or different credentials.
odbc_exec(connection_id,SQL query_string) - function used to execute an SQL statement. The function takes two parameters: a connection object created using the odbc_connect() function and a SQL statement. Returns FALSE on error. Returns a record set if the SQL command was executed successfully.
odbc_fetch_array(recordset name) - used to retrieve records or rows from a record set as an associative array. The recordset name is created when the odbc_exec() function is called. This function returns TRUE if it is able to return rows, otherwise FALSE.
odbc_num_rows(recordset name) - returns the number of rows in an ODBC result set. The function will return -1 if an error occurs. For INSERT, UPDATE and DELETE statements odbc_num_rows() returns the number of rows affected. For a SELECT clause this can be the number of rows available. Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with MS Access drivers.
odbc_close(connection_id) - will close down the connection to the database server associated with the given connection identifier.
These functions will be demonstrates in sections 8-2 through 8-5.