FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » PDO and date(time)s
Show: Today's Messages :: Polls :: Message Navigator
Return to the default flat view Create a new topic Submit Reply
PDO and date(time)s [message #170561] Mon, 08 November 2010 15:54 Go to previous message
Erwin Moller is currently offline  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
[Message index]
 
Read Message
Read Message
Previous Topic: Stats comp.lang.php (last 7 days)
Next Topic: Illegal variable _files or _env or _get or _post or _cookie or _server or _session or globals passed to script.
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 22:21:42 GMT 2024

Total time taken to generate the page: 0.04041 seconds