Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to get many data at once?

How to get many data at once?

From
"窦德厚(ddh)"
Date:
Hi, if I have such a table:

t_ref_id     t_data
--------------------
1             'abc'
2             '321'
1             'ddd'
2             'xyz'
9             '777'
...


I want to get data with a special t_ref_id:

SELECT t_data FROM THETABLE WHERE t_ref_id = '1';

I must use a while loop to extract the data (I'm using PHP):

$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
    $rows[] = $row;
}

And if there are many matched rows, such as many hundreds or thousands of rows, I think such a loop maybe inefficient.

How to do this in a more efficient way?

Thank you!



--
ddh

Re: How to get many data at once?

From
Bill Moran
Date:
In response to "窦德厚(ddh)" <doudehou@gmail.com>:

> Hi, if I have such a table:
>
> t_ref_id     t_data
> --------------------
> 1             'abc'
> 2             '321'
> 1             'ddd'
> 2             'xyz'
> 9             '777'
> ...
>
>
> I want to get data with a special t_ref_id:
>
> SELECT t_data FROM THETABLE WHERE t_ref_id = '1';
>
> I must use a while loop to extract the data (I'm using PHP):
>
> $rows = array();
> while (($row = pgsql_fetch_assoc($result) !== false) {
>     $rows[] = $row;
> }
>
> And if there are many matched rows, such as many hundreds or thousands of
> rows, I think such a loop maybe inefficient.

You're wrong.  It's pretty much the only way.

The only way you can improve on that is to process that row immediately
instead of copying it from $row to $rows[].

> How to do this in a more efficient way?

Use a more efficient language, such as C, instead of PHP.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Re: How to get many data at once?

From
"Chandra ASGI Tech"
Date:
Hi,
 
PHP has several functions related to postgresql - including a function to fetch all the rows of the resultset at once into an array and then have a loop that extracts one row at a time from that :
 
 
search for all functions with pg_  prefix --- they are PHP functions relating to PostgreSQL. You need not fetch rows one by one from pg, instead fetch all the result rows into a php array and then loop to read them one by one from that array.
 
Regards,
Chandra ASGI

On Thu, Aug 7, 2008 at 7:03 AM, 窦德厚(ddh) <doudehou@gmail.com> wrote:
Hi, if I have such a table:

t_ref_id     t_data
--------------------
1             'abc'
2             '321'
1             'ddd'
2             'xyz'
9             '777'
...


I want to get data with a special t_ref_id:

SELECT t_data FROM THETABLE WHERE t_ref_id = '1';

I must use a while loop to extract the data (I'm using PHP):

$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
    $rows[] = $row;
}

And if there are many matched rows, such as many hundreds or thousands of rows, I think such a loop maybe inefficient.

How to do this in a more efficient way?

Thank you!



--
ddh

Re: How to get many data at once?

From
"Dmitry Koterov"
Date:
Try to use

SELECT ARRAY(SELECT t_data FROM THETABLE WHERE t_ref_id = '1') AS v;

In PHP you may fetch all matched values as a single string and then - use explode() to split it into values (possibly with later stripslashes).
It is much faster than fetching a thousands of rows.


On Thu, Aug 7, 2008 at 3:03 PM, 窦德厚(ddh) <doudehou@gmail.com> wrote:
Hi, if I have such a table:

t_ref_id     t_data
--------------------
1             'abc'
2             '321'
1             'ddd'
2             'xyz'
9             '777'
...


I want to get data with a special t_ref_id:

SELECT t_data FROM THETABLE WHERE t_ref_id = '1';

I must use a while loop to extract the data (I'm using PHP):

$rows = array();
while (($row = pgsql_fetch_assoc($result) !== false) {
    $rows[] = $row;
}

And if there are many matched rows, such as many hundreds or thousands of rows, I think such a loop maybe inefficient.

How to do this in a more efficient way?

Thank you!



--
ddh