The easy way to count the rows in an odbc resultset where the driver returns -1 is to let SQL do the work:
<?php
$conn = odbc_connect("dsn", "", "");
$rs = odbc_exec($conn, "SELECT Count(*) AS counter FROM tablename WHERE fieldname='" . $value . "'");
$arr = odbc_fetch_array($rs);
echo $arr['counter'];
?>
odbc_num_rows
(PHP 4, PHP 5, PHP 7, PHP 8)
odbc_num_rows — Number of rows in a result
Description
Gets the number of rows in a result. For INSERT, UPDATE and DELETE
statements odbc_num_rows() returns the number of rows
affected. For a SELECT clause this can be the number
of rows available.
Return Values
Returns the number of rows in an ODBC result. This function will return -1 on error.
Notes
Note:
Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.
add a note
User Contributed Notes 17 notes
walt at brookhouse dot co dot uk ¶
14 years ago
chew_baka at hotmail dot com ¶
14 years ago
None of these examples were working for me, so I came up with the following silly procedure that gives me the number of rows. This example is crude, but you should get the idea.
<?php
$cxn = odbc_connect("ODBC_DSN_NAME", "", "");
$sql = "SELECT * FROM some_table'";
$res = odbc_exec($cxn, $sql);
$items = 0;
while ($row = odbc_fetch_array($res))
{
$items++;
}
odbc_free_result($res);
echo "<br>total No. of rows: $items";
?>
pjavilla at gmail dot com ¶
12 years ago
When accessing a DB2 database with PHP via the ODBC functions, beware of statements which include references to IBM's LONGDESCRIPTION table (for example, if you were - like myself - digging through IBM's Maximo product). Reading from that table usually requires error suppression, because although it works ODBC will spit out a warning message onscreen.
When you make it part of another query however, ODBC_NUM_ROWS will always return -1. The solution is to keep any queries to LONGDESCRIPTION in a separate standalone query by itself.
However, if you run the query though a previewer like Toad for DB2, using LONGDESCRIPTION in a larger query is fine and does show the results. It's just that you have to break the query up and segregate the query to LONGDESCRIPTION on its own if you are writing ODBC queries for PHP.
Just a quick note for anyone else who found ODBC_NUM_RESULTS normally reliable otherwise but inexplicably always returning -1 under certain circumstances.
sirio3mil at gmail dot com ¶
16 years ago
The diference between functions used here are consierable, for example for one table with 36 columns and 806 rows the time to execute two of those functions are this:
function using odbc odbc_result_all take 2,6 seconds
function using odbc_fetch_row take 0,8 seconds
areznik at survdata dot com ¶
17 years ago
I could have been noted before in this thread but I couldnt find it on my first search.
This function (odbc_num_rows) returns -1 when ODBCing to MS SQL and making it hard to get the number of rows in the returned recordset.
Two workarounds:
1. When you just need to verify that any rows returned from your query you can use select count(*) as cnt from table and then just get $row['cnt']
2. When you need to actually loop through the records this function returns number of rows in the recordset if and only if you include ORDER BY clause in your query statement.
That sounds a bit annoying but thats the work around when dealing with MS SQL odbc driver I guess.
It would be beneficial if someone explained how the Order By clause makes the difference.
j.c ¶
4 years ago
On my server odbc_num_rows() is returning a kind of weird 48bit integer (perhaps it's a 64bit with 16bit heading zeroes...).
I found out that i can obtain the correct value by masking the result with 0xffffffff
<?php
// your $result=odbc_exec(...)
$num_rows = odbc_num_rows($result) & 0xffffffff;
echo "this works for me: num rows=$num_rows\n";
?>
tom dot underwood5 at gmail dot com ¶
5 years ago
sometimes if you are for example using a stored procedure rather than an actual SELECT/INSERT/UPDATE, the odbc_num_rows can return unrealiable numbers (I was getting 3 or 0 for everything). In my case I was able to determine the number of rows with the following:
// Put the data in array
while ($data[] = odbc_fetch_array($result));
// use native php function to get its size
$num_rows = count($data);
echo $num_rows;
Hope this helps someone.
panchome66 at gmail dot com ¶
10 years ago
Este codigo se probó en SQL Server 2000, no se probó en otras versiones como 2005 o 2008. Aun asi no se aplica para MySQL, porque no existe la tabla "sysindexes":
$cnx = odbc_connect("dbSQLEmpresa","Admin","123");
if ($cnx)
{
$rs = odbc_exec($cnx, "SELECT * FROM alumnos");
$f = odbc_num_fields($rs);
$r = odbc_num_rows($rs);
$r = LFRJ_odbc_num_rows($cnx,"alumnos");
echo "<table border = '1'>";
for ($i = 1; $i <= $f; $i++)
{
$n = odbc_field_name($rs, $i);
echo "<th>", $n, "</th>";
}
while(odbc_fetch_row($rs))
{
echo "<tr>";
for ($i = 1; $i <= $f; $i++)
{
$d = odbc_result($rs, $i);
echo "<td>", $d, "</td>";
}
echo "</tr>";
}
echo "<tr><td colspan = '" . $f . "'>Campos(" . $f . ") Registros(" . $r . ")</td></tr>";
echo "</table>";
}
odbc_close($cnx);
function LFRJ_odbc_num_rows($cnx,$Tabla)
{
$rs = odbc_exec($cnx, "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('" . $Tabla . "') AND indid < 2;");
return odbc_result($rs, 1);
}
johnnyboyct-AT-yahoo.com ¶
11 years ago
function best_odbc_num_rows($r1) {
ob_start(); // block printing table with results
(int)$number=odbc_result_all($r1);
ob_clean(); // block printing table with results
return $number;
}
Above is the best way to count if you are not using something like IBM Netezza and ODBC and not doing more than 100000ish records, otherwise even this method will run out of memory.
IBM Netezza and ODBC will give you counts matching the prefetch setting in the odbc.ini file :( the default is 256 so watch out because it is accurate until that number.
pmo@raadvst-consetatDOTbe ¶
17 years ago
voland's function is simply great.
However, i would recommend the use of ob_end_clean(), to shut down completely the output buffer (can cause weird behaviour).
nielsvandenberge at hotm dot dot dot dot dot com ¶
17 years ago
I just tried to use the function best_odbc_num_rows($result) from voland at digitalshop dot ru, but it's not working quite well. After executing the function odbc_result_all(); the resultset has to be resetted again.
Resetting it with
odbc_fetch_row($result, 0);
is not working for me.
I think the internal number-value of the odbc_result_all()-function is not resetted, but that's just a guess.
when I execute the function 3 times with a resultset of 17 rows the values: 17, 34 and 51 are returned.
His previous function useful_odbc_num_rows($result) works better (for me).
voland at digitalshop dot ru ¶
17 years ago
Today we find a BEST way to count number of rows with ODBC!
function best_odbc_num_rows($r1) {
ob_start(); // block printing table with results
(int)$number=odbc_result_all($r1);
ob_clean(); // block printing table with results
return $number;
}
dm at personalcomputingsolutions dot co dot uk ¶
17 years ago
function db_get_row($cur, $rownum){
if (odbc_fetch_into($cur, $row, $rownum)){
return ($row);
}else{
return (FALSE);
}
$i=1;
if (db_get_row($cur,1)){
while ($record=db_get_row($cur,$i++)){
do stuff
}else{
tell the user there are no results
}
jeff at script-xs dot com ¶
15 years ago
After minutes of frustration, I realized why odbc_num_rows was not returning the number of affected rows on a prepared update query. I'm using ODBC to connect to Microsot SQL Server 2005.
My corrected code:
<?php
$query = odbc_prepare($conn, 'UPDATE table SET cat = ? WHERE id = 1');
$result = odbc_execute($query, $category);
$affected = odbc_num_rows($query);
?>
This code works. I was frustrated that odbc_num_rows($result) didn't work as I expected, but instead required me to pass the original prepared query to this function.
Gerd Christian Kunze ¶
11 years ago
odbc_num_rows does return -1 when it shouldn't.
i used this code:
<?php
if( odbc_num_rows( $Result ) ) {
while( false !== ( $Row = @odbc_fetch_array( $Result ) ) ) {
// do something with $Row
}
}
else {
return false;
}
?>
and it didn't work... obviously
but this while loop will skip an empty result set anyway, so i use this:
<?php
while( false !== ( $Row = @odbc_fetch_array( $Result ) ) ) {
// do something with $Row
}
if( ! odbc_num_rows( $Result ) ) {
return false;
}
?>
because after processing the $Result with fetch, odbc_num_rows reports the correct count (false|0..n) ... magic :-)
Nathaniel at comtel dot com dot au ¶
17 years ago
My development computer is running XP sql2005 while the production copy sits on a server 2003R2 sql2000 computer.
In the course of trying to get this function to work (switching from mssql to odbc) I have discovered that the ODBC driver versions are different between the two OS and that while the newer version (release date 17/2/07) that is able to be installed on 2003 handles this function fine, the older version doesn't.
Microsoft sites suggest that Vista might also handle it (ie have the newer driver). It also says that there are no plans to release the newer driver in a installable package.
http://support.microsoft.com/kb/892854
Will hopefully test with the sql2005 on server 2003R2 in the near future to confirm it is the driver helping here.
voland at digitalshop dot ru ¶
17 years ago
After a hour for a searching a good alter function of odbc_num_rows... i try to write it by mysels:
function useful_odbc_num_rows($result){
$num_rows=0;
while($temp = odbc_fetch_into($result, &$counter))
{
$num_rows++;
}
@odbc_fetch_row($result, 0); // reset cursor
return $num_rows;
}
