Heya,
After much fiddling around with the odbc_num_rows() function, and trying to get odbc_fetch_array() to play nice with it, I decided to write this little function which will return a 2-dimensional array, in the format [rownumber][field].
The 'rownumber' array index is obviously the number of the row stored in the array.
The 'field' index is keyed to the field name on that row.
--Function--
<?
function fetch2DArray($res){
$i = 0;
$j = 0;
$toReturn = "";
while(odbc_fetch_row($res))
{
for ($j = 1; $j <= odbc_num_fields($res); $j++)
{
$field_name = odbc_field_name($res, $j);
$ar[$field_name] = odbc_result($res, $field_name);
}
$toReturn[$i] = $ar;
$i++;
}
return $toReturn;
}
?>
I then took this one stage further.. and wrote a whole class to deal with the odbc connection... it's a bit slap-happy, but it does work well enough.
-- Class --
<?
class odbcConnection{
var $user; //Username for the database
var $pass; //Password
var $conn_handle; //Connection handle
var $temp_fieldnames; //Tempory array used to store the fieldnames, makes parsing returned data easier.
function odbcConnection(){
$this->user = "";
$this->pass = "";
}
function connectDatabase($dsn_link,$user,$pass){
$handle = @odbc_connect($dsn_link,$user,$pass,SQL_CUR_USE_DRIVER) or die("<B>Error!</B> Couldn't Connect To Database. Error Code: ".odbc_error());
$this->conn_handle = $handle;
return true;
}
function runStoredQuery($query, $returns_results){
if($returns_results == false){
return false;
}
$toReturn = "";
$res = @odbc_exec($this->conn_handle, "exec ".$query."") or die("<B>Error!</B> Couldn't Run Stored Query. Error Code: ".odbc_error());
unset($this->temp_fieldnames);
$i = 0;
$j = 0;
while(odbc_fetch_row($res))
{
//Build tempory
for ($j = 1; $j <= odbc_num_fields($res); $j++)
{
$field_name = odbc_field_name($res, $j);
$this->temp_fieldnames[$j] = $field_name;
$this->temp_fieldnames[$j];
$ar[$field_name] = odbc_result($res, $field_name);
}
$toReturn[$i] = $ar;
$i++;
}
return $toReturn;
}
function runSQL($query,$returns_results){
$toReturn = "";
$res = @odbc_exec($this->conn_handle,$query) or die("<B>Error!</B> Couldn't Run Query. Error Code: ".odbc_error());
unset($this->temp_fieldnames);
if($returns_results == false){
return false;
}
$i = 0;
$j = 0;
while(odbc_fetch_row($res))
{
//Build tempory
for ($j = 1; $j <= odbc_num_fields($res); $j++)
{
$field_name = odbc_field_name($res, $j);
$this->temp_fieldnames[$j] = $field_name;
$ar[$field_name] = odbc_result($res, $field_name);
}
$toReturn[$i] = $ar;
$i++;
}
return $toReturn;
}
}
//And an example of how to use the class:
include("dbClass.inc"); //Where dbClass.inc is the name of the file holding the class
//Declare a new instance of the class
$dbConnection = new odbcConnection;
$dsn = "GroupWork"; //Your System DSN name to point to your database
$dbConnection->connectDatabase($dsn,"",""); //No username and password - read only access
echo"<BR><HR><B>Testing SQL</b><BR><BR>";
$query_result = $dbConnection->runSQL("SELECT * FROM Event WHERE Type = 'Sport' ORDER BY EDate ASC",true);
if(!$query_result)
{
//No Results - Your Error Code Here
}else{
//Get the results
$key = $dbConnection->temp_fieldnames;
$rows = count($query_result);
$keys = count($key);
$i = 0;
while($i < $rows){
$j = 1;
echo "Echoing Row $i:<BR>";
while($j < $keys - 1){
//$query_result[row][field];
$result = $query_result[$i][$key[$j]];
$field = $key[$j];
echo("Field <b>'".$field."'</b> : ".$result." <BR>");
$j++;
}
echo "<BR>----<BR><BR>";
$i++;
}
}
?>
Hope this was of some help. If anyone has any improvments to the class, please drop them by me.