Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : ODBC Functions (Unified) : odbc_fetch_array

odbc_fetch_array

Fetch a result row as an associative array (PHP 4 >= 4.0.2, PHP 5)
array odbc_fetch_array ( resource result [, int rownumber] )

Examples ( Source code ) » odbc_fetch_array

<?php

        $connection_string = 'DRIVER={SQL Server};SERVER=<servername>;DATABASE=<databasename>';
        $user = 'username';
        $pass = 'password';
        $conn = odbc_connect( $connection_string, $user, $pass );




        $sql =  "select * from MyTable";
        //this function will execute the sql satament
        $result=odbc_exec($conn, $sql);
         while($user_detail = odbc_fetch_array($result) ) {
                 echo $user_detail[CUSTOMER_ID];
              }
        
?>        

Code Examples / Notes » odbc_fetch_array

scott
Using odbc_fetch_into() is becoming tiresome when it had to be changed in php version 4.0.5, 4.0.6 and 4.2.x.  Also, using define() function no longer work well with 4.2.x, so define() is not reliable for odbc_fetch_into().  Time on the job to keep up with the changes is ill-advised.  Turned out the better solution is to use odbc_fetch_array and not have to deal with the hassle of updating the database, web pages, etc.  It is worth the time in the long run.
--clip-- (old script)
define(CUSTOMER_ID,0);
define(CUSTOMER_NAME,1);
//$rows = 1;
if (odbc_fetch_row($result))
{
//odbc_fetch_into($result,1,&$user_detail); //php 4.0.5
//odbc_fetch_into($result,$row,$user_detail); //php 4.0.6
odbc_fetch_into($result,$user_detail,1);  //php 4.2.x
  echo $user_detail[CUSTOMER_ID];
} else {
  echo "Failed!";
}
--clip--
//#########################################
--clip-- (new script)
if (odbc_fetch_row($result))
{
  while($user_detail = odbc_fetch_array($result) ) {
     echo $user_detail[CUSTOMER_ID];
  }
} else {
  echo "Failed!";
}
--clip--
This is pretty useful when we keep adding columns to the database table.  If you combine two tables and have two columns with the same column name, then you'll need to have two seperate array, like $user_detail1 and $user_detail2, etc.  Whatever you can come up with.

xhat
This is a quick and dirty way to accomplish the same thing:
<?
while(odbc_fetch_row($result)) {
$var1 = odbc_result($result, "NAMEOFFIELD1");
$var2 = odbc_result($result, "NAMEOFFIELD2");
..... //as many vars as you have fields with data to capture
$array_of_results[] = compact('var1', 'var2','var3', etc, etc)
}
?>
Just turn each returned row's data into variables then use the compact().  Turns each variable name into a key and the vars value into the array value.  Makes a wonderful 2d array that you can walk easily and still use key values to get at data.

garriedotpowersatkcldotacdotuk
This function will return all of the rows returned by a query in an array.
$resultSet['fieldNames'] contains an array of fieldnames keyed by field number
$resultSet[n], where n is the record number, contains an associative array keyed on fieldname.
- Garrie
----------------------------------------
function odbc_fetch_resultset($resID)
{
/* Return all the rows returned by a query in an array.
 
*/
  $resultSet=array();
// Assign the field names to $resultSet['fieldNames']
  $fCount = odbc_num_fields($resID);
  for ($i=1; $i<= $fCount; $i++){
     $fNames[$i] = odbc_field_name($resID, $i);
  }
  $resultSet['fieldNames']=$fNames;
// Assign the records
  for ($i=1; odbc_fetch_row($resID,$i); $i++){
     $record=array();
     for ($j = 1; $j <= $fCount; $j++){
        $fName = odbc_field_name($resID, $j);
        $record[$fName]=odbc_result($resID, $j);
     }
     $resultSet[$i]=$record;
  }
 
 
  return ($resultSet);
}

philip
This function requires one of the following to exist: Windows, DB2, or UNIXODBC.

jezndiatyahoodotcodotuk
This function is undefined in 4.1.2 (RedHat 7.3) despite the claims of the documentation.
It's just too useful a function not to have, particularly if your database is prone to change. Here's my quick and dirty version:
function odbc_fetch_array($resource, &$array)
{
   $array=array(); // Truncate the array first...
   if (! $int = odbc_fetch_into ($resource, $result_array))
       return false;
   for ($i=1; $i <= $int; $i++)
   {
       $array[odbc_field_name($resource, $i)] = $result_array[$i - 1];
   }
   return true;
}

powerstat
This function is badly implemented, because the fetch_array versions for all other databases are storing the values as associative AND numeric key.
This one only stores as associative key :(
Someone should really cleanup the complete database api design!

andrea dot galli
The next_record () function, checks if there is the odbc_fetch_array function in the installed version of php.
If it doesn't exist, it  call another function named odbc_fetch_array() made by me that it returns the same result.
while(list($Record1, $Record2) = $next_record($Query_ID))
{
  echo("$Record1 $Record2\n");
}
---
function next_record($Query_ID)
{
  if(function_exists(odbc_fetch_array))
  {
     $Record = odbc_fetch_array($Query_ID);
  }
  else
  {
     $Record = $odbc_fetch_array($Query_ID);
  }
  $Row += 1;
  $stat = is_array($Record);
  if(!$stat)
  {
     odbc_free_result($Query_ID);
     $Query_ID = 0;
  }
  return $Record;
}
function odbc_fetch_array($Query_ID, $RowNumber = -1)
{
  if($RowNumber < 0)
  {
     odbc_fetch_into($Query_ID, $rs);
  }
  else
  {
     odbc_fetch_into($Query_ID, $rs, $RowNumber);
  }
  if(isset($rs))
  {
     return $rs;
  }
  else
  {
     return false;
  }
}

martin
since this is not available at 4.1 at the moment you can try this (put it in an extra file and include it to avoid an error when the function will exist on the next php update):
if (function_exists(odbc_fetch_array))
 return;
function odbc_fetch_array($result, $rownumber=-1) {
 if (PHP_VERSION > "4.1") {
   if ($rownumber < 0) {
     odbc_fetch_into($result, &$rs);
   } else {
     odbc_fetch_into($result, &$rs, $rownumber);
   }
 } else {
   odbc_fetch_into($result, $rownumber, &$rs);
 }
 foreach ($rs as $key => $value) {
   $rs_assoc[odbc_field_name($result, $key+1)] = $value;
 }
 return $rs_assoc;
}

luke
On IBM DB2 for iSeries I have to specify a specific row number to retrieve. If I don't specify the row number, the behavior is erratic.
This seems to work:
<?php
for($i=1;$row=odbc_fetch_row($result,$i);$i++) {
 //use $row
}
?>
The row count MUST start at 1 or else the behavior seems undefined; i.e., when I start from 0 some rows might be returned two or more times or not at all.

michael dot fallise
Just a note about the odbc_fetch_array function of Martin (above).
The function actually works fine except when your resultset is empty (where $rs_assoc is not defined).
To avoid this just define $rs_assoc as an array before the foreach like this :
$rs_assoc = Array();
That should do the trick

phpnet dot odbc_fetch_array
It all seems so obvious now, but in case you're stuck on how to get the entire ODBC query into one array, instead of just one line, this will do the trick:
<?php
while(odbc_num_rows($myodbcexec)){
$myarray[] = odbc_fetch_array($myodbcexec);
}
?>
sina
london

miles
In response to Sena's post, odbc_num_rows does not always return a row count.  When executing a query on MS Access it seems to return a row count of -1.
<?php
while(odbc_num_rows($myodbcexec)){
      $myarray[] = odbc_fetch_array($myodbcexec);
}
?>
Just something to watch out for when attempting to execute this code.
Miles Phillips
e-Clipse Consulting

cenix
if you have trouble getting odbc_fetch_array() working, try:
odbc_fetch_into($result, $myarray);
instead of
$myarray = odbc_fetch_array($result);
it worked fine for me.

remy
I use this function to get my recordset in an array, and it's very easy to use.
function dbquery($sql) {
 $arr    = array();
 $conn = odbc_connect('dsn','user','pass');
 $rs     = odbc_exec($conn,$sql);
 $x      = 1;
 while (odbc_fetch_row($rs)) {
   for ($y = 1; $y <= odbc_num_fields($rs); $y++)
     $arr[$x][$y] = odbc_result($rs,$y);
   $x++;
 }
 if ($x > 1)
   return $arr;
}
usage:
$arr=dbquery("SELECT * FROM tblTable");
echo $arr[1][1] //echo's first column of the first row

russ
I really liked Ryan's example so I took it another step and added a recordset class to work with the connection class.  I made slight alterations to the original code as well.  Also note the recordset class takes advantage of php5's __get property function...
<%
class odbcRecordset {
  var $recordcount;
  var $currentrow;
  var $eof;
  var $recorddata;
  var $query;
  function odbcConnection(){
     $this->recordcount = 0;
     $this->recorddata = 0;
  }
  function SetData( $newdata, $num_records, $query ) {
     $this->recorddata = $newdata;
     $this->recordcount = $num_records;
     $this->query = $query;
     $this->currentrow = 0;
     $this->set_eof();
  }
  function set_eof() {
     $this->eof = $this->currentrow >= $this->recordcount;
  }
  function movenext()  { if ($this->currentrow < $this->recordcount) { $this->currentrow++; $this->set_eof(); } }
  function moveprev()  { if ($this->currentrow > 0)                  { $this->currentrow--; $this->set_eof(); } }
  function movefirst() { $this->currentrow = 0; set_eof();                                               }
  function movelast()  { $this->currentrow = $this->recordcount - 1;  set_eof();                         }
  function data($field_name) {
     if (isset($this->recorddata[$this->currentrow][$field_name])) {
        $thisVal = $this->recorddata[$this->currentrow][$field_name];
     } else if ($this->eof) {
        die("<B>Error!</B> eof of recordset was reached");
     } else {
        die("<B>Error!</B> Field <B>" . $field_name . "</B> was not found in the current recordset from query:
$this->query");
     }
     return $thisVal;
  }
  function __get($field_name) {
     return $this->data($field_name);
  }
}
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 open($dsn,$user,$pass){
     $handle = @odbc_connect($dsn,$user,$pass,SQL_CUR_USE_ODBC) or
        die("<B>Error!</B> Couldn't Connect To Database. Error Code:  ".odbc_error());
     $this->conn_handle = $handle;
     return true;
  }
 
  function &execute($query){
     //Create a temp recordset
     $newRS = new odbcRecordset;
     $thisData = "";
     $res = @odbc_exec($this->conn_handle,$query) or
        die("<B>Error!</B> Couldn't Run Query:
" . $query . "
Error Code:  ".odbc_error());
     unset($this->temp_fieldnames);
     $i = 0;
     $j = 0;
     $num_rows = 0;
     // only populate select queries
     if (stripos($query, 'select ') !== false) {
        while(odbc_fetch_row($res)) {
           $num_rows++;
 
           //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) . "";
           }
 
           $thisData[$i] = $ar;
           $i++;
        }
     }
     
     //populate the recordset and return it
     $newRS->SetData( $thisData, $num_rows, $query );
     return $newRS;
  }
}
%>
usage is pretty simple:
<%
 $con = new odbcConnection
 $con->open("dsn","user","pass")
 $sql = "select bar from foo";
 $rs = $con->execute($sql);
 if (!$rs->eof) {
   print $rs->data("bar");
     // or //
   print $rs->bar;
 }
 while (!$rs->eof) {
   // blah blah code
   $rs->movenext();
 }
%>
Works pretty well, but I haven't thoughly tested it yet.
Code can be dl'd here:
http://www.russprince.com/odbc_functions.zip
Cheers,
Russ

robincw
I am using MS SQL Server 2005, and using odbc_connect with SQL_CUR_USE_ODBC.
I had a problem with odbc_fetch_array when getting results from a stored procedure that returns a text column. The misleading error I got was:
odbc_fetch_array() [function.odbc-fetch-array]: SQL error: [Microsoft][ODBC Cursor Library] Result set was not generated by a SELECT statement, SQL state SL004 in SQLGetData
Eventually I found that by converting the text column in my database to varchar(8000), it worked fine. Perhaps using CONVERT or CAST might have worked too. I also found that varchar(max) columns were scrambled.

javiles dot lazcano
Hi. And using the same example to connect to a SYBASE ASE 12.0 Server with ODBC and Stored procedures, we have the following code:
<?PHP
function sybase_rs($SP){  
 include("INCLUDE/sdd.odbc.conect.php");
 $ARRAY = array();
 $RS = odbc_exec($conexion,$SP);
 $CONT = 1;
 while (odbc_fetch_row($RS))
        {
        for ($y = 1; $y <= odbc_num_fields($RS); $y++)
             echo odbc_result($RS,$y);
             $CONT++;
           }
if ($CONT > 1)
}
$r = sybase_rs("{CALL dbo.sdd_determina_usuario ('". $_REQUEST['usuario']."','". $_REQUEST['clave']."')}");
echo $r;  
?>

ryan rystar_x2000
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"
<HR><B>Testing SQL</b>
";
$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:
";

while($j < $keys - 1){

//$query_result[row][field];
$result = $query_result[$i][$key[$j]];
$field = $key[$j];
echo("Field <b>'".$field."'</b> : ".$result."
");

$j++;
}
echo "
----
";
$i++;
}
}
?>
Hope this was of some help. If anyone has any improvments to the class, please drop them by me.

craig
for some reason the following code seams to work but returns nothing.
[code]
$username = $clientinfo_row[6];
function dbquery($sql) {
 $arr    = array();
 $conn = odbc_connect('DB_name','','');
 $rs    = odbc_exec($conn,$sql);
 $x      = 1;
 while (odbc_fetch_row($rs)) {
  for ($y = 1; $y <= odbc_num_fields($rs); $y++)
    $arr[$x][$y] = odbc_result($rs,$y);
  $x++;
 }
 if ($x > 1)
  return $arr;
}
//usage:
$dbsel = sprintf(
"SELECT UserId FROM Users WHERE Users.UserName = '%s'", $username);
$arr=dbquery($dbsel);
echo $arr[1][1]."
";//echo's first column of the first row
echo $arr[1][2]."
";
[/code]
I believe it has something to do with sprintf line? Even tho echoing it returns the right SELECT statement.
any help would rock thanx

frosty dot z
Even with PHP 4.2.0 installed this function didn't worked for me (wasn't defined)...
Then I've tried "mysql_fetch_into" : it allowed me to get results into an array, but array keys were numbers :-/.
So I wrote my own mysql_fetch_array, returning results into an array with field names as keys ($row['login'], $row['password']... instead of $row[0], $row[1]...)
<?
  /*
  * odbc_fetch_array
  *
  * parameters :
  *   - $id_res : result id (e.g. got from an odbc_exec)
  *
  * returns :
  *   - a row of the result in an associative array with field names as keys
  *   - false if there is no more result
  */
  function odbc_fetch_array($id_res)
  {
    unset($ar);
    if (odbc_fetch_row($id_res))
    {
      for ($i = 1; $i <= odbc_num_fields($id_res); $i++)
      {
        $field_name = odbc_field_name($id_res, $i);
        $ar[$field_name] = odbc_result($id_res, $field_name);
      }
      return $ar;
    }
    else
    {
      return false;
    }
  }
?>

tim
Depending on the odbc driver/operating system you are using,  odbc_fetch_array might not be defined. (ie if you used --with-custom-odbc you may be out of luck).   The following can be safely added to define the odbc_fetch_array function if doesn't exist already.  Unlike some of the other examples contributed here, this one will create the numeric keys and the text keys.   This function is definitely not very optimal because it has to check the number of fields and field names each time a row is pulled.  Often you will be looping through a result set and there is no need to pull this information each time.  It could probably be improved by caching the result of these functions but I'll leave that as an exercise for someone else... :)
if(!function_exists("odbc_fetch_array"))
{

function odbc_fetch_array($res)
{
// make sure to pass back the false if we are out of rows
if(!odbc_fetch_row($res)) return false;

$row=array();
// populate the row array
$numfields=odbc_num_fields($res);
for($i=1; $i<=$numfields; $i++)
{
//odbc starts its indice at 1 but since I am
// trying to emulate the functionality of *_fetch_array
// for other dbs (ie mysql)  I'm going to decrement my
// my numeric indice by 1.  This might not be what
// you are after in which case get rid of the -1  
$row[odbc_field_name($res,$i)]=$row[$i-1]=odbc_result($res,$i);

}
return $row;
}


}

nauti
An easy possibility to use list() for requesting data by while-loop.
define('ODBC_DB_DSN', 'dbsys');
define('ODBC_DB_USR', '');
define('ODBC_DB_PWD', '');
function record($resId){
   $mpResult = odbc_fetch_array($resId);
   if(is_array($mpResult)) {
       return array_values($mpResult);
   }
   return false;
}
$resLink = odbc_pconnect(ODBC_DB_DSN, ODBC_DB_USR, ODBC_DB_PWD);
if(is_resource($resLink)) {
   $stQuery = "SELECT id, sort, name FROM category";
   $resId = odbc_exec($resLink, $stQuery);
   
   while(list($nID, $nSort, $stName) = record($resId)) {
       echo $nID, " ", $nSort, " ", $stName;
       echo "<br />\r\n";
   }
   if(is_resource($resId)) {
       odbc_free_result($resId);
   }
   if(is_resource($resLink)) {
       odbc_close($resLink);
   }
}

Change Language


Follow Navioo On Twitter
odbc_autocommit
odbc_binmode
odbc_close_all
odbc_close
odbc_columnprivileges
odbc_columns
odbc_commit
odbc_connect
odbc_cursor
odbc_data_source
odbc_do
odbc_error
odbc_errormsg
odbc_exec
odbc_execute
odbc_fetch_array
odbc_fetch_into
odbc_fetch_object
odbc_fetch_row
odbc_field_len
odbc_field_name
odbc_field_num
odbc_field_precision
odbc_field_scale
odbc_field_type
odbc_foreignkeys
odbc_free_result
odbc_gettypeinfo
odbc_longreadlen
odbc_next_result
odbc_num_fields
odbc_num_rows
odbc_pconnect
odbc_prepare
odbc_primarykeys
odbc_procedurecolumns
odbc_procedures
odbc_result_all
odbc_result
odbc_rollback
odbc_setoption
odbc_specialcolumns
odbc_statistics
odbc_tableprivileges
odbc_tables
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker