PDO BIND a DATE MYSQL - How to pass a date ?

Question

I have a problem

When i use this in PHP:

$stmt1 = $conn->prepare("SELECT COUNT(b_slotStart) FROM cp_booking "
                . "WHERE b_date=:date"
                . "AND :start BETWEEN cp_booking.b_slotStart "
                . "AND cp_booking.b_slotEnd "
                . "OR :stop BETWEEN cp_booking.b_slotStart AND cp_booking.b_slotEnd");

        $stmt1->bindParam(':start',$time->format('H:i'), PDO::PARAM_STR);
        $stmt1->bindParam(':stop',$time->add($interval)->format('H:i'), PDO::PARAM_STR);
        $stmt1->bindParam(':date',$date, PDO::PARAM_STR);
        $stmt1->execute();
        $slotStart = $stmt1->fetchColumn();

I get an error with the :date

but if i instead of :date write '2016-1-14' it works like a charm.

how do i pass a time string in PDO ?

I get this error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? BETWEEN cp_booking.b_slotStart AND cp_booking.b_slotEnd OR ? BETWEEN cp_bookin' at line 1' in /Applications/XAMPP/xamppfiles/htdocs/getData.php:59 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/getData.php(59): PDO->prepare('SELECT COUNT(b_...') #1 {main}

If i put '' around :date in the query i get this error:

exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in /Applications/XAMPP/xamppfiles/htdocs/getData.php:63 Stack trace: #0 /Applications/XAMPP/xamppfiles/htdocs/getData.php(63): PDOStatement->bindParam(':date', '2016-1-14', 2) #1 {main}

I am a newbie when we talk about PDO

Thanks in advance!

You had better show us an `echo $date` as it all depends on what is in that variable — RiggsFolly, Jan 14 '16 at 00:19
Possible duplicate of [PDO::PARAM for dates?](http://stackoverflow.com/questions/2374631/pdoparam-for-dates) — Shadow, Jan 14 '16 at 00:24
The f'''' space did it!!!!!!!!! I have looked and looked and looked.... FFS — Christian H, Jan 14 '16 at 00:26
`WHERE b_date=:date" . "AND` reads as `WHERE b_date=:dateAND` — Funk Forty Niner, Jan 14 '16 at 00:41

score 2 · Accepted Answer · answered Jan 14 '16 at 00:30

2

Actually I think I see the issue, just a typo probably when you replaced a hard coded date with the parameter

This line

"WHERE b_date=:date"

need a space after :date like this

"WHERE b_date=:date "

answered Jan 14 '16 at 00:30

RiggsFolly

93,638
21
103
149

Personally I find it more secure to put the space in front of multiple line SQL statements: "SELECT COUNT(b_slotStart) FROM cp_booking" . " WHERE b_date=:date" . " AND :start BETWEEN cp_booking.b_slotStart" – Mark1 Nov 03 '20 at 15:08
@Mark1 Me too :) – RiggsFolly Nov 03 '20 at 15:39

PDO BIND a DATE MYSQL - How to pass a date ?

1 Answers1

Linked