PDO queries search whole column

Question

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.

The query itself should be pretty much the same, independent from mysqli and pdo. Can you show your working code with mysqli so we can see if you are doing something differently? — Philipp, Jul 25 '16 at 11:25
Put your array together. var_dump it. When in doubt, see the atrocity you are sending via [this](http://stackoverflow.com/a/38394479). It is not rocket science, just debugging 101 — Drew, Jul 25 '16 at 11:28

score 0 · Accepted Answer · answered Jul 25 '16 at 11:47

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

Thank you so much George. Worked perfectly – Andrew Jul 25 '16 at 11:54 — Andrew, Jul 25 '16 at 11:54
Welcome @Andrew glad to help you :))) – George G Jul 25 '16 at 11:56 — George G, Jul 25 '16 at 11:56

PDO queries search whole column

1 Answers1