Solved

Counting rows returned by select statement

Posted on 2008-10-23
5
4,118 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

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

749 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