I was working on a project that required gaining access to an ODBC source (mdb file) on a windows box via PHP. I wanted a quick and dirty way to access the data. I did a google search on php odbc browser, but I couldn't find a thing. So, I decided to write my own. This is a very basic browser, but it seems to do what I need it to do. The only thing you should have to change is the DSN name, username and password (if the user & pass are set). This works great for my MDB file and it should work okay w/ other ODBC sources. Enjoy!
<?
/*
* Program Comments:
* This is a basic...very basic ODBC browser written in PHP
*/
session_start();
// odbc connection parameters
$dsn = "dsn_name_here";
$user = ""; // username and password may or may not be needed depending on the database source
$pwd = "";
$max_saved_queries = 15;
// the saved_query session variable isn't set, go ahead and define it as an array
if (!isset($_SESSION['saved_queries']))
{
$_SESSION['saved_queries'] = array("----------");
}
// A query was requested to be executed...
// Save it to history if it's not the duplicate of a previous entry in the query history
// Also, move the currently executing query to the top of the query history list
$query = $_REQUEST['query'];
if (isset($_REQUEST['query']) && isset($_REQUEST['execute_query']) && $query != "----------" && $query != "")
{
array_unshift($_SESSION['saved_queries'], $query);
for ($i=1; $i<count($_SESSION['saved_queries'])-1; $i++)
{
$q = $_SESSION['saved_queries'][$i];
// we have a query match.
// delete the match because we've already put the query at the top with our array_unshift function above
if (strtolower($q) == strtolower($query))
{
array_splice($_SESSION['saved_queries'], $i, 1);
}
}
if (count($_SESSION['saved_queries']) > $max_saved_queries)
{
array_pop($_SESSION['saved_queries']);
}
}
// connect to the odbc dsn
$dbh = odbc_connect($dsn, $user, $pwd);
?>
<html>
<head>
<style type="text/css">
body { font-family: arial; }
.tbl { font-weight: bold; padding-top:10px; }
.columns { display:none; }
.resultClass0 { background-color:#F5F5F5; padding-left:8px; }
.resultClass1 { background-color:#E0E0E0; padding-left:8px; }
</style>
<script language="javaScript">
var tableArray = new Array();
function showHide()
{
var selectBox = document.getElementById('tables');
var selectedTable = selectBox[selectBox.selectedIndex].value;
document.getElementById('columns').innerHTML = tableArray[selectedTable];
}
function showSavedQuery()
{
var selectBox = document.getElementById('saved_queries');
var query = selectBox[selectBox.selectedIndex].value;
document.getElementById('query').value = query;
}
</script>
</head>
<body>
<b><i><big>ODBC Browser</big></i></b><br><br>
<?
$table_result = odbc_tables($dbh);
$table_name = array();
$table_type = array();
while (odbc_fetch_row($table_result))
{
$tn = odbc_result($table_result,"TABLE_NAME");
$tt = odbc_result($table_result,"TABLE_TYPE");
$table_name[] = $tn;
$table_type[] = $tt;
?>
<script language="javascript">
tableArray['<?=$tn?>'] = "";
<?
$column_result = odbc_columns($dbh, $dsn, "", $tn);
while (odbc_fetch_row($column_result))
{
?>
tableArray['<?=$tn?>'] += '<small><?=odbc_result($column_result, "COLUMN_NAME")?></small><br>';
<?
}
?>
</script>
<?
}
?>
<table border=0>
<tr>
<td valign="top">
<b>Tables</b><br>
<select name="tables" id="tables" size="8" onChange="showHide()">
<br>
<?
$table_result = odbc_tables($dbh);
foreach ($table_name as $key => $tn)
{
?>
<option value="<?=$tn?>"><?=$tn?> - <?=$table_type[$key]?></option>
<?
}
?>
</select>
</td>
<td valign="top" style="padding-left:15px;">
<b>Columns</b>
<div id="columns" style="width:500;height:140;overflow:auto;border:1px;border-style: solid;"></div>
</td>
</tr>
</table>
<hr>
<table border=0>
<tr>
<td>
<form method="post" action="mdb_table_data.php">
<table border=0 cellspacing=0 cellpadding=0>
<tr>
<td>
<b>Execute Query:</b><br>
<textarea name="query" id="query" cols="60" rows="8"><?=$query?></textarea><br>
<input type="submit" name="execute_query" value="Execute">
</td>
</tr>
</table>
</form>
</td>
<td valign="top" style="padding-left:15px;">
<b>Saved Queries:</b><br>
<select name="saved_queries" id="saved_queries" size="8" onChange="showSavedQuery()">
<?
foreach ($_SESSION['saved_queries'] as $saved_query)
{
$short_display = substr($saved_query, 0, 40)
?>
<option value="<?=$saved_query?>"><?=$short_display?></option>
<?
}
?>
</select>
</td>
</tr>
</table>
<hr>
<table border="0" cellspacing="0" cellpadding="0">
<?
// execute the query here
if (isset($_REQUEST['query']) && isset($_REQUEST['execute_query']) && $query != "----------" && $query != "")
{
$res = odbc_prepare($dbh, $query);
odbc_execute($res);
$total_num_of_rows = 0;
$row_num = 0;
$max_rows = 20;
while ($row = odbc_fetch_array($res))
{
$row_num++;
$total_num_of_rows++;
$class_num = $row_num % 2;
if ($row_num == 1)
{
?>
<tr>
<?
foreach ($row as $col_name => $value)
{
?>
<th valign="top" style="padding-top:10px;padding-left:8px;"><?=$col_name?></th>
<?
}
?>
</tr>
<?
}
if ($row_num >= $max_rows)
{
$row_num = 0;
}
?>
<tr>
<?
foreach ($row as $col_name => $value)
{
?>
<td valign="top" class="resultClass<?=$class_num?>">
<?=$value?>
</td>
<?
}
?>
</tr>
<?
}
?>
</table>
<?=$total_num_of_rows?> row(s) matched your query
<?
}
?>
</table>
</body>
</html>
