| PDO and date(time)s [message #170561] |
Mon, 08 November 2010 15:54  |
Erwin Moller
Messages: 228
Registered: September 2010
Karma:
|
Senior Member |
|
|
Hi all,
Until now I have always used adodb for database abstraction.
(http://www.phplens.com/adodb/)
Or database specific PHP functions.
I thought it might be good/fun to try PDO for my next project.
PDO doesn't offer full database abstraction, just 'database access
abstraction', according to the specs on www.php.net.
So far, everything in PDO seems to make sense, works as expected, etc,
except: Where is the date and/or datetime handling?
It is simply not implemented at all it seems.
Since I am new to PDO and nobody ever used the words "PDO datetime" ever
before in comp.lang.php according to googlegroups, I thought I better ask.
http://groups.google.com/groups/search?q=pdo+datetime+group%3Acomp.lang.php
(But I don't trust google groups too much these days)
I created a simple example so we have something to talk about:
** database: **
(Postgres, but that doesn't matter too much)
create table tbldob(
dob_id serial primary key,
name text,
dob date
)
Now suppose we want to store a name and the dob (dob = date of birth).
Posted ($_POST) is:
$_POST["name"] a string
$_POST["dob_year"] , expecting an integer
$_POST["dob_month"] , expecting an integer
$_POST["dob_day"] , expecting an integer
Here under follows the code with three approaches I could think of.
I don't like any so far.
Please comment. :-)
** PHP/PDO ** (errorhandling stuff removed)
$connection = new PDO($dsn, $dbusername, $dbpassword);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
Approach 1: Use multiple placeholders
$prepStm = $connection->prepare
("INSERT INTO tbldob (name,dob) VALUES (:name,
date(:dob_year || '-' || :dob_month || '-' || :dob_day));");
$prepStm->bindValue(':name',"erwinstest",PDO::PARAM_STR);
$prepStm->bindValue(':dob_year',$_POST["dob_year"],PDO::PARAM_STR);
$prepStm->bindValue(':dob_month',$_POST["dob_month"],PDO::PARAM_STR);
$prepStm->bindValue(':dob_day',$_POST["dob_day"],PDO::PARAM_STR);
$prepStm->execute();
Approach 2: Assemble the date string by hand:
$prepStm = $connection->prepare
("INSERT INTO tbldob (name,dob) VALUES (:name, date(:dob));");
$prepStm->bindValue(':name',"erwinstest",PDO::PARAM_STR);
$dob = $_POST["dob_year"]."-".$_POST["dob_month"]."-".$_POST["dob_day"];
$prepStm->bindValue(':dob',$dob,PDO::PARAM_STR);
$prepStm->execute();
Approach 3: Cast to UTS and use UTS on database too
-------------------------------
My conclusion:
My problem with my first 2 approaches is that I am constructing a string
for a date that I know will be understood by Postgres.
(And I also use date() function, which is maybe postgres-only too.)
But MS Access, for example, uses something like #Y-m-d# for dates and
#Y-m-d h:i:sA# for timestamps.
So that doesn't seem a good approach to real "data-access abstraction"
at all.
Does that mean I should use Unix timestamps (approach3) to do it right?
Is it just me, or is this a missing piece of functionality?
How do others use PDO when it comes to dates and datetimes?
If you have experience with PDO, please let me hear what you think.
Thanks for your time.
Regards,
Erwin Moller
--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]