CODEWITHSUNDEEP
phpmysqlpdo
Nevermore
Nevermore

Reputation: 1743

PDO MySQL where condition if null or equal

I have a products table which has types_id, mid_types_id and sub_types_id. sub_types_id can be null.

My query like this :

public function getProductsByType($types_id, $mid_types_id, $sub_types_id, $limit, $offset) {
        $sql = "SELECT * FROM products AS p WHERE p.types_id = :types_id AND p.mid_types_id = :mid_types_id";
        if (!empty($sub_types_id)) $sql .= " AND p.sub_types_id = :sub_types";
        $sql .= " GROUP BY p.id LIMIT :limit OFFSET :offset";

        $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $query = $this->db->prepare($sql);
        $query->bindParam('types_id', $types_id);
        $query->bindParam('mid_types_id', $mid_types_id);
        if (!empty($sub_types_id)) $query->bindParam('sub_types_id', $sub_types_id);
        $query->bindParam('limit', $limit);
        $query->bindParam('offset', $offset);
        $query->execute();
        if ($query->rowCount() > 0)
            return $query->fetchAll(PDO::FETCH_ASSOC);
        return null;
    }

My table's data like :

types_id = 3, mid_types_id = 4, sub_types_id = 2

types_id = 1, mid_types_id = 1, sub_types_id = NULL

When i send to types_id:3, mid_types_id:4, sub_types:2 it works. Also if i send to types:3, mid_types_id:4 it works too. Because it doesn't control the sub_types_id which is null.

if i create my query without sub_types_id empty control. It works but if i send to sub_types_id as a null. It doesn't work.

How can i solve this problem ?

Upvotes: 0

Views: 493

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157839

Use a Mysql spaceship operator, <=>

AND p.sub_types_id <=> :sub_types

Upvotes: 5

Dan Nagle
Dan Nagle

Reputation: 5425

Use the PDO::PARAM_NULL predefined constant to specify the null value.

if (!empty($sub_types_id)) {
  $query->bindParam('sub_types_id', $sub_types_id);
} else {
  $query->bindParam("sub_types_id", $sub_types_id, PDO::PARAM_NULL);
}

Upvotes: -1

Related Questions