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
Switch to threaded view of this topic Create a new topic Submit Reply
PDO and date(time)s [message #170561] Mon, 08 November 2010 15:54 Go to next message
Erwin Moller is currently offline  Erwin Moller
Messages: 228
Registered: September 2010
Karma: 0
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
Re: PDO and date(time)s [message #170567 is a reply to message #170561] Mon, 08 November 2010 16:40 Go to previous message
Matthew Leonhardt is currently offline  Matthew Leonhardt
Messages: 9
Registered: November 2010
Karma: 0
Junior Member
"Erwin Moller"
<Since_humans_read_this_I_am_spammed_too_much(at)spamyourself(dot)com> wrote in
message news:4cd81d34$0$34849$e4fe514c(at)news(dot)xs4all(dot)nl...
> 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.

I get your frustration, but I think at the root of it, you're talking about
a difference in SQL, not access. IF one RDBMS prefers/requires one date
format, and another prefers something else, there's nothing in PDO that will
help you build syntactically valid SQL for that.

By the same token, there's nothing in PDO that will be able to assist with
the correct syntax for row-limiting between MySQL, MSSQL, Oracle, etc.
(LIMIT, TOP, etc.)

I think what you're really after here is a SQL-abstraction layer. I've
built my own just for this purpose.
  Switch to threaded view of this topic Create a new topic Submit Reply
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: Thu Nov 14 13:22:13 GMT 2024

Total time taken to generate the page: 0.02774 seconds