Andrew Andrew - 4 months ago 10
PHP Question

PDO queries search whole column

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');
$stmt->execute(array(
'tailormade' => $tailormade,
'region' => $region,
'type' => $type,
'channel' => $channel,
'firstDate' => $date1,
'secondDate' => $date2
));


Now lets say that the variables are all set except for $region. If that is the case I want to search all the options with the region option searching the whole region column.

Is this possible?

I hope the question makes sense.

Answer

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');
Comments