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.
Tags: PHP, PostgreSQL | 3 Comments
At first when I saw the Zend Framework recommendation of omitting the closing tag in PHP files I thought it bizarre and stupid. Afterall, you close every other syntactic character in programming: parenthesis, brackets, HTML tags, ifs, loops, etc. So why wouldn't you close the PHP tag at the end of the document as well?
The Zend Framework reason is, of course, to white space data from being sent to the browser prematurely, which messes up sending header information to the browser. My first thought was "this is lazy and encourages bad coding."
But the more I think about it, the more it makes sense. It isn't laziness, but a fool proof way to prevent a common mistake from happening. And apparently the closing tag isn't required at all, so why not?
I don't think I'll be quick to adopt this practice, but maybe eventually, and maybe as I write new code. We'll see.
Tags: PHP, Zend Framework | No Comments
While building MySQL database support into the OpenAvanti PHP framework, I came across an interesting quirk in MySQL that I thought must be a bug. Apparently, after submitting a bug report and getting a response from MySQL, it's a documented feature (and later, referred to as a limitation).
Continue Reading »
Tags: MySQL | No Comments
Today I embarked on an epic struggle with the Portage package manager attempting to update the packages on my system. After I ran into what can only be described as a phantom package.
Continue Reading »
Tags: Gentoo, Linux, Portage | 1 Comment
This snippet courtesy of Kieran Smith.
Want to get more work done in a single query? Tired of looping through query results to simply build a list of data. How about this?
SELECT
array_to_string(
ARRAY(
SELECT name
FROM projects
WHERE customer_id = 10
ORDER BY LOWER(name)
), ', '
) AS projects;
What you are doing is building an array from the subquery and then turning it back into a string immediately, so you can display multiple results in a single field. Our results might look something like this:
gnucashweb, Hangar, OpenAvanti, tarmac
Nice!
Tags: PostgreSQL | 2 Comments
I just came across an alternative to the http://gentoo-portage.com and http://packages.gentoo.org sites: http://znurt.org/
Nice, clean, beautiful and full of information.
Tags: Gentoo | 2 Comments
Gentoo, ffs, get PHP 5.3.1 in Portage ASAP. PHP 5.3 isn't even in there, yet. What's the use of your package manager if I just have to manually install stuff?
/rant
Tags: Gentoo, PHP | No Comments
Most of my database tables always have four columns in common:
created_on timestamp
created_by_id int
last_modified_on timestamp
last_modified_by_id int
These columns should be pretty self explanatory. Usually created_on has a default of now() to auto populate it. Populating the last_modified_on column is a little more difficult, especially if you don't want it to be populated on INSERT (which is what using default now()) would do.
Of course we could just provide for it in our code, especially if using a trusty application framework that takes care of it for us, but I like putting basic logic in the database, and we can't assume all data updates will come from our application. They could, for example, come from a command line update.
I crafted this quick little database trigger function for taking care of this for me:
CREATE OR REPLACE FUNCTION auto_update_last_modified()
RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
NEW.last_modified_on:= now();
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
This function simply populates the last_modified_on column of NEW (the new record being updated) with the current timestamp. Now I can add a trigger to every table that has this column:
CREATE TRIGGER auto_update_table_name_last_modified
BEFORE UPDATE
ON table_name
FOR EACH ROW
EXECUTE PROCEDURE auto_update_last_modified();
And now when updating a record in table_name, it will automatically populate a value for last_modified_on.
Tags: PostgreSQL | No Comments
When writing applications that are meant to be used by users the world over (not just users within a localized network), it's important to be able to show dates and times relative to the users time zone. If one user posts something at 11:30 EST, and a few minutes later, a user in California checks the post, it would look as if it were made in the future. Not cool.
Lucky, it's quite easy to localize dates and times using PHP's built in date classes.
Continue Reading »
Tags: PHP | 2 Comments
A new version of PostgreSQL, 8.4.2-r1, is available in Gentoo Portage, but be careful. It appears the server is compiled with HAVE_INT64_TIMESTAMP by default, something not true with previous versions. Because of this, after updating, you might get an error about the data cluster being incompatibile with the server when you restart, unless you were using the pg-intdatetime use flag in previous versions (which has been removed in the newest version).
Continue Reading »
Tags: PostgreSQL | No Comments