0

If I were to run a normal mySQL query that selects all nodes I would do this:

set @type='node';
select * from actions where type = @type;

but what if lets say I set type to be something that doesn't exist for example

set @type='';
select * from actions where type = @type or 1=1;

This will then in return select everything regardless.

How do I do the same with db_query?

I thought just doing

db_query("select * from actions where type = :type or 1=1;", array(':type' => '')).fetchAll();

but that doesn't work. Any ideas? (notice that :type is equal to '' just like in the mySQL example)

  • What is your expected results you are wanting to achieve? – iLLin Feb 18 '13 at 17:51
2

What you need to do is take that logic out of the SQL query, and instead use the query builder so to create your query; then you can add the IN statement conditionally, e.g.

// Set up your query
$query = db_select('actions')->fields('actions');

// See if you have data
if (!empty($types)) {
  // IN is the default operator for arrays so there's no need to specify,
  // but for the sake of an example...
  $query->condition('type', $types, 'IN');
}

// Get the results
$results = $query->execute()->fetchAll();
  • If possible that would make your life a bit easier in the long run (think maintenance 6 months down the road), but you could always build the query string up the 'old fashioned' way with if statements in PHP... – Clive Feb 18 '13 at 18:35
  • Actually as a last comment, does that mean this 'old chestnut' question is something that isn't possible with mysql alone? – John Riselvato Feb 18 '13 at 18:37
  • Oh not at all, MySQL has ample support for conditional statements (IF/THEN/ELSE, CASE), I just think it's easier for maintainability to keep that sort of thing in the application logic rather than the query. If you're more comfortable with raw SQL, though, it would probably be the other way round – Clive Feb 18 '13 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.