Обсуждение: Getting arrays from a result set?
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/
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
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 > >
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
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