|
|
|
|
|
|
| |
PHP includes functions for Open Database Connectivity (ODBC), which is useful if, for example, you want to connect to a Microsoft Access data source on Windows NT. An increasing number of PHP developers are using the ODBC functions to connect to databases; to that end, the ODBC functions are numerous and well-documented in the PHP Manual. However, you only need a few of these functions in order to make a simple connection and select some data: |
|
odbc_connect - opens a connection to an ODBC data source; requires the data source name, username and password.
|
|
odbc_prepare - readies the SQL statement for execution. |
|
odbc_execute - executes the SQL statement. |
|
|
odbc_result_all - places the results of your SQL statement into a nicely-formatted HTML table. |
|
odbc_free_result - frees the resources in use by the current connection. |
|
odbc_close - closes the current connection.
|
|
Just for argument's sake, let's pretend that you have an ODBC datasource of some sort already installed on your system, and you have a valid username and password for that datasource. Let's also assume that you've created a table within that datasource, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY. |
|
The rows in the COFFEE_INVENTORY table could be populated with data such as: |
|
French Roast,dark,18 |
|
Kenya,medium,6
|
|
Ethiopian Harrar,medium,35 |
|
Sumatra,dark,8 |
|
Columbian,light,12 |
|
Now, let's do some PHP. First, you must know the name of the datasource to which you want to connect, as well as a valid username and password. Then, start your PHP code by creating a connection variable:
|
|
<?
$connection = odbc_connect("YourDataSourceName","username","password");
|
|
|
Test that a connection was established, and if it wasn't, print an error message and exit the program:
|
|
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
|
|
|
If you've made it this far, you can issue a SQL statement and hopefully see some results! Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement: |
|
|
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";
|
|
|
Next, create a variable to hold the prepared query, within the context of the connection:
|
|
$sql_result = odbc_prepare($connection,$sql);
|
|
|
Now create a variable to hold the result of the query, executed by the odbc_execute function: |
|
odbc_execute($sql_result);
|
|
|
To format the results currently held in $sql_result, use the very handy odbc_result_all function. This function uses the result variable and HTML table options, such as "border=1".
|
|
odbc_result_all($sql_result,"border=1");
|
|
|
Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur. |
|
odbc_free_result($sql_result);
odbc_close($connection);
?>
|
|
|
The full script to perform a simple connection and data selection from an ODBC datasource could look something like this: |
|
<?php
// create connection
$connection = odbc_connect("YourDataSourceName","username","password");
// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
// create SQL statement
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";
// prepare SQL statement
$sql_result = odbc_prepare($connection,$sql);
// execute SQL statement and get results
odbc_execute($sql_result);
// format result in HTML table
odbc_result_all($sql_result,"border=1");
// free resources and close connection
odbc_free_result($sql_result);
odbc_close($connection);
?>
|
|
|
Please see the PHP Manual for additional ODBC functions, and try using your own tables and SQL statements instead of the examples above.
|
|
|
|
| Creating Auto-incrementing ID Fields with PHP and Oracle
Categories : PHP, PHP options/info, Databases, Oracle | | | PHP 101 Part 8 of 15 : Databases and Other Animals
Categories : PHP, Beginner Guides, Databases | | | Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases | | | Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation | | | Custom MySQL-functions
Categories : Databases, MySQL, PHP, PHP Functions | | | PHP 101 Part 9 of 15 : SQLite My Fire!
Categories : PHP, Beginner Guides, Databases, SQLite | | | Referer Statistics
Categories : PHP, MySQL, HTTP, Databases | | | Simple Connection to Sybase with PHP
Categories : PHP, Sybase, Databases | | | User identification using cookies in PHP and MySQL
Categories : PHP, Databases, MySQL, Cookies | | | Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL | | | Creating an IE-Only Database Driven Menu System With PHP, MySQL and DHTML
Categories : PHP, MySQL, Databases, DHTML | | | Simple Connection to Informix with PHP
Categories : PHP, Informix, Databases | | | PHP and MySQL News with Comments
Categories : PHP, Databases, MySQL | | | Apache, PHP, and PostgreSQL on RedHat Linux
Categories : Apache, PHP, Databases, PostgreSQL, Linux | | | Abstracting Oracle Connectivity with PHP and OCI8
Categories : PHP, Databases, OCI8, Oracle | |
| | | Anonymous wrote : 79
Well, I just wanna know if we also can create a dns-less
connection ?
That means that we can connect directly to a file.
In ASP this looks like this:
Set conn = Server.CreateObject("ADODB.Connection")
conStr = "DBQ=" & Server.MapPath("database.mdb")
conn.Open "DRIVER={Microsoft Access Driver
(*.mdb)};UID=username;PWD=password; " & conStr | | | Anonymous wrote : 193
To my knowledge, a DSN-less connection is an ASP
feature only. I do not believe it`s possible in PHP or in
any other server-side scripting language for that matter.
Regards, Harald | | | Anonymous wrote : 257
The only way I have been able to create a DSN-less
connection is using the COM object, but so far it has
been squirrelly (fails sometimes for reasons I cannot
find). This code is based off of code I found on PHP.net
on the function list.
//database connection
$db_connection = new COM("ADODB.Connection");
$db_dbq = ("d:\data\labfeedback.mdb");
$db_defaultdir = ("d:\data");
$db_connstr = "Driver={Microsoft Access Driver
(*.mdb)}; DBQ=".realpath
($db_dbq).";DefaultDir=".realpath($db_defaultdir);
$db_connection->open($db_connstr) or die("<h3>Could
not connect to the
Database</h3><h4>$db_connstr</h4>");
| | | Anonymous wrote : 314
I can`t run this sample code. I get CGI Error Message | |
|
|
|
