Question about array datatype, and alternatives
am 09.05.2007 07:04:12 von Lynna LandstreetHi there,
I've run into a sticky situation with a site I'm working on. The site uses
an external payment gateway, and when a user has paid their membership fee
there, it routes them back to a registration form on the site, where they
can enter in their membership information, then saves it all into the
database when the form is submitted.
Now, the payment gateway returns a variety of information as a $_POST array
when it sends them back, which I had some PHP code on the membership form
capture and save into a subarray within $_SESSION, and then the script that
processes the form takes that subarray and turns it into an insert statement
which inserts the data the gateway returned into a database table set up for
that purpose, so that we have all the sales data for each member (don't
worry, this does *not* include credit card numbers or anything like that...
it's more administrative stuff).
The problem is that twice now, the information sent back by the gateway has
changed, breaking my registration system. The first time, they added several
new fields to the array it returned, so I amended the code that captures the
information to only take the fields it had corresponding columns for in the
table it eventually writes it to. Now, it's just failed again, and while I
haven't checked to see exactly what they're returning now, it looks as
though they either deleted some fields or changed their names, causing the
insert to fail.
It would be easy to set up a test page to see what they're now sending back
with each transaction, but the thing is, if it's changed twice already,
there's no reason to think it won't change further. So I'm looking for a way
to store the array that it returns in a way that won't break if it suddenly
has a different number of elements than usual, or changes the names of some
of the keys.
My first thought was that I could use the much-maligned array datatype, and
just dump the entire array they send back into one column. But having looked
through the documentation on that datatype, I'm not so sure that would work
for an array of uncertain size. It looks as though it's meant to handle
arrays that are a little more predictable in their composition. Now,
phpPgAdmin does list a datatype called "anyarray", which according to the
documentation is a "polymorphous" datatype that can handle almost anything,
but there isn't a lot of detail on how to use that. PostgreSQL's array
syntax seems to be very different from PHP's, and I don't know if it's
really feasible to just dump the entire contents of a PHP associative array
into an anyarray column.
So then I thought of just serializing the array to result in a single string
- would that work better?
Does anyone have any experience with either array/anyarray columns, or any
other way of storing incoming arrays with an uncertain number and variety of
elements in PostgreSQL?
Lynna
--
Spider Silk Design - http://www.spidersilk.net
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Cel 416.873.9289
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate