Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Counting rows returned by select statement

Posted on 2008-10-23
5
4,072 Views
Last Modified: 2013-12-13
I have a query that I am running

SELECT (FIRST_NAME || ' ' || LAST_NAME) AS NAME, ID, COMPANY_NAME
                              FROM OWNER
                              WHERE lower(COMPANY_NAME) like '%$s%'
                              order by LAST_NAME asc"

I just want to know how many rows it finds.  

I have tried oci_num_rows, but that only works if you are updating, deleteing inserting ect.  Also, if I put a count(*) in the same query, it gives me an error.

I am running apache2, php 5 with oci compiled on suse linux server 10.  

Also, I tried this

$i=0;
while (oci_fetch_array($statement_owner_list, OCI_BOTH)) {
$i++;
}
echo "row count= ".$i

and it works, however it breaks the page giving this error

Warning: oci_fetch_array() [function.oci-fetch-array]: ORA-01002: fetch out of sequence in /srv/www/htdocs/history.php on line 339

Thanks
0
Comment
Question by:revo1059
5 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 total points
ID: 22785690
for count(*) don't use order by

try this

SELECT count(*)                              FROM OWNER
                              WHERE lower(COMPANY_NAME) like '%$s%'
0
 
LVL 1

Author Comment

by:revo1059
ID: 22785722
Right, I know I can do that.  There are 6 different queries depending on how they want to search.  I don't want to have to do 12 queries just to find out how many rows oracle found
0
 
LVL 27

Expert Comment

by:yodercm
ID: 22785921
$rowcount = mysql_num_rows($result);
0
 
LVL 1

Assisted Solution

by:WANM
WANM earned 250 total points
ID: 22785976
Supposedly you just include "found_rows" in your column list... (I don't have an oracle handy to check it)

select found_rows,(FIRST_NAME || ' ' || LAST_NAME) AS NAME, ID, COMPANY_NAME
                              FROM OWNER
                              WHERE lower(COMPANY_NAME) like '%$s%'
                              order by LAST_NAME asc"

found_rows will be the same (total number of rows found) for each row returned.
If your version of oracle doesn't support found_rows, you will probably need to execute

select count(*) FROM OWNER  WHERE lower(COMPANY_NAME) like '%$s%'

to get your answer first, then get your data with the original query.

0
 
LVL 1

Author Comment

by:revo1059
ID: 22786401
I won't need to do that.  I got it to work by doing:

oci_execute ($statement_owner_list);     again.

I guess once you do oci_fetch_array, you can't do it again untill you oci_execute.  After that, all I did was

$nrows = oci_fetch_all($statement_owner_list, $results); and that gave me what I wanted
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question