How to pass date as parameter from php to postgres

Question

i have the following request:

 select * from newagenda where 
     debut >'$1' AND debut < date '$1' + interval '24 hours' and
     agendaid=$2'

which fails with a type cast error...

 failed: the error: = FEHLER: ungültige Eingabesyntax für Typ timestamp:

i try to translate: ERROR: invalid inputsyntax for type timestamp:

before i parametrized, i had

 select * from newagenda where debut >'$adate' AND debut < date '$adate' +
     interval '24 hours' and agendaid=$agid;

which worked perfectly well.....

now, the date comes back through a post request from a web request, so can inherently be manipulated, that's why would like to parametrize it, but i am clueless on how to get this to work.....

i tried

 select * from newagenda where 
     debut >'$1'::DATE AND debut < date '$1'::DATE + interval '24 hours' and
     agendaid=$2'

or

$largs = array($isodate."::DATE");

also

$largs = array("'".$isodate."'::DATE");

but nothing worked...... how can i get this get to work? thanks in advance!

The first query has an obvious syntax error (typo here?) but even without, that is not a parametrized query... — arkascha
– arkascha, Commented Mar 19, 2019 at 15:08
and the proper SQL format for a date/time is date('Y-m-d G:i:s') — DarkMukke
– DarkMukke, Commented Mar 19, 2019 at 15:20

DarkMukke · Accepted Answer · 2019-03-19 15:27:19Z

1

You are probably looking for something along the lines of :

<?php

//using single quoates to make sure the variables aren't expanded
$sql = 'select * from newagenda 
        where 
            debut >$1::DATE 
            AND debut < date $2::DATE
            and agendaid=$3';

$formattedDate = new \DateTime($date);
$rangeStart = $formattedDate->format('Y-m-d G:i:s');
$formattedDate->add('1 day');
$rangeEnd = $formattedDate->format('Y-m-d G:i:s');

pg_prepare($con,'sel_from_agenda', $sql);
pg_execute($con,'sel_from_agenda', [$rangeStart, $rangeEnd, $agid]);

I using functional connections here but it would probably be best to use OO or PDO.

answered Mar 19, 2019 at 15:27

DarkMukke

2,4891 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Noh Kumado Over a year ago

Thanks, that works, even without the casts, but this means i can't use the postgres functions ?

DarkMukke Over a year ago

Why wouldn't you be able to ? pg_prepare($con,'sel_my_func', 'select myFumction($1, $2)');

Noh Kumado Over a year ago

Ah i meant the date manipulations directly in the sql request

DarkMukke Over a year ago

I see, in that case, have a look at stackoverflow.com/questions/7796657/…

greg · Accepted Answer · 2019-03-20 14:49:33Z

0

You can use the pomm-project/foundation library take advantage of the parameters (and results) converters and by the way use the range type and operators:

$pomm = new PommProject\Foundation\Pomm(['db' => ['dsn' => 'pgsql://user@host/db_name']]);

$date = new \Datetime(); // set your date here
$query = <<<SQL
select * from newagenda
where
  tsrange($*::timestamp, $*::timestamp + '1 day'::interval, '()') @> debut
  and agendaid = $*
SQL;
$iterator = $pomm['db']
    ->getQueryManager()
    ->query($query, [$date, $date, 123]);

foreach ($iterator as $row) {
    print_r($row);
}

answered Mar 20, 2019 at 14:49

greg

3,5351 gold badge27 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to pass date as parameter from php to postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest