CODEWITHSUNDEEP
phpmysqldynamicpdo
Rixhers Ajazi
Rixhers Ajazi

Reputation: 1313

Dynamic PDO Query - With AND / OR

I have two issues, the first as the title states is that I need to have dynamic query with AND/OR in it. I fully understand the AND part (I've done a bunch of these) however, the OR part is very confusing to me because looking at this following sql :

$sql = SELECT * FROM table WHERE 1

then if you add an OR statement if a condition is met : 

if(isset($_POST['OR'])){
   $sql. = " OR peaches = :good";
}

then the query will return WHERE 1 OR peaches = :good

Again I understand the part with the AND, but I do not understand how to set up the OR part.

This is how I have set up the AND / OR selection (and this works)

The second issue I am facing is this code snippet from the same script (please read code comments) : 

$sql .= " GROUP BY anum"; // I always group BY anum no matter what

if ($count !== "") { // if COUNT is not "" 
    $sql .= " HAVING COUNT(session.anum) :count"; // Then I want the user to be able to choose the operator (> < => =< =) and the dynamic number for it to use
    $placeholder[':count'] = $count; // Then add the key :count to an array with the value of $count 
}
$dynamic = $this->db->conn_id->prepare($sql);
$dynamic->execute($placeholder);

So as you notice I give the named parameter (:count) the value of $count, however this does "not work".

Is it possible to do what I am trying to do ($sql .= " HAVING COUNT(session.anum) :count";)

If not then I could just do : $sql .= " HAVING COUNT(session.anum) $count";

but that would defeat the purpose of PDO.

Any help would be great

Upvotes: 3

Views: 541

Answers (2)

Henrique Barcelos
Henrique Barcelos

Reputation: 7900

For the first issue, what is the problem exactly?

For the second, MySQL manual says that you can't use functions on having clauses. You can do like this:

SELECT *, COUNT(session.anum) AS total GROUP BY session.anum HAVING total > :count

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562338

Problem 1:
The reason that some developers use WHERE 1 when they have optional search terms is that an expression like TRUE AND <condition> is always equal to <condition>. This is basic boolean algebra.

But this is not the case for OR expressions. TRUE OR <condition> is always simply TRUE. You could modify your base query to use WHERE 0 so that when you append an OR term it comes out as WHERE 0 OR <condition>. Any expression like FALSE OR <condition> is always equal to the <condition>.

If you need to support both AND and OR in the same SQL query, you need to start putting parentheses around terms so they evaluate in the way you intend. I'm not going to explain boolean algebra and MySQL's operator precedence in this StackOverflow answer. But suffice to say that simply appending terms with .= isn't going to work when you have a mix of AND and OR terms.

Problem 2:
Parameters are very useful, but they don't solve every case of dynamic SQL. You can use an SQL parameter in place of a single literal value, but nothing else.

  • Not table names
  • Not column names
  • Not lists of values (like an IN( ) predicate)
  • Not SQL keywords
  • Not expressions
  • Not operators

You have to use string interpolation to include a user-chosen operator in your HAVING clause.

It's recommended to use whitelisting to avoid risk of SQL injection when you need to interpolate dynamic content and can't use a parameter.

Upvotes: 2

Related Questions