5
Mar
2010
Mar
2010
PostgreSQL arrays and PHP’s str_getcsv()
Yesterday, while trying to figure out the best way to deal with PostgreSQL arrays in PHP, I came across the new str_getcsv() function in PHP as of 5.3. This function works much the same as fgetcsv to parse a CSV line, except that it works on a string instead of a file.
For quick reference, CSV looks like this:
"My Name",14,"32,000","2009-04-15"
And the return value of a PostgreSQL array looks like this:
{"My Name",14,"32,000","2009-04-15"}Notice the similarities?
We can use PHP’s trim and str_getcsv to turn this PostgreSQL array into a PHP array:
<?php $data = str_getcsv(trim($record->value, "{}")); ?>
Simple as simple does. As long as your array has only a single dimension. If you’re using multidimensional arrays in PostgreSQL then you’re dead to me.
Category: Database, Programming
Tags: PHP, PostgreSQL
[...] the original: PostgreSQL arrays and PHP's str_getcsv() – phup 'n stuff [...]
[...] More here: PostgreSQL arrays and PHP's str_getcsv() – phup 'n stuff [...]
If only there were an easy way to replace the array {}s with []s without accidentally mangling the strings, you could throw it at a JSON parser instead. And it might even work for multidimensional arrays!
I found this method to be at least 50% slower than explode() and PostgreSQL’s ARRAY_TO_STRING() most of the time.
Faster way to handle PostgreSQL arrays in PHP:
Example query:
SELECT ARRAY_TO_STRING(ARRAY_AGG(column), ‘,’) AS data FROM TABLE
PHP code:
If you know your data doesn’t contain commas, then both the original solution and the array_to_string() are unnecessary. You can safely trim() and explode() on the column. As far as I know only string (CHAR, VARCHAR, TEXT) types will contain commas.
If your data might contain commas in it, I don’t see how Red fish’s code improves things at all as you’re still going to end up with the quoted comma data issue to begin with. The only thing I see it saves is the trim() step, but that’s probably made up by the extra time the database does converting the array to a string. I suspect the OP solution would work best.
Lastly, unless you experience a serious increase in your execution time, I would still not recommend the array_to_string() solution because if performance is an issue, returning a second class data type (pg arrays) in a large result set probably isn’t a good idea. If performance isn’t an issue, making code changes in two places (SQL and PHP) probably isn’t a good idea either.
And I agree, multidimensional arrays in a relational database table just scream “bad idea”. If you’re doing that either take a class or just switch to a NoSQL database.
Gregory, great points. And yeah, if you know your data doesn’t have commas in it, then it’s a no brainer. I don’t remember my specific use case for this anymore (it was a crazy database schema someone else setup), but it definitely involved commas.