?
Solved

Counting rows returned by select statement

Posted on 2008-10-23
5
Medium Priority
?
4,286 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
[X]
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
5 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 1000 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:Cornelia Yoder
ID: 22785921
$rowcount = mysql_num_rows($result);
0
 
LVL 1

Assisted Solution

by:WANM
WANM earned 1000 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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

801 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