Getting arrays from a result set?

Поиск
Список
Период
Сортировка
Искать

Getting arrays from a result set?

От:
Louis Bertrand <louis@bertrandtech.on.ca>
Дата:
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 "
pending=" . $transactions; } else { echo "
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 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/

Re: Getting arrays from a result set?

От:
The Hermit Hacker <scrappy@hub.org>
Дата:

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 "
pending=" . $transactions; > } > else { > echo "
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 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

Re: Getting arrays from a result set?

От:
Louis Bertrand <louis@bertrandtech.on.ca>
Дата:
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   


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 "
pending=" . $transactions; > > } > > else { > > echo "
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 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 > >

Re: Getting arrays from a result set?

От:
The Hermit Hacker <scrappy@hub.org>
Дата:

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   
> 
> 
> 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 "
pending=" . $transactions; > > > } > > > else { > > > echo "
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 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

Re: Getting arrays from a result set?

От:
Doug Semig <dougslist@semig.com>
Дата:
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   
>
>
>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 "
pending=" . $transactions; >> > } >> > else { >> > echo "
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 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
FAQ