I can run that function in 1 SQL statement. This approach makes the
last column in the result set an integer containing the total number of
rows in the SQL select.
I would expect that the subselect "might" run for each line in the SQL,
but that the SQL Optimizer will cache it rendering the issue moot.
Those of you with large data sets please let us know your experience
relative to just running the result data set + the separate query to
count. I would love to hear how this sucker performs and scales. Also
include you version of IBM i so we all know flavor of DB2 is running!
It ran pretty zippy on my machine but I only had 5 records in the result
set...
SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, CUSTOMER_ADDRESS_LINE1,
CUSTOMER_ADDRESS_LINE2, CUSTOMER_CITY, CUSTOMER_STATE,
CUSTOMER_ZIP_CODE, (select count(*) from zenddata/customer)
as total_number_rows FROM zenddata/customer
Regards,
Mike
mike.p@xxxxxxxx Cell: (408)679-1011 Office: (815)722-3454
Zend Server for IBM i Beta avilable at
http://www.zend.com/en/products/server/zend-server-5-new-ibmi
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Larry Kleinman
Sent: Tuesday, December 15, 2009 10:45 AM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] getting number of records returned by SQL in PHP
Thanks folks - looks like it is either two queries, or put stuff into an
array
Larry Kleinman
Kleinman Associates, Inc.
212-949-6469
203-255-4100
MattLavinder@xxxxxxxxxxxxxxxxxxx
Sent by: web400-bounces@xxxxxxxxxxxx
12/15/2009 11:38 AM
Please respond to
Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
To
Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
cc
Subject
Re: [WEB400] getting number of records returned by SQL in PHP
If I need to know the number of rows ahead of time I either do a
count(*)
query first or I read all the results into an array before doing any
processing. If you all the read the data into an array you can just use
the count() function to get the number of rows. Most likely net.data is
doing something similar to that if it is able to tell you how many rows
were retrieved. As far as I know, SQL does not have a facility to tell
you
the number of rows a select will retrieve without actually counting
them.
I suppose the third option would be to return multiple result sets. I
am
not as familiar with that method, but it should work. It would still
involve two queries, but they could be done on the same request. The
performance hit would be negligible compared to two separate requests.
The
first result set would include a column with the total number of rows
and
the second would contain the actual data.
|------------>
| From: |
|------------>
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|Larry Kleinman <larry@xxxxxxxxxxxxxxxxx> |
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|------------>
| To: |
|------------>
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx> |
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|------------>
| Date: |
|------------>
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|12/15/2009 09:34 AM |
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|------------>
| Subject: |
|------------>
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|[WEB400] getting number of records returned by SQL in PHP
|
-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
Hi all - net.data has a nice feature called TOTAL_ROWS which gives the
total number of rows returned by a SQL query. Is there something
similar
in PHP?
Larry Kleinman
Kleinman Associates, Inc.
212-949-6469
203-255-4100
--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/web400..
midrange.com
