pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pg 043g9j2g <pg043g9j2g@gmail.com>
To: pgsql-php@postgresql.org
Subject: newbie issues with PDO / stored procedures
Date: Thu, 12 Jun 2008 05:46:14 -0400
Message-ID: <4850F066.7070306@gmail.com> (raw)

Hi! I am new to PHP and Postgres; in the early stages of creating a web 
app, just trying to achieve rudimentary results, yet trying to follow 
"best practices" -- in the form of using Stored Procedures vs. Dynamic 
SQL -- as I do hope to take it live to the scary ol' Internet one day.

I have a stored procedure (function), "select_user_details", of the form 
"SELECT * FROM users;" which returns "SETOF users".

When I execute my prepared statement, "SELECT 
select_user_details([userid])" via PDO, I get a rowset back which holds 
all the columns of users as an array in one column. From my experiement 
directly in pgAdmin Query tool, I guess that's what you expect when you 
return SETOF, but I already miss the simple DSQL I had set up where I 
could access returned columns via $row[columname] syntax.

I messed around with using fetch() but that didn't seem right (more on 
that below). Then I took a look at fetchColumn() but I didn't like that 
because you address columns by index # instead of column name. Also the 
fact that you advance the row pointer with every call to any kind of 
fetch*() seems like it isn't well suited to retrieving data from more 
than one column.

Now it looks like my best option in hopes of being able to access 
columns by semantically sensible names is to fetchObject() into PHP 
objects representing my database business objects. Is this correct? 
Suggestions? Alternatives? Insights?

In Postgres, is there any way to use named params in my stored 
procedures rather than just addressing them by number?

Also, what do I tell my SP to return if I want to return a set of 
columns that is not directly represented by some existing table/schema 
in my database. For example "a few columns from table a, joined to a few 
columns from table b..." that sort of thing.

To back up a few steps to the "big picture" standpoint, I can see pretty 
clearly how things should work well if I am using a stored procedure to 
return a single value, but I am not so sure when it comes to returning 
multiple column recordsets. Is this the best way to go about returning, 
say, a complete "User Details" record?

Bonus question: why, when I was first experimenting with this, and 
trying to figure out how to "get at" my SELECTed data after calling 
$spUsers->execute(), and used the form "$row = $spUsers->fetch();" did 
the resulting $row contain a 2-element array? The element indices were 
[select_user_details] and [0] and both contained the identical row 
value. (Only one row is being returned by the SP at this point, and I 
don't understand how, in the PHP code, that row was being appended to 
the $row array more than once. Hence, confusion.)

Any links to existing tutorials/examples on these specifics would be 
welcome as well.

Thanks for reading, and thanks for any help!




view thread (4+ messages)  latest in thread

Message-ID: <4850F066.7070306@gmail.com>
Permalink:  ../4850F066.7070306@gmail.com/
Also on:    postgresql.org/message-id/4850F066.7070306@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: pg043g9j2g@gmail.com
  Subject: Re: newbie issues with PDO / stored procedures
  In-Reply-To: <4850F066.7070306@gmail.com>
  References: <4850F066.7070306@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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