PDO MySQL query failing with more than one comparison operator

Question

I'm sure I'm just formatting this incorrectly but I'm getting a PDO exception with one of my queries and the debug isn't helping.

if I run the following it's works fine :

$db = static::getDB();

$sql = 'SELECT * FROM djs WHERE day = :day 
                 AND start_hour = :hr AND shifts LIKE :shift';

$stmt = $db->prepare($sql);
$stmt->bindParam(':day', $arr['day'], PDO::PARAM_STR);
$stmt->bindParam(':hr', $arr['hr'], PDO::PARAM_INT);
$stmt->bindParam(':shift', $shift, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetch(PDO::FETCH_OBJ);

But when I try to add more than one comparison operator in the query like this :

$sql = 'SELECT * FROM djs WHERE day = :day 
                 AND start_hour > :hr AND end_hour <= :hr 
                 AND shifts LIKE :shift';

It throws the following exception pointing to the line containing the execute command :

Uncaught exception: 'PDOException'
Message: 'SQLSTATE[HY093]: Invalid parameter number'

end_hour is a valid column in the table and I'm trying to ascertain if the star_hour is greater than :hr and the end_hour is less than or equal to :hr. I must be doing this wrong. Any pointers please? It is because I'm using the same named parameter :hr in the query twice but only binding it once? If so what is the solution other than setting up another named parameter with the same data?

@ Magnus Eriksson Thanks that answered my question. Sorry for the repeat. — spice, Jan 28 '19 at 13:09

score 5 · Accepted Answer · answered Jan 28 '19 at 13:05

5

Try to use different names for the parameters, even if you are using the same value:

$db = static::getDB();
$sql = 'SELECT * FROM djs WHERE day = :day 
                 AND start_hour > :hr1 AND end_hour <= :hr2 
                 AND shifts LIKE :shift';

$stmt = $db->prepare($sql);
$stmt->bindParam(':day', $arr['day'], PDO::PARAM_STR);
$stmt->bindParam(':hr1', $arr['hr'], PDO::PARAM_INT);
$stmt->bindParam(':hr2', $arr['hr'], PDO::PARAM_INT);
$stmt->bindParam(':shift', $shift, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetch(PDO::FETCH_OBJ);

answered Jan 28 '19 at 13:05

nacho

5,280
2
25
34

So the only option is to repeat yourself like I mentioned in the question? Why doesn't PDO allow for the use of the same named parameter twice in the same query? Seems a bit odd to me. – spice Jan 28 '19 at 13:07
1

@spice: read this http://php.net/manual/en/pdo.prepare.php – devpro Jan 28 '19 at 13:08

PDO MySQL query failing with more than one comparison operator

1 Answers1