pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Getting arrays from a result set?
5+ messages / 3 participants
[nested] [flat]
* Getting arrays from a result set?
@ 2000-08-28 03:15 Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:12 ` Re: Getting arrays from a result set? The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 5+ messages in thread
From: Louis Bertrand @ 2000-08-28 03:15 UTC (permalink / raw)
To: pgsql-php
Hello,
I am trying to save an array of strings in a column, but I can't figure
out how to retrieve it from within PHP3. What I'm hoping to accomplish is
to store a sequence of update SQL statements resulting from a posted form,
then output a Confirm (Yes/No) form. If the user clicks Yes, I retrieve
the SQL statements from the session table and execute them in a
transaction.
I added the column:
alter table sessions add column pending varchar(240)[];
db=> \d sessions
| pending | varchar[] | var |
I insert the strings with:
db=> update sessions set pending = '{ \'blah\', \'blorg\'}' where
sessionid = 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
UPDATE 1
Then the select:
db=> select pending from sessions where sessionid =
'f38ca9c5f28fd13bd9619b13b5d8f2fc';
{" 'blah'","'blorg'"}
(1 row)
Then I do the following PHP code:
$selectsql = "SELECT pending"
." FROM sessions WHERE"
." sessionid = '$SessionID'";
$result = @pg_Exec( $conn, $selectsql);
if( $result && ( pg_NumRows($result) > 0)) {
$transactions = pg_Result( $result, 0, "pending");
echo "<br>pending=" . $transactions;
}
else {
echo "<br>No transactions";
} }
This is what I get:
pending={" 'blah'","'blorg'"}
I suppose I can always tokenize and parse the string, but I'm hoping there
is a classier way around this. I'm afraid of using temporary tables
because I don't want them cluttering the database when users abandon their
sessions.
Using PHP3.0.16 and PostgreSQL 6.5.2 on OpenBSD (no, I haven't had time to
upgrade yet).
Any thoughts?
Thanks
--Louis <louis@bertrandtech.on.ca>
Louis Bertrand http://www.bertrandtech.on.ca/
Bertrand Technical Services, Bowmanville, ON, Canada
Tel: +1.905.623.1500 Fax: +1.905.623.3852
OpenBSD: Secure by default. http://www.openbsd.org/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting arrays from a result set?
2000-08-28 03:15 Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
@ 2000-08-28 03:12 ` The Hermit Hacker <scrappy@hub.org>
2000-08-28 03:30 ` Re: Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 04:22 ` Re: Getting arrays from a result set? Doug Semig <dougslist@semig.com>
0 siblings, 2 replies; 5+ messages in thread
From: The Hermit Hacker @ 2000-08-28 03:12 UTC (permalink / raw)
To: Louis Bertrand <louis@bertrandtech.on.ca>; +Cc: pgsql-php
why not insert multiple records instead, serialized for order?
On Sun, 27 Aug 2000, Louis Bertrand wrote:
> Hello,
>
> I am trying to save an array of strings in a column, but I can't figure
> out how to retrieve it from within PHP3. What I'm hoping to accomplish is
> to store a sequence of update SQL statements resulting from a posted form,
> then output a Confirm (Yes/No) form. If the user clicks Yes, I retrieve
> the SQL statements from the session table and execute them in a
> transaction.
>
> I added the column:
> alter table sessions add column pending varchar(240)[];
>
> db=> \d sessions
> | pending | varchar[] | var |
>
> I insert the strings with:
> db=> update sessions set pending = '{ \'blah\', \'blorg\'}' where
> sessionid = 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> UPDATE 1
>
> Then the select:
> db=> select pending from sessions where sessionid =
> 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> {" 'blah'","'blorg'"}
> (1 row)
>
> Then I do the following PHP code:
> $selectsql = "SELECT pending"
> ." FROM sessions WHERE"
> ." sessionid = '$SessionID'";
> $result = @pg_Exec( $conn, $selectsql);
> if( $result && ( pg_NumRows($result) > 0)) {
> $transactions = pg_Result( $result, 0, "pending");
> echo "<br>pending=" . $transactions;
> }
> else {
> echo "<br>No transactions";
> } }
>
> This is what I get:
> pending={" 'blah'","'blorg'"}
>
> I suppose I can always tokenize and parse the string, but I'm hoping there
> is a classier way around this. I'm afraid of using temporary tables
> because I don't want them cluttering the database when users abandon their
> sessions.
>
> Using PHP3.0.16 and PostgreSQL 6.5.2 on OpenBSD (no, I haven't had time to
> upgrade yet).
>
> Any thoughts?
>
> Thanks
> --Louis <louis@bertrandtech.on.ca>
>
> Louis Bertrand http://www.bertrandtech.on.ca/
> Bertrand Technical Services, Bowmanville, ON, Canada
> Tel: +1.905.623.1500 Fax: +1.905.623.3852
>
> OpenBSD: Secure by default. http://www.openbsd.org/
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting arrays from a result set?
2000-08-28 03:15 Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:12 ` Re: Getting arrays from a result set? The Hermit Hacker <scrappy@hub.org>
@ 2000-08-28 03:30 ` Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:35 ` Re: Getting arrays from a result set? The Hermit Hacker <scrappy@hub.org>
1 sibling, 1 reply; 5+ messages in thread
From: Louis Bertrand @ 2000-08-28 03:30 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-php
The way the session table is set up, I have one record per active
session. When the user comes back for another hit, I retrieve the session
particulars contained in the record based on the sessionID presented in
the GET URL or hidden field in the POST. The sessionID is the primary key.
I suppose I could create a separate table of pending transactions, with
a sequence to keep them in order, referencing the sessionid.
Is that along the lines of what you were suggesting?
Thanks
--Louis <louis@bertrandtech.on.ca>
On Mon, 28 Aug 2000, The Hermit Hacker wrote:
>
> why not insert multiple records instead, serialized for order?
>
> On Sun, 27 Aug 2000, Louis Bertrand wrote:
>
> > Hello,
> >
> > I am trying to save an array of strings in a column, but I can't figure
> > out how to retrieve it from within PHP3. What I'm hoping to accomplish is
> > to store a sequence of update SQL statements resulting from a posted form,
> > then output a Confirm (Yes/No) form. If the user clicks Yes, I retrieve
> > the SQL statements from the session table and execute them in a
> > transaction.
> >
> > I added the column:
> > alter table sessions add column pending varchar(240)[];
> >
> > db=> \d sessions
> > | pending | varchar[] | var |
> >
> > I insert the strings with:
> > db=> update sessions set pending = '{ \'blah\', \'blorg\'}' where
> > sessionid = 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> > UPDATE 1
> >
> > Then the select:
> > db=> select pending from sessions where sessionid =
> > 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> > {" 'blah'","'blorg'"}
> > (1 row)
> >
> > Then I do the following PHP code:
> > $selectsql = "SELECT pending"
> > ." FROM sessions WHERE"
> > ." sessionid = '$SessionID'";
> > $result = @pg_Exec( $conn, $selectsql);
> > if( $result && ( pg_NumRows($result) > 0)) {
> > $transactions = pg_Result( $result, 0, "pending");
> > echo "<br>pending=" . $transactions;
> > }
> > else {
> > echo "<br>No transactions";
> > } }
> >
> > This is what I get:
> > pending={" 'blah'","'blorg'"}
> >
> > I suppose I can always tokenize and parse the string, but I'm hoping there
> > is a classier way around this. I'm afraid of using temporary tables
> > because I don't want them cluttering the database when users abandon their
> > sessions.
> >
> > Using PHP3.0.16 and PostgreSQL 6.5.2 on OpenBSD (no, I haven't had time to
> > upgrade yet).
> >
> > Any thoughts?
> >
> > Thanks
> > --Louis <louis@bertrandtech.on.ca>
> >
> > Louis Bertrand http://www.bertrandtech.on.ca/
> > Bertrand Technical Services, Bowmanville, ON, Canada
> > Tel: +1.905.623.1500 Fax: +1.905.623.3852
> >
> > OpenBSD: Secure by default. http://www.openbsd.org/
> >
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting arrays from a result set?
2000-08-28 03:15 Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:12 ` Re: Getting arrays from a result set? The Hermit Hacker <scrappy@hub.org>
2000-08-28 03:30 ` Re: Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
@ 2000-08-28 03:35 ` The Hermit Hacker <scrappy@hub.org>
0 siblings, 0 replies; 5+ messages in thread
From: The Hermit Hacker @ 2000-08-28 03:35 UTC (permalink / raw)
To: Louis Bertrand <louis@bertrandtech.on.ca>; +Cc: pgsql-php
why not strip off the '{' and '}' from the result string and just do:
$pieces = explode (",", pg_Result( $result, 0, "pending"));
On Sun, 27 Aug 2000, Louis Bertrand wrote:
> The way the session table is set up, I have one record per active
> session. When the user comes back for another hit, I retrieve the session
> particulars contained in the record based on the sessionID presented in
> the GET URL or hidden field in the POST. The sessionID is the primary key.
>
> I suppose I could create a separate table of pending transactions, with
> a sequence to keep them in order, referencing the sessionid.
> Is that along the lines of what you were suggesting?
>
> Thanks
> --Louis <louis@bertrandtech.on.ca>
>
>
> On Mon, 28 Aug 2000, The Hermit Hacker wrote:
>
> >
> > why not insert multiple records instead, serialized for order?
> >
> > On Sun, 27 Aug 2000, Louis Bertrand wrote:
> >
> > > Hello,
> > >
> > > I am trying to save an array of strings in a column, but I can't figure
> > > out how to retrieve it from within PHP3. What I'm hoping to accomplish is
> > > to store a sequence of update SQL statements resulting from a posted form,
> > > then output a Confirm (Yes/No) form. If the user clicks Yes, I retrieve
> > > the SQL statements from the session table and execute them in a
> > > transaction.
> > >
> > > I added the column:
> > > alter table sessions add column pending varchar(240)[];
> > >
> > > db=> \d sessions
> > > | pending | varchar[] | var |
> > >
> > > I insert the strings with:
> > > db=> update sessions set pending = '{ \'blah\', \'blorg\'}' where
> > > sessionid = 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> > > UPDATE 1
> > >
> > > Then the select:
> > > db=> select pending from sessions where sessionid =
> > > 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
> > > {" 'blah'","'blorg'"}
> > > (1 row)
> > >
> > > Then I do the following PHP code:
> > > $selectsql = "SELECT pending"
> > > ." FROM sessions WHERE"
> > > ." sessionid = '$SessionID'";
> > > $result = @pg_Exec( $conn, $selectsql);
> > > if( $result && ( pg_NumRows($result) > 0)) {
> > > $transactions = pg_Result( $result, 0, "pending");
> > > echo "<br>pending=" . $transactions;
> > > }
> > > else {
> > > echo "<br>No transactions";
> > > } }
> > >
> > > This is what I get:
> > > pending={" 'blah'","'blorg'"}
> > >
> > > I suppose I can always tokenize and parse the string, but I'm hoping there
> > > is a classier way around this. I'm afraid of using temporary tables
> > > because I don't want them cluttering the database when users abandon their
> > > sessions.
> > >
> > > Using PHP3.0.16 and PostgreSQL 6.5.2 on OpenBSD (no, I haven't had time to
> > > upgrade yet).
> > >
> > > Any thoughts?
> > >
> > > Thanks
> > > --Louis <louis@bertrandtech.on.ca>
> > >
> > > Louis Bertrand http://www.bertrandtech.on.ca/
> > > Bertrand Technical Services, Bowmanville, ON, Canada
> > > Tel: +1.905.623.1500 Fax: +1.905.623.3852
> > >
> > > OpenBSD: Secure by default. http://www.openbsd.org/
> > >
> > >
> >
> > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> >
> >
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting arrays from a result set?
2000-08-28 03:15 Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:12 ` Re: Getting arrays from a result set? The Hermit Hacker <scrappy@hub.org>
@ 2000-08-28 04:22 ` Doug Semig <dougslist@semig.com>
1 sibling, 0 replies; 5+ messages in thread
From: Doug Semig @ 2000-08-28 04:22 UTC (permalink / raw)
To: pgsql-php
Make a column of type text. Just before the UPDATE (or INSERT, as the case
may be), serialize the array and addslashes (in that order: serialize,
addslashes, INSERT/UPDATE).
When retrieving the data, SELECT out the column, then stripslashes then
unserialize the data back into an array.
Reference: http://www.php.net/manual/function.serialize.php
Doug
At 11:30 PM 8/27/00 -0400, Louis Bertrand wrote:
>The way the session table is set up, I have one record per active
>session. When the user comes back for another hit, I retrieve the session
>particulars contained in the record based on the sessionID presented in
>the GET URL or hidden field in the POST. The sessionID is the primary key.
>
>I suppose I could create a separate table of pending transactions, with
>a sequence to keep them in order, referencing the sessionid.
>Is that along the lines of what you were suggesting?
>
>Thanks
> --Louis <louis@bertrandtech.on.ca>
>
>
>On Mon, 28 Aug 2000, The Hermit Hacker wrote:
>
>>
>> why not insert multiple records instead, serialized for order?
>>
>> On Sun, 27 Aug 2000, Louis Bertrand wrote:
>>
>> > Hello,
>> >
>> > I am trying to save an array of strings in a column, but I can't figure
>> > out how to retrieve it from within PHP3. What I'm hoping to accomplish is
>> > to store a sequence of update SQL statements resulting from a posted
form,
>> > then output a Confirm (Yes/No) form. If the user clicks Yes, I retrieve
>> > the SQL statements from the session table and execute them in a
>> > transaction.
>> >
>> > I added the column:
>> > alter table sessions add column pending varchar(240)[];
>> >
>> > db=> \d sessions
>> > | pending | varchar[] | var |
>> >
>> > I insert the strings with:
>> > db=> update sessions set pending = '{ \'blah\', \'blorg\'}' where
>> > sessionid = 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
>> > UPDATE 1
>> >
>> > Then the select:
>> > db=> select pending from sessions where sessionid =
>> > 'f38ca9c5f28fd13bd9619b13b5d8f2fc';
>> > {" 'blah'","'blorg'"}
>> > (1 row)
>> >
>> > Then I do the following PHP code:
>> > $selectsql = "SELECT pending"
>> > ." FROM sessions WHERE"
>> > ." sessionid = '$SessionID'";
>> > $result = @pg_Exec( $conn, $selectsql);
>> > if( $result && ( pg_NumRows($result) > 0)) {
>> > $transactions = pg_Result( $result, 0, "pending");
>> > echo "<br>pending=" . $transactions;
>> > }
>> > else {
>> > echo "<br>No transactions";
>> > } }
>> >
>> > This is what I get:
>> > pending={" 'blah'","'blorg'"}
>> >
>> > I suppose I can always tokenize and parse the string, but I'm hoping
there
>> > is a classier way around this. I'm afraid of using temporary tables
>> > because I don't want them cluttering the database when users abandon
their
>> > sessions.
>> >
>> > Using PHP3.0.16 and PostgreSQL 6.5.2 on OpenBSD (no, I haven't had
time to
>> > upgrade yet).
>> >
>> > Any thoughts?
>> >
>> > Thanks
>> > --Louis <louis@bertrandtech.on.ca>
>> >
>> > Louis Bertrand http://www.bertrandtech.on.ca/
>> > Bertrand Technical Services, Bowmanville, ON, Canada
>> > Tel: +1.905.623.1500 Fax: +1.905.623.3852
>> >
>> > OpenBSD: Secure by default. http://www.openbsd.org/
>> >
>> >
>>
>> Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
>> Systems Administrator @ hub.org
>> primary: scrappy@hub.org secondary:
scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-08-28 04:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-28 03:15 Getting arrays from a result set? Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:12 ` The Hermit Hacker <scrappy@hub.org>
2000-08-28 03:30 ` Louis Bertrand <louis@bertrandtech.on.ca>
2000-08-28 03:35 ` The Hermit Hacker <scrappy@hub.org>
2000-08-28 04:22 ` Doug Semig <dougslist@semig.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox