using mysql variable in where clause with PDO

Question

I'm still struggling to find out the best possible solution for binding one date as variable in SQL with PDO. So far I programmed a function in PHP which approximates times in a specific date. I only have one date but the dirtiest solution which I found would be to bind it twice which I wouldn't risk to keep my code clean. So I was thinking to set a variable which will be used then in Where Clause. That's my code so far:

<?php
function approximateAppo($date){
    $link = OpenDB();
    try {
        $query = "SET @v1 = :date;
                  SELECT from, till
                  FROM termin
                  WHERE from >= CONVERT('@v1 08:00:00', DATETIME) 
                  AND till <= CONVERT('@v1 20:00:00', DATETIME) 
                  AND comp_id=:comp";
        $statement = $link->prepare($query);
        $statement->bindValue(':comp', $_SESSION['comp'],PDO::PARAM_INT);
        $statement->bindValue(':date', $date, PDO::PARAM_STR);
        $statement->execute();
        $row = $statement->fetchAll();
    } catch (PDOException $e){
        p($e);
    }
    CloseDB($link);
    return $row;
}

But it doesn't really work, what might be the issue in there? Or what is the best possible solution for solving the issue? I still haven't found anything similar for solving my case.

You run multiple query-statements inside one query. Separate them out and I think it should work. — Qirel
– Qirel, Commented Jul 11, 2019 at 10:17

Barmar · Accepted Answer · 2019-07-11 10:17:14Z

2

You can't execute multiple queries in a single call.

You can initialize a variable in a subquery.

Also, variables aren't replaced inside strings, you need to use CONCAT().

$query = "SELECT from, till
          FROM termin
          CROSS JOIN (SELECT @v1 := :date) AS x
          WHERE from >= CONVERT(CONCAT(@v1, ' 08:00:00'), DATETIME) 
          AND till <= CONVERT(CONCAT(@v1, ' 20:00:00'), DATETIME) 
          AND comp_id=:comp";

But there isn't really a need for the variable, you can use the :date placeholder twice.

$query = "SELECT from, till
          FROM termin
          WHERE from >= CONVERT(CONCAT(:date, ' 08:00:00'), DATETIME) 
          AND till <= CONVERT(CONCAT(:date, ' 20:00:00'), DATETIME) 
          AND comp_id=:comp";

answered Jul 11, 2019 at 10:17

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

Sparkm4n Over a year ago

yes, CONCAT was the key. Because when I tried to use :date placeholder twice I got an error

Barmar Over a year ago

Placeholders also can't be used inside strings. You probably got an error because it didn't find the placeholder.

Collectives™ on Stack Overflow

using mysql variable in where clause with PDO

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related