Solved

Counting rows returned by select statement

Posted on 2008-10-23
5
3,919 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
The viewer will learn how to count occurrences of each item in an array.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now