CODEWITHSUNDEEP
phpsqlodbccountsybase
TopDeveloper
TopDeveloper

Reputation: 542

How to Get The Count or The Number Of Rows In A Result Set In PHP using ODBC Connection?

While I build a web page in My PHP web application, My Connection works ok but When I want to get the count of rows of the SELECT Statement I used in my query, It gives me -1 !! although my result set has about 10 rows.

I would like to get the actual number of result set rows. I searched the PHP Manual & documentation but I do not find a direct way like a Count function or something like that.

I wonder if I have to make a Count(*) SQL Statement in another query and attach it to my Connection to get the Count of Rows ?

Does any one knows an easy and direct way to get that ?

the odbc_num_rows function always gives -1 in result so I can not get the actual number of rows.

My Programming langauge is PHP and My Database Engine is Sybase and The Way to connect to Database is ODBC.

Here are you the Code I used:-

<?PHP

//PHP Code to connect to a certain database using ODBC and getting information from it

//Determining The Database Connection Parameters
$database = 'DatabaseName';
$username = 'UserName';
$password = 'Password';

//Opening the Connection
$conn = odbc_connect($database,$username,$password);

//Checking The Connection
if (!$conn)
{
exit("Connection Failed: " . $conn);
}

//Preparing The Query
$sql = "SELECT * FROM Table1 WHERE Field1='$v_Field1'";

//Executing The Query
$rs = odbc_exec($conn,$sql);

//Checking The Result Set
if (!$rs)
{
exit("Error in SQL");
}

echo "<p align='Center'><h1>The Results</h1></p>";

while ( odbc_fetch_row($rs) )

{
  $field1 = odbc_result($rs,1);
  $field2 = odbc_result($rs,2);
  $field3 = odbc_result($rs,3);
  echo "field1 : " . $field1 ;
  echo "field2 : " . $field2 ;
  echo "field3 : " . $field3 ;
}

$RowNumber = odbc_num_rows($rs);

echo "The Number of Selected Rows = " . $RowsNumber ; 

//Closing The Connection
odbc_close($conn);

?>

Thanks for your Help :)

Upvotes: 4

Views: 29101

Answers (3)

PerformanceDBA
PerformanceDBA

Reputation: 33808

On what basis, do you expect odbc_num_rows to return anything other than -1 ?

We have the fact from the manuals, that OBDC does not support @@ROWCOUNT / odbc_num_rows. So there is no basis for expecting that it "should" return anything other than that which is documented, -1 in all circumstances.

Even if you used Sybase directly (instead of via ODBC), you would have the same "problem".

  • odbc_num_rows returns @@ROWCOUNT, which is the rows inserted/updated/deleted by the immediately preceding command. -1 is the correct, documented value, if the immediately preceding command is not insert/update/delete.

  • It has nothing to do with rows in a table.

Use another batch, and either one of the documented methods to obtain rows in a table, and load the value into a variable:

  • SELECT @Count = COUNT(*) -- slow, requires I/O
    or
  • SELECT @Count = ROW_COUNT(db_id, object_id) -- very fast, no I/O

Then interrogate the result array, to obtain the variable, not odbc_num_rows, which will continue returning -1.

Upvotes: 3

Ramin Rezazadeh
Ramin Rezazadeh

Reputation: 336

in php.net:

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'];

?>

Upvotes: 3

Pekka
Pekka

Reputation: 449713

odbc_num_rows seems to be reliable for INSERT, UPDATE, and DELETE queries only.

The manual says:

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

one way around this behaviour is to do a COUNT(*) in SQL instead. See here for an example.

Upvotes: 8

Related Questions