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.

Answer 1

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";