I want to select the entire column of a mysql database table, but only if a value has not been set.
For mysqli I would have set the value to equal the name of the column without making it a string, however in the PDO query it doesn't work?
I have the following:
$stmt = $this->_db->prepare('SELECT BookDate, DepDate, BkngVal, Channel, Market, Region, MappedType, calc_ExpMargin FROM sales
WHERE MappedType = :tailormade AND Region = :region AND MappedType = :type AND Channel = :channel AND BookDate <= :firstDate AND BookDate >= :secondDate');
'tailormade' => $tailormade,
'region' => $region,
'type' => $type,
'channel' => $channel,
'firstDate' => $date1,
'secondDate' => $date2
As I understand you sometimes want to omit search criteria from the query, you can do it like this:
SELECT BookDate, DepDate, BkngVal, Channel, Market, Region, MappedType, calc_ExpMargin FROM sales WHERE (MappedType = :tailormade OR :tailormade IS NULL) AND (Region = :region OR :region IS NULL) AND (Channel = :channel OR :region IS NULL) AND (BookDate <= :firstDate OR :firstDate IS NULL) AND (BookDate >= :secondDate OR :secondDate IS NULL)
Also you had
MappedType used twice, that would be problem when using
AND clause because one column can't have two different values at the same time. So I removed one. Anyway I give the example how to do it.
Also remember to set any parameter to
null value if you want to omit it - this can be your function call for given example:
getDataUK('FIT', NULL, 'TRADE', 'UK', '2016-01-01', '2016-01-07');