Before commencing this tutorial, i have to say that for the ODBC setup section,
I used the work
of Leo West. His page
on ODBC setup and interfacing with Ms-SQL server was most usefull to my original work project...
Until my boss changed the project specs to make the database to be MS-Access, instead of MySQL.
If you need to find out more information about SQL, go
here.
Summary of Contents
- Creating an Access database
- Setting up an ODBC Connection
- Interfacing with database
I am going to assume a *little* experience in databases. Having said that, I'm
really only hoping that you know how to create a database and insert tables
into that database.
Requirements
- Apache/Web server of some kind
- PHP 3.0 (at least)
- Ms-Access (Version 97+)
- Wordpad for editing PHP
- Windows 9x+ of some working *grin* description
Files used in this tutorial (remove .sid extension to make functional)
I have given directions on exactly how to make the above files. But just in case
my directions aren't 100% correct, you can download a working copy of the relevant
software.
Important Notes
Throughout this tutorial, i have assumed that the person who will interface with
the database will have full read and write priveleges to the database. It is best
in any circumstance to make sure you have read and write priveleges to the
database. Having said that, please, please, please try out these techniques on a
sample database located on a 'local' machine. I know first hand the trouble caused
if you screw up a company database upon which most of the company relies.
So, it's onto the tutorial.
Creating An Access Database To Use
Generally when you start up access, it gives you the option of opening an
existing database or creating a new one.
For the purpose of this tutorial, im going to assume you are going to create a
new database.
Start up Access. If Access is already running, close all other Access
databases currently open. This is just to make the tutorial a bit easier.
Now, create a new Access Database. You can call this database whatever you like.
Just make sure you remember the name and the location you save it to, as you will need
it later to create the ODBC connection. Into this database create the table 'People'.
Into people, add the fields of name and specific types as follows:
You dont need to populate the table as we will do that later on.
Setting Up The ODBC Connection
I previously mentioned Leo West and its from this page that I found out how to
create an ODBC link. To create an ODBC link, open up Control Panel and
from within control panel, open up ODBC. The icon for this looks like this:
When you open this up, you end up with a screen like this:
Select the System DSN tab. If you havent done any work with user defined DSN's,
this should be empty. Select the Add button. You will get a screen which looks
like this:
Select the Microsoft Access driver. Click on the finish button. You will
now see this screen:
In the Data Source Name text field, enter a name which you wont forget, as this
will be the way you will call your database. For the purposes of this tute, enter
'WebTute'. The description text field is for your purposes as you may want to
manage your DSN connections later.
Finally, select the Select button. From this menu, choose the Access
database you initially created.
And that's it! your ODBC connction is done! Now to interface with it.
Interfacing With Database
Ok, now we can get down to the fun part, interfacing with a database!
We first create 2 php files. One to act as a form to get data from the user and
another to actually process the command. I know there are more elegant ways of
doing this. However, I was after a way which would be the easiest to understand.
The first PHP file is as follows. It is the form into which the user enters information.
You should be able to copy and paste the code into you favourite editor. You can
call this file whatever you want. I have called it FormInput.php3.
<?php
function HTML_Head() {
echo "
<HTML><HEAD>
<TITLE>Processing Form</TITLE>
</HEAD>
<BODY BGCOLOR=\"#D5D5AB\">";
}
function HTML_Form() {
echo "
<FORM NAME = \"AccessInterface\" METHOD=post ACTION=\"DataAccess.php3\">
Please enter the details you wish to be inserted into the Access Database.<br>
First Name:<input name=\"FirstName\" TYPE=\"text\" SIZE=\"25\"><br>
Last Name:<input name=\"LastName\" TYPE=\"text\" SIZE=\"25\"><br>
Phone Number: <input name=\"PhoneNumber\" TYPE=\"text\" SIZE=\"25\"><br>
<INPUT type=\"Submit\">
</form>
";
}
function HTML_Existing() {
echo "Existing database entries";
}
function HTML_Foot() {
echo "</body></html>";
}
HTML_Head();
HTML_Form();
HTML_Existing();
HTML_Foot();
?>
The code on the prior page is pretty common place. Now we actually interface with the database.
We can do this using the inherent ODBC functions within PHP and SQL. As the above
form says, the action is going to be DataAccess.php3. So copy and paste the code
below into the file DataAccess.php3.
<?php
function HTML_Head() {
echo "
<HTML><HEAD>
<TITLE>Processing Form</TITLE>
</HEAD>
<BODY BGCOLOR=\"#D5D5AB\">";
}
function HTML_Foot() {
echo "</body></html>";
}
function Database_Entries($msg) {
echo $msg;
}
function Output_Entries() {
/*
Make the connection to the database. The syntax is
odbc_connect( 'SYSTEM_DSN' , 'USER', 'PASSWORD' );
$cnx will hold the
pconnect is used to establish a persistent database
connection to the Database until the procedure is completed.
*/
$cnx = odbc_connect( 'WebTute' , 'root', '' );
if (!$cnx) {
Error_handler( "Error in odbc_connect" , $cnx );
}
// send a simple odbc query . returns an odbc cursor
$cur= odbc_exec( $cnx, "select Index,FirstName,LastName,PhoneNumber from People" );
if (!$cur) {
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}
echo "<table border=1><tr><th>Index</th><th>First Name</th>".
"<th>Last Name</th><th>Phone Number</th></tr>\n";
$nbrow=0; //Local variable to count number of rows
// fetch the succesive result rows
while( odbc_fetch_row( $cur ) ) {
$nbrow++;
$Index= odbc_result( $cur, 1 ); // get the field "Index"
$FirstName= odbc_result( $cur, 2 ); // get the field "FirstName"
$LastName= odbc_result( $cur, 3 ); // get the field "LastName"
$PhoneNumber= odbc_result( $cur, 4 ); // get the field "PhoneNumber"
echo "<tr><td>$Index</td><td>$FirstName</td>".
"<td>$LastName</td><td>$PhoneNumber</td></tr>\n";
}
echo "<tr><td colspan=2>$nbrow entries </td></tr></table>";
// close the connection. important if persistent connection are "On"
odbc_close( $cnx);
}
function Error_Handler( $msg, $cnx ) {
echo "$msg \n";
odbc_close( $cnx);
exit();
}
function Enter_New_Entry($FirstName,$LastName,$PhoneNumber) {
/*
First, we create a connection to our ODBC source. This is done by creating
a connection. Once this is done, we are returned an ODBC connection number.
We use this number to use the ODBC functions within PHP.
*/
$cnx = odbc_connect( 'WebTute' , 'root', '' );
if (!$cnx) {
Error_handler( "Error in odbc_connect" , $cnx );
}
$SQL_Exec_String = "Insert Into People (FirstName, LastName, PhoneNumber)
Values ('$FirstName', '$LastName', '$PhoneNumber')";
$cur= odbc_exec( $cnx, $SQL_Exec_String );
if (!$cur) {
Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx );
}
odbc_close( $cnx);
}
$strOldEntries = "Previous Entries in database";
$strNewEntries = "Updated version of databse (after entries)";
HTML_Head();
Database_Entries($strOldEntries);
Output_Entries();
Enter_New_Entry($FirstName,$LastName,$PhoneNumber);
Database_Entries($strNewEntries);
Output_Entries();
HTML_Foot();
?>
Now you should be ready to go! In this example, i havent done any deletion of records.
However, as you can see, if you modify the variable SQL_Exec_String, you can delete
specific records as well.
I hope this has been of use to you.
--Sid