Since I searched on this error way too long, and I finally found my coding issue for odbc_fetch_row(): ... I wanted to share it.
odbc_fetch_row(): x not a valid ODBC result resource
where x represents a number.
In my case, the close statement was accidently put in an area while I was spinning through records. Once you close it, while trying to get a row, you can get this msg. thrown.
while (odbc_fetch_row($rs))
{
do something.
}
ONLY THEN
odbc_close($conn);
odbc_fetch_row
Description
bool odbc_fetch_row ( resource result_id [, int row_number] )If odbc_fetch_row() was successful (there was a row), TRUE is returned. If there are no more rows, FALSE is returned.
odbc_fetch_row() fetches a row of the data that was returned by odbc_do() / odbc_exec(). After odbc_fetch_row() is called, the fields of that row can be accessed with odbc_result().
If row_number is not specified, odbc_fetch_row() will try to fetch the next row in the result set. Calls to odbc_fetch_row() with and without row_number can be mixed.
To step through the result more than once, you can call odbc_fetch_row() with row_number 1, and then continue doing odbc_fetch_row() without row_number to review the result. If a driver doesn't support fetching rows by number, the row_number parameter is ignored.
add a note
User Contributed Notes
jeffreyjarnot at comcast dot net
14-Sep-2006 08:34
14-Sep-2006 08:34
michael dot buergi at gmx dot net
05-Apr-2006 08:11
05-Apr-2006 08:11
Thanks for the binary search code, romanziak. However, it does not work correct in all cases. I corrected your version and added some code to complete the custom num_rows() function:
<?php
// note: there should be no calls to odbc_fetch_* after odbc_exec* and
// prior to a call to this function. it would affect the results.
//
// for this reason, you should cache the output of odbc_num_rows()
// and of this function.
//
// original code was tested with php 5.1.2 on iis and mssql server 8.0
//
function num_rows(&$rid) {
// try to use odbc_num_rows
$num= odbc_num_rows($rid);
if ($num >= 0) {
return $num;
}
// optimisation for frequent cases
if (!@odbc_fetch_row($rid, 1)) {
@odbc_fetch_row($rid, 0); // reset cursor
return 0;
}
if (!@odbc_fetch_row($rid, 2)) {
@odbc_fetch_row($rid, 0); // reset cursor
return 1;
}
// use binary search for counting rows for the driver
// won't give us this info...
$lo= 2;
$hi= 8192000; // max number of rows that can be in the result + 1
while ($lo < ($hi - 1)) {
$mid= (int)(($hi + $lo) / 2);
if (@odbc_fetch_row($rid, $mid)) {
$lo= $mid;
} else {
$hi= $mid;
}
}
$num= $lo;
@odbc_fetch_row($rid, 0); // reset cursor
return $num;
}
?>
romanziak at yahoo dot ca
22-Mar-2006 09:50
22-Mar-2006 09:50
I see many here try to iterate to emulate tis missing functionality. I benchmarked the performance on my PentiumM 1.6G on Excel table with 1100 rows and 2 field records and got 82ms time to retrieve number of rows using iteration through all of them, but only 7ms to execute the SQL statement. In this case of thousadn(s) of rows, it is probably faster to have the database calculate rows.
Here is different algorithm for calculating the number of rows, which does not iterate through all of them, just uses interval splitting algorithm, and number of iterations is log2(n), where n is total number of rows in the result. The below algorithm in the case above (Excel table, 1100 rows 2 fields) took 10ms to calculate:
for($lo=0,$hi=1000000; $lo<$hi; )
{
$num = (int)(($hi+$lo) / 2);
if(odbc_fetch_row($this->Result, $num))
$lo = $num + 1;
else
$hi = $num - 1;
}
ricmailinglists at yahoo dot com dot br
10-Jan-2006 04:17
10-Jan-2006 04:17
To count all the row on a table
<?php
$connection= odbc_connect("SolidBase", "user", "");
$query = "SELECT Codigo FROM Produto";
$queryexe = odbc_do($connection, $query);
$i = 0;
while(odbc_fetch_row($queryexe)) $i++;
echo $i;
?>
eolscr at gmail dot com
07-Apr-2005 11:05
07-Apr-2005 11:05
When I migrates from 4 to 5 took me a long day to find the solution.
The way to use it without problems
In php4:
WHILE (odbc_fetch_row($stringsql)) {
...
}
In php5:
<?php
odbc_fetch_row($stringsql, 0);
WHILE (odbc_fetch_row($stringsql)) {
...
}
?>
Good luck
RRRRyan
24-Nov-2004 09:00
24-Nov-2004 09:00
It appears in PHP 5 that odbc_fetch_row($rs,0) no longer acts the same way. Now that I moved to PHP 5 moving to row 0 no longer resets the record set rather it fetches the first row. :-( This broke a lot of things for me. So watch out for a missing first row on record sets reset in this fashion.
alex at bartl dot net
30-Nov-2002 01:22
30-Nov-2002 01:22
/*
I found the combination of the following lines very useful, as the
fieldnames will be simply available as variables by their names.
$query="select fieldname from table";
...
$line=mysql_fetch_assoc($query_result);
extract($line);
echo $fieldname;
However, I was missing the function odbc_fetch_assoc() so I created it myself to use in in the same way like mysql_fetch_assoc(). I am sure it could be coded in a better way, but at least it works. As a side effect $odbc_affected_rows should contain the amount of rows found, but have a look at odbc_num_rows() as well, as in my case it does not contain the expected information.
array obdc_fetch_assoc(resource result)
*/
function odbc_fetch_assoc($rs){
if (odbc_fetch_row($rs)){
$line=array("odbc_affected_rows"=>odbc_num_rows($rs));
for($f=1;$f<=odbc_num_fields($rs);$f++){
$fn=odbc_field_name($rs,$f);
$fct=odbc_result($rs,$fn);
$newline=array($fn => $fct);
$line=array_merge($line,$newline);
//echo $f.": ".$fn."=".$fct."<br>";
}
return $line;
}
else{
return false;
}
}
scott at abcoa dot com
21-Sep-2002 03:42
21-Sep-2002 03:42
The odbc_fetch_row() function worked fine when I used PHP version 4.0.6 but when I upgraded PHP to 4.2.2, the odbc_fetch_row() doesn't work when there is 1 row.
When I use the echo command to see the response to the odbc_fetch_row() before the while loop, it showed that it doesn't return True or anything when there is 1 row.
I made the workaround to the problem by including the "$bug_workaround" into the script, this is to be use as 'row number' as shown in the PHP manual at "http://www.php.net/manual/en/function.odbc-fetch-row.php".
I enclosed two clipping to point this out. The 1st clipping is the one that don't work and the 2nd clipping showed the work-around to it. In this script, you'll find the word, 'INQUIRIES', it is a table that contain number of companies and users. What the script does is to display each user whenever the company is selected. The cool thing about it is it won't display the data if there is no row for any user.
--clip--
$cid = odbc_connect('blah blah blah');
$ask7 = "SELECT * FROM INQUIRIES WHERE USER_ID = '38SCK3'";
$R7 = odbc_exec($cid,$ask7);
$result = odbc_result($R7,1);
echo "Result or No Result??? --> ".odbc_fetch_row($R7);
while (odbc_fetch_row($R7))
{
odbc_fetch_into($R7,$inquiry,$inq_c);
echo $inquiry[0], $inquiry[1];
}
--clip--
--clip--
$cid = odbc_connect('blah blah blah');
$ask7 = "SELECT * FROM INQUIRIES WHERE USER_ID = '38SCK3'";
$R7 = odbc_exec($cid,$ask7);
$result = odbc_result($R7,1);
echo "Result or No Result??? --> ".odbc_fetch_row($R7);
$bug_workaround=0;
while (odbc_fetch_row($R7,++$bug_workaround))
{
odbc_fetch_into($R7,$inquiry,$inq_c);
echo $inquiry[0], $inquiry[1];
}
--clip--
I post this into bugs.php.net as well. See bug #19528
andrew at majiclab dot ab dot ca
11-Jan-2002 07:26
11-Jan-2002 07:26
I made a slight modification to mchelen2@hknet.com's script. I was running into problems with rows that had either combination of ' or ". The add slashes will correct this problem.
class fetch_object
{
function fetch( $re )
{
if( !$re )
return null;
$no_f = odbc_num_fields( $re );
if( odbc_fetch_row( $re ) )
{
for( $i = 1; $i <= $no_f; $i++ )
{
$fn = odbc_field_name( $re, $i );
$f_re = odbc_result( $re, $i );
$ff = "\$this->$fn = \"" . addslashes( $f_re ) . "\";";
eval( $ff );
}
}
else
return null;
}
}
function odbc_fetch_object( $result )
{
$good = new fetch_object;
$good->fetch( $result );
return $good;
}
Hope that helps
-Andrew
duncanmadcow at hotmail dot com
28-Aug-2001 04:19
28-Aug-2001 04:19
it seens that when u do a odbc_fetch_row (result_id ,0)
u reset to the first row of an odbc result just like a move first in ASP
"<%rs.movefirst%>"
it is quite handy when u have to loop thru a result many times
mchelen2 at hknet dot com
20-May-2001 04:54
20-May-2001 04:54
I used to PHP's mysql functions. Now there is a project that I need to use PHP with IBM DB2. So, I have to use PHP's ODBC functions. However, the ODBC functions are different from Mysql functions. And, missing the "mysql_fetch_object()" function. I used to the "mysql_fetch_object()". So, I want to have "odbc_fetch_object()" too.
So, I come up with the following function.
class fetch_object{
function fetch($re){
$no_r = odbc_num_rows($re);
$no_f = odbc_num_fields ($re);
if (odbc_fetch_row($re)){
for ($i=1;$i<=$no_f;$i++){
$fn = odbc_field_name ($re, $i);
$f_re = odbc_result($re, $i);
$ff = "\$this->".$fn."='".$f_re."';";
eval($ff);
}
}
}
}
function odbc_fetch_object($result){
$good = new fetch_object;
$good->fetch($result);
return $good;
}
You can use this function 100% the same way as 'mysql_fetch_object()'. Like:
$row = odbc_fetch_object($result);
david dot zepp at alltel dot com
02-Mar-2000 01:49
02-Mar-2000 01:49
Here is code snippet to do dynamically build a table so you do not have to hardcode the fields that you wish to display. You could use the the odbc_result_all but this allows special handling of rows/columns. This is similar to some ASP code I use.
$Fields = odbc_num_fields($cur);
print "<table border='1' width='100%'><tr>";
// Build Column Headers
for ($i=1; $i <= $Fields; $i++){
printf("<th bgcolor='silver'>%s</th>", odbc_field_name( $cur,$i));
}
// Table Body
$Outer=0;
while( odbc_fetch_row( $cur )){
$Outer++;
print "<tr>";
for($i=1; $i <= $Fields; $i++){
printf("<td>%s</td>", odbc_result( $cur, $i ));
}
print "</tr>";
}
print "</table>";
print "<b> Your request returned $Outer rows!</b>";
odbc_close( $cnx);
zhilin at 188 dot net
19-Dec-1999 05:32
19-Dec-1999 05:32
see following i.e:
$cx=odbc_connect('ww');
$cur=odbc_exec($cx,"select id,pass from table_name");
while(odbc_fetch_row($cur)){
$id=odbc_result($cur,1);
$pass=odbc_result($cur,2);
echo "id:$id,pass:$pass <br>";
}