Conceptually, all you need to do is:
To do that, you need to know basic PHP, key PHP odbc functions, basic SQL statements, and how to create HTML pages and use forms.
Before you get started, since you are likely to make mistakes, you'll want to turn on error reporting. Otherwise, all you'll see is an empty web page if an error occurs.
<?php
ini_set('display_errors', '1');
error_reporting(E_ALL);
?>
This doesn't address all possible errors, because syntax errors will cause the entire PHP script to fail before it processes these runtime error settings. Consequently, if you get a blank web page, look over the PHP code very carefully for syntax errors.
The PHP odbc_connect() function is used to make the connection and save the connection identifier:
<?php
$db = odbc_connect("dsn", "userid", "password") or die ("could not connect<br />");
print "connected<br />"; // Remove this line later
?>
Of course, you have to substitute your own values (but keep the quotes) for "dsn", "userid" and "password".
You could create a file called test_db.php right now and insert the lines above, then test it using something like:
http://cssgate.insttech.washington.edu/~css_test/test_db.php
Either there will be an error from the PHP processor, or you should see a message from this PHP script: "could not connect", or "connected". If you could not connect, something is wrong with the dsn, userid or password, the server is down, or the network is down to the server.
Now you need to know valid SQL SELECT syntax and specifics about your table names and possibly the column names, depending on how you want to reference the results later. That is, you can reference the results by name or by position -- but we'll get back to that later. Let's say that the table name is tblDepartments.
<?php
$stmt = "Select * from tblDepartments";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die ("could not execute statement $stmt<br />");
?>
First, the variable $stmt is set to the SQL SELECT statement to later execute. You don't need to use $stmt -- you could have put the SQL SELECT statement directly in the call to odbc_exec():
$result = odbc_exec($db, "Select * from tblDepartments");
However, having the statement in a variable is convenient for displaying a meaningful error message if the statement fails to execute (the PHP statement with die in it covers that).
Note that the connection identifier, held in variable $db is passwed to odbc_exec() to reference which connection to use in case there are more than one, and the $stmt is passed as the second parameter. The results are returned in the $result variable, which is a fairly complicated variable that we'll access through more PHP odbc functions, rather than directly.
Lastly, in case there is an error in execution, the $result variable's value is checked. If it is FALSE, there was an error, which hopefully can be determined with the help of the error reporting and careful examination of the SQL statement.
A SELECT statement often returns many rows of information from the table. But it could return nothing -- which is not an error -- or it could return just one item. To determine if it didn't find anything to return, you can use:
<?php if (odbc_fetch_row($result) == FALSE) // then do something ?>
If there is something returned, the next row will be accessible using odbc_result(), supplying it with both the $result variable and a specification for the column of the row to extract -- either the column name, or the position (from 1 to the number of columns).
Let's say we want to display whatever is in the first column of each row. We have to iterate over each row until there are no more rows left, extracting the first column out of each row as we iterate:
<?php
while (odbc_fetch_row($result)) // while there are rows
{
print odbc_result($result, 1) . "<br />\n"; // Show on page, one line per row
}
?>
The <br /> tag is used to make sure each value will be formatted by the browser to display on its line, while the \n makes the line readable if you view the HTML source in a browser, prior to formatting.
Let's now assume this is an employee table (tblEmployees), which includes the columns "LastName" and "FirstName", and we want to list all of the employees by name:
<?php
while (odbc_fetch_row($result)) // while there are rows
{
print odbc_result($result, "LastName") . ", " .
odbc_result($result, "FirstName") .
"<br />\n";
}
?>
If you had wanted the names sorted, you could change the SQL SELECT statement to do the sorting for you:
$stmt = "Select * from tblEmployees ORDER BY LastName, FirstName";
However, this is not very nicely formatted, and not accessible to blind people requiring screenreaders. We need to provide more information, and an HTML <table> tag fits the bill nicely. Here is a nearly complete example, after the database is connected:
<table border="1">
<caption>Employees</caption>
<tr>
<th>Last Name
<th>First Name
</tr>
<?php
$stmt = "Select * from tblEmployees ORDER BY LastName, FirstName";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die ("could not execute statement $stmt<br />");
while (odbc_fetch_row($result)) // while there are rows
{
print "<tr>\n";
print " <td>" . odbc_result($result, "LastName") . "\n";
print " <td>" . odbc_result($result, "FirstName") . "\n";
print "</tr>\n";
}
?>
</table>
Tables can have thousands or millions of rows in them, It's a good idea to free up results as soon as you are finished with them:
<?php
odbc_free_result($result);
?>
The last bit to tidy up before leaving the PHP code, presuming the connection id is in the $db variable:
<?php
odbc_close($db);
?>
Here is a complete example of a database-driven web page displaying one table. Remember to change the dsn, userid and password, and to have a SQL Server table called tblEmployees with at least two columns, named "LastName" and "FirstName", populated with some data. Only then will this work.
<html>
<head>
<title>Displaying a SQL Server Table on the Web</title>
</head>
<body>
<table border="1">
<caption>Employees</caption>
<tr>
<th>Last Name
<th>First Name
</tr>
<?php
$db = odbc_connect("dsn", "userid", "password") or die ("could not connect<br />");
$stmt = "Select * from tblEmployees ORDER BY LastName, FirstName";
$result = odbc_exec($db, $stmt);
if ($result == FALSE) die ("could not execute statement $stmt<br />");
while (odbc_fetch_row($result)) // while there are rows
{
print "<tr>\n";
print " <td>" . odbc_result($result, "LastName") . "\n";
print " <td>" . odbc_result($result, "FirstName") . "\n";
print "</tr>\n";
}
odbc_free_result($result);
odbc_close($db);
?>
</table>
</body>
</html>