So I have managed to spit out (through var_dump) the following:
// This is my whole select statement
string 'SELECT * FROM logs AS LOG WHERE ( LOG.DATE BETWEEN :startMonth AND :endMonth ) AND LOG.VALUE LIKE :filter ' (length=106)
// This is the database object (note the params)
object(Micro\Database)[1667]
protected 'query' => string 'SELECT * FROM logs AS LOG WHERE ( LOG.DATE BETWEEN :startMonth AND :endMonth ) ' (length=79)
protected 'params' =>
array
':startMonth' =>
object(DateTime)[1668]
public 'date' => string '2014-06-01 00:00:00' (length=19)
public 'timezone_type' => int 3
public 'timezone' => string 'UTC' (length=3)
':endMonth' =>
object(DateTime)[1671]
public 'date' => string '2014-06-30 23:59:59' (length=19)
public 'timezone_type' => int 3
public 'timezone' => string 'UTC' (length=3)
':filter' => string '%"unit_id": 1030006431%' (length=23)
So in the query we see that I am using :startMonth, :endMonth and :filter and then in the params We see I am setting the same variables. everything should be there. But apparently it's not:
'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'
Uh .... no all the tokens are there. Let look at the code
public static function findByWildCard($unitId=null, $messageType=null, $filter=null, $month) {
$sqlSelect = 'SELECT * ' .
'FROM logs AS LOG '.
'WHERE ( LOG.DATE BETWEEN :startMonth AND :endMonth ) ';
// Connect to lazarus
$query = self::connectToLazarus();
// Start of the specified month. Eg. 2013-01-01 00:00:00
$startMonth = \DateTime::createFromFormat('Y-m-d H:i:s', $month.' 00:00:00');
// End of the specified month. Eg. 2013-01-31 11:59:59
$endMonth = clone $startMonth;
$oneMonth = \DateInterval::createFromDateString('1 month');
$oneSecond = \DateInterval::createFromDateString('1 second');
$endMonth->add($oneMonth)->sub($oneSecond);
// Get the results from the query
$query->setQuery($sqlSelect)
->setParameter('startMonth', $startMonth)
->setParameter('endMonth', $endMonth);
if (!empty($filter)) {
$sqlSelect .= 'AND LOG.VALUE LIKE :filter ';
$query->setParameter('filter', '%'.$filter.'%');
}
var_dump($query->getResult()); exit;
}
What is going on?
You are setting the query before you add the filter to the query string.
// Get the results from the query
$query->setQuery($sqlSelect)
->setParameter('startMonth', $startMonth)
->setParameter('endMonth', $endMonth);
if (!empty($filter)) {
$sqlSelect .= 'AND LOG.VALUE LIKE :filter ';
$query->setParameter('filter', '%'.$filter.'%');
}
You are setting the query to $sqlSelect and aftwards appending the filter part after setting the query but never setting the query again to the new value.
There are three possible issues that may cause such a problem