phpmysqlmodel-view-controllerzend-frameworkwhere-clause
WHERE clause not working in a query - Zend Framework
I set up a simple DB in MySQL with these informations:
And a Zend Model CalendarioVeicolo to interact with the DB.
In my Controller file, if I execute the following query:
$query = $model->select()
->where('start_date <= ? AND end_date >= ?', $innerDate, $innerDate);
All works out fine and the output is this:
[
{
"date": "2022-10-17 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-18 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-19 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-20 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 15, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 16, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 17, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 18, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 19, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 20, "value": { "id": "17", "veicolo": "Renault Megane"}}
]
},
{
"date": "2022-10-21 00:00:00",
"value": [
{ "reference": 8, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 9, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 10, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 11, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 12, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 13, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 14, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 15, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 16, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 17, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 18, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 19, "value": { "id": "17", "veicolo": "Renault Megane"}},
{ "reference": 20, "value": { "id": "17", "veicolo": "Renault Megane"}}
]
},
{
"date": "2022-10-22 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-23 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
}
]
As you can see, for the 2022-10-20 date it return a reservation after 14:00.
But if I try to add a WHERE clause on the vehicle (veicolo column), like so:
$query = $model->select()
->where('veicolo = ? AND start_date <= ? AND end_date >= ?', $vehicle, $innerDate, $innerDate);
The result is a void JSON:
[
{
"date": "2022-10-17 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-18 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-19 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-20 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-21 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-22 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
},
{
"date": "2022-10-23 00:00:00",
"value": [
{ "reference": 8, "value": []},
{ "reference": 9, "value": []},
{ "reference": 10, "value": []},
{ "reference": 11, "value": []},
{ "reference": 12, "value": []},
{ "reference": 13, "value": []},
{ "reference": 14, "value": []},
{ "reference": 15, "value": []},
{ "reference": 16, "value": []},
{ "reference": 17, "value": []},
{ "reference": 18, "value": []},
{ "reference": 19, "value": []},
{ "reference": 20, "value": []}
]
}
]
I checked the vehicle value and is working, is a correct integer.
The same query with the WHERE clause in phpmyadmin return a correct result with a reservation for the vehicle 1 on 2022-10-20 after 14:00, in contrast with the void JSON.
Any suggestion? Thanks in advance!
Solution
I think your where is incorrectly formed. It should be:
$query = $model->select()
->where('start_date <= ?', $innerDate);
->where('end_date >= ?', $innerDate);
The where method only supports one replacement parameter. (see code) Third parameter is type, not another value. It also supports quoting array, but only in format where('x IN (?)', $arrayOfValues) I believe.
Alternatively you can quote yourself
$where = sprintf(
'start_date <= %s AND end_date >= %s',
$db->quote($innerDate),
$db->quote($innerDate)
);
$query = $model->select()
->where($where)