PHP  
downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | my php.net 
search for in the  
<odbc_num_fieldsodbc_pconnect>
view the version of this page
Last updated: Thu, 21 Aug 2003

odbc_num_rows

(PHP 3>= 3.0.6, PHP 4 )

odbc_num_rows -- Number of rows in a result

Description

int odbc_num_rows ( resource result_id)

odbc_num_rows() will return the number of rows in an ODBC result. This function will return -1 on error. 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.

Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.



add a note add a note User Contributed Notes
odbc_num_rows
Saran
25-Sep-2003 02:16
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
17-Sep-2003 02:36
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 -/-/-
25-Apr-2003 02:08
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_
11-Apr-2003 04:50
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
28-Mar-2003 04:55
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
17-Dec-2002 07:51
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
23-Nov-2002 11:09
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
23-Oct-2002 10:21
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
14-Oct-2002 07:21
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
23-Feb-2002 04:34
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
28-Jan-2002 10:27
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
29-Nov-2001 03:14
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
03-Oct-2001 02:47
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
18-Jul-2001 06:44
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
22-Mar-2001 11:31
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
14-Feb-2001 01:23
A better method for calculating the record count (without being forced to use objects) is:

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).

<odbc_num_fieldsodbc_pconnect>
 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by: http://php.mirrors.ilisys.com.au/
Last updated: Sat 01 Nov 2003 04:13:36 EST EST