(PHP 4, PHP 5)
odbc_num_rows — Number of rows in a result
$result_id
)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.
Returns the number of rows in an ODBC result. This function will return -1 on error.
Note:
Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.
johnnyboyct-AT-yahoo.com (2013-06-19 17:35:48)
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.
Gerd Christian Kunze (2013-01-23 12:34:37)
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 :-)
linuxcrash at hotmail dot com (2013-01-17 09:25:34)
If you are using 'odbc_num_rows' function connecting to a MSSQL 2008 Server using the Microsoft PHP driver and querying a stored procedure having the SET NOCOUNT ON comand within the stored procedure code will always return "-1", as it seems that the odbc interface is relaying on the parameter reported by the "Number of rows affected" statement from the MSSQL server.
This might also apply to other drivers or database systems.
If SET NOCOUNT OFF is used or nothing stated (default setting on MSSQL) in the stored procedure code everything is working as intended.
Example PHP code:
<?php
$query = 'execute sp_getInformation()';
$resID = odbc_exec($conn, $query);
echo 'Number of rows: ' . odbc_num_rows($resID);
?>
Example Stored Procedure code:
CREATE PROCEDURE sp_getInformation
AS
SET NOCOUNT ON
SELECT * from Table
GO
pjavilla at gmail dot com (2012-10-01 04:07:30)
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.
neil at woolandcompany dot com (2011-08-21 11:50:33)
Working Example of odbc_num_rows with MSSQL 2000.
This works ...
<?php
$connstr = "Driver={SQL Server};Server=SERVERNAME;Database=DBNAME;";
$conn = odbc_connect($connstr, "usr", "pwd");
$queryresult = odbc_exec($conn, "select * from table");
if ( odbc_num_rows($queryresult) > 0 ) {
// rows to process
} else {
// no rows to process
}
?>
Vadim Istratov (2010-11-12 08:35:03)
There is a much better way to count rows in SELECT queries for MSSQL. Just use T-SQL.
Thus the function would be:
<?php
function odbc_mssql_num_rows($dbh) {
$res = odbc_exec($dbh, 'SELECT @@ROWCOUNT AS cnt');
if ( @odbc_fetch_into($res, $row)) {
$count = trim( $row[0] );
} else {
$count = 0;
}
odbc_free_result($res);
return $count;
}
?>
Where $dbh is your database connection resource (see odbc_connect()). And make sure that your SELECT statement is a last query before your odbc_mssql_num_rows() call.
walt at brookhouse dot co dot uk (2010-07-09 14:45:05)
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'];
?>
chew_baka at hotmail dot com (2010-05-18 14:44:35)
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";
?>
jeff at script-xs dot com (2009-01-02 11:34:21)
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.
sirio3mil at gmail dot com (2008-06-17 09:46:50)
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 (2007-11-30 10:04:21)
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.
Nathaniel at comtel dot com dot au (2007-10-30 20:57:10)
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.
pmo@raadvst-consetatDOTbe (2007-10-16 02:46:08)
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 (2007-09-11 05:06:24)
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 (2007-03-26 07:50:53)
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;
}
voland at digitalshop dot ru (2007-03-05 05:56:32)
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;
}
dm at personalcomputingsolutions dot co dot uk (2007-02-07 13:41:37)
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
}
lechatthierry at hotmail dot com (2006-08-10 10:29:35)
<quote>
ramon
27-Aug-2005 04:41
odbc_num_rows for access does return -1. I use count($resultset);
it seams to work.
</quote>
I taught it was a solution once but $resultset is a ressource and count() alwais return 1 since it considered it as a single object.
patx00 put_an_at_symbol_here gmail.com (2006-04-07 00:28:11)
If you simply want to loop through the results of a query if there are some results a more efficient way to do it (without looping once for the count and another time for doing whatever you want to do ..) would be this:
$result2 = odbc_exec($vConn, $vSql);
if (odbc_fetch_row($result2, 1)) { //Some results
odbc_fetch_row($result2, 0); //Go back to 1st row
while ($vField = odbc_fetch_array($result2)) {
....
}
}
I hope this helps.
michael dot buergi at gmx dot net (2006-04-05 05:14:31)
Look at the notes for odbc_fetch_row to find a custom num_rows() function which implements a binary search algorithm.
ramon (2005-08-27 01:41:53)
odbc_num_rows for access does return -1. I use count($resultset);
it seams to work.
moogleii (2005-08-19 09:21:37)
With MS SQL, I found if you add an ORDER BY to your SELECT statement, the function will return the correct number of rows.
Alain (2005-08-09 11:22:53)
I use ODBC on Windows to connect to Oracle on OpenVMS, and to execute a "select count(*)" I need to add the SQL_CUR_USE_ODBC parameter to odbc_connect.
tysonswing at hotmail dot com (2005-03-26 00:08:29)
Quick and easy way to return the number of rows:
$query = "SELECT COUNT(*) FROM <my database name>";
$result = odbc_exec($dbc, $query);
odbc_fetch_into($result, $count, 1);
where:
$dbc = connection_id
$count = the total number of rows
ashley at ashley-young dot com (2004-11-29 10:57:17)
To add to the note about using odbc_num_rows with the Tandem Driver:
As mentioned, the Tandem driver will return -1 until the last row. To use this to your advantage for formatting Tandem output, you can simply say:
while(odbc_num_rows($rs) == -1)
{
$row = odbc_fetch_object($rs);
print $row->fieldname;
}
From there, you can format Tandem output as you normally would with mssql or mysql.
paul at mNoOvSePtAoMiNcOeSlPaMnNdO dot ScPoAmM (2004-09-30 11:25:05)
Here's a quick and dirty function that *should* return the number of rows in a query even if the driver is being ornery.
<?php
function better_odbc_num_rows($con,$sql){
$result = odbc_exec($con,$sql);
$count=0;
while($temp = odbc_fetch_into($result, &$counter)){
$count++;
}
return $count;
}
?>
$con is your ODBC connection, and $sql is your SQL query. Put it in your header and you should be good to go.
Lambert Antonio [ lamy ] (2003-12-08 09:30:03)
It isnt the drivers actually that makes recordcount return -1, its because the recordcount property is a read-only property available to recordsets opened with one of two kinds of cursor types, opening it as static will return the actual value by default it is opened as forward only. FYI
Saran (2003-09-25 00:16:53)
Using odbc_num_rows() against MS SQL Server will return correct number of result if you add 'TOP' modifier in the select statement, make it too big number to make sure they will cover all possible records, it doesn't effect any performance, for example:
$query="select top 100000 * from employees where dept_id=$dept_id";
instead of just:
$query="select * from employees where dept_id=$dept_id";
The first statement will return the correct number of records while the latter will always return -1 if there are any records or 0 if no record at all.
However if you only want to examine whether there is a record, -1 is suffice.
Another solution is to use odbc_next_result() function, count for the records in first statement and follow by select statement, for example:
$query="select count(*)from employees where dept_id=$dept_id;select*from employees where dept_id=$dept_id"
After execute this multiple statements in a single execution, it will return multiple recordsets:
$result=odbc_exec($connection,$query);
# get number of records form count statement
if(odbc_fetch_row($result))$record_count=odbc_result($result,1);
# go to next result set
odbc_next_result($result);
# the code to handle next result set here...
Hope this help...
avodiez at hotmail dot com (2003-09-16 12:36:00)
I've found a little mistake while trying deejay_'s function against an ODBC SQL Server driver.
If you work with that database, subqueries must be named. So if you are trying to get record number deejay_'s way, you must use AS statement when passing SELECT count(*) query as this:
where is
$countQueryString="SELECT count(*) as result FROM (".$query.")";
might be
$countQuerySrting="SELECT count(*) as result FROM (".$query.") AS anyNameYouLike ";
As well, be carefull with ORDER BY statements in subqueries as they are not accepted unless they with TOP modifyers.
Avo
-/-/- Jag Alskar Carla och Sverige -/-/- (2003-04-24 12:08:26)
I solved the problem using two "while(odbc_fetch($result))", one for counting (on top of the table) and the other to populate the table:
--- counting: ----
$result_count = odbc_exec($connection, $sql);
while (odbc_fetch_row($result_count))
{
$count++;
}
echo "Title";
if ($count)
{
echo "Total: <b>$count</b>.";
}
else
{
echo "No Records Found.";
}
---- populating: ----
$result = odbc_exec($connection, $sql);
while (odbc_fetch_row($result))
{
(...)
}
odbc_close ($connection);
deejay_ (2003-04-10 14:50:48)
i modified the script of fergus a little bit so that you can give the whole query string to the function.
Maybe some find this one helpfull.
<?php
function odbc_record_count ($result, $odbcDbId, $query)
{
$numRecords = odbc_num_rows ($result);
if ($numRecords < 0)
{
$countQueryString = "SELECT count(*) as results FROM (".$query")";
$count = odbc_exec ($odbcDbId, $countQueryString);
$numRecords = odbc_result ($count, "results");
}
return $numRecords;
}
?>
deejay_
EC (2003-03-27 12:55:08)
This is how I got odbc_num_rows to work...I was getting "-1" and "Resource id #3" for the result until I added the variable $rows
(code)
$rows = odbc_result_all($sql_result, "border='1' bordercolor='#003636'");
odbc_num_rows($sql_result);
print "Number of rows:$rows";
odbc_free_result($sql_result);
odbc_close($connection);
?>
(code)
joef43065NOSPAM at yahoo dot NOSPAM dot com (2002-12-16 15:51:41)
This function calls SQLRowCount, which according to the ODBC Specification returns "the number of rows affected by an UPDATE, INSERT, or DELETE statement;" This is not what odbc_num_rows is supposed to return, though it works with some ODBC drivers. Search for "ODBC reference sqlrowcount" (no quotes) on Google for more information (link is too long to post).
webmaster at walia dot com (2002-11-23 07:09:59)
if you are using ODBC you will get -1 as the answer to this command most of the time. I tried this on windows (sigh) and it did not work.
If you are using ODBC with a DSN for MS ACCESS then the best method would be
CODE:
--------
$connection = odbc_connect("DSN_name","userid","password") or die("ERROR");
$sql = "SELECT COUNT(*) FROM tablename";
$sql_result = odbc_prepare($connection,$sql) or die("ERROR");
odbc_execute($sql_result) or die("ERROR");
$rc = odbc_fetch_into($sql_result, $my_array);
echo ("Total rows: " . $my_array[0]);
odbc_free_result($sql_result);
odbc_close($connection);
--------
This way you do not have to go through loops and stuff. imagine if you had over 100,000 entried (rows) and you tried to count them using a loop with incrememnting counter. It would really screw up the CPU. specially if you get 100 people doing the same thing at the same time (which you never know)
Using this method is fast and most convenient for me at least.
fergus at serif dot net (2002-10-23 08:21:01)
building on masuod_a@hotmail.com's note, above, I made this function which is similar but uses the SQL count(*) recomended in other notes. I've tested this against masuod_a's function and this is much faster for me.
function odbc_record_count($result, $conn, $db, $options) {
$numRecords=odbc_num_rows($result);
if ($numRecords<0) {
$countQueryString = "SELECT count(*) FROM $db $options";
$count = odbc_exec($conn, $countQueryString);
$numRecords = odbc_result($count, 1);
}
return $numRecords;
}
called like this: odbc_record_count($result, $conn, $dsn, "myField='myValue'");
where $result is the indicator to try first with odbc_num_rows, $conn is the result of your original odbc_connect, $dsn is the DSN used to get $conn and also works as the table name for the count(*), and $options is to add any WHERE this = 'that' SQL arguments.
Christian dot Schlager at t-online dot de (2002-10-14 05:21:13)
odbc_num_rows returns -1 with the drivers i'm using, too.
Using the keyword DISTINCT in the select-statement makes the function return the correct number of rows.
Alas, this doesnt work if i want to select text from a table, because in this case, you cant use DISTINCT.
Anyway, i hope it helps.
masuod_a at hotmail dot com (2002-02-22 12:34:39)
i wrote a function for counting odbc records , this function work for all odbc drivers :
function odbc_record_count($sql_id, $CurrRow = 1)
{
if ($NumRecords=odbc_num_rows($sql_id)<0) {
$NumRecords = 0;
odbc_fetch_row($sql_id,0);
while (odbc_fetch_row($sql_id))
{
$NumRecords++;
}
odbc_fetch_row($sql_id, $CurrRow);
}
return $NumRecords;
}
oscaralvaro at correo dot udep dot edu dot pe (2002-01-27 18:27:58)
There is another SQL stament to get the rows number:
SELECT COUNT(*) from Usuario where (UsrUsername = '$username') AND (UsrPassword = '$contrasena');
I think this is more fast.
mikeheald at streamgroup dot SPAMBGONE dot co dot uk (2001-11-28 11:14:02)
I have found the odbc functions to be unreliable with MSSQL 7. odbc_num_rows returns incorrect results, and specifying which row to return in odbc_result has no effect (this is with php4.0.6)
t3singh at uwaterloo dot ca (2001-10-02 12:47:31)
Hi, had troubles with this as well and after quite a while and after reading several posts I came up with the following:
$to_ret = 0;
while(odbc_fetch_row($result))
$to_ret++;
odbc_fetch_row($result,0);
return $to_ret;
just put it all in a function and I think it should do
nlange at NOSPAM dot usb dot com (2001-07-17 16:44:14)
Quick note on buggy odbc drivers...<BR>
For the Tandem ODBC Driver <BR> odbc_num_rows() returns -1 up until the very last row fetched <BR>[for example, while(odbc_fetch_into()){} ]...<BR><BR> So, you can at least tell which is the last row using odbc_num_rows(), which proves useful in HTML rendering situations...
ntp at panda dot bg dot unv dot gda dot pl (2001-03-22 07:31:22)
An alternative way of getting the number of rows if your driver returns -1 is to requery using the above query:
SELECT Count(*) FROM (SELECT ... original query);
I don't only know which solution is faster: to requery, or to fetch lot of rows (it depends on how does SQL engine does Count operation)
shinelight at excite dot com (2001-02-13 21:23:45)
A better method for calculating the record count (without being forced to use objects) is:
<?php
function RecordCount($sql_id, $CurrRow = 0) {
$NumRecords = 0;
odbc_fetch_row($sql_id, 0);
while (odbc_fetch_row($sql_id))
{
$NumRecords++;
}
odbc_fetch_row($sql_id, $CurrRow);
return $NumRecords;
}
?>
The only problem arises (in both this and the bit of code relying on objects) is when the driver does not support fetching a specific row number. In that case, the query will have to be run again (and cross your fingers that the data has not changed in the datasource).