Solved

Counting rows returned by select statement

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

910 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

19 Experts available now in Live!

Get 1:1 Help Now