OR in WHERE clause

I have the following 4 queries:

    public function get_product_total($subcatId)
    {
        $sql  = "SELECT COUNT(*) AS count
                   FROM product_photos
                  WHERE sub_category_id = ?";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($subcatId));
                                
        $row = $stmt->fetch();                                
        return $this->aantal = $row['count'];    
    }

    public function get_product_photos($subcatId)
    {
        $sql = "SELECT *
                  FROM product_photos
                 WHERE sub_category_id = ?";
                 
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($subcatId));
        
        return $stmt->fetchAll();
    }
    
    public function get_event_total($eventId)
    {
        $sql  = "SELECT COUNT(*) AS count
                   FROM product_photos
                  WHERE event_id = ?";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($eventId));
                                
        $row = $stmt->fetch();                                
        return $this->aantal = $row['count'];    
    }
    
    public function get_event_photos($eventId)
    {
        $sql = "SELECT *
                  FROM product_photos
                 WHERE event_id = ?";
                 
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($eventId));
        
        return $stmt->fetchAll();
    }

As you will see are the first and third nearly the same and the same for the second and fourth. Everything is coming from the same table, The difference lies in the where clause. The first two queries use sub_category_id (70% of the rows in the table), while the last two queries use event_id. How do I combine the first and third query on one hand and the second and fourth on the other.

And when it is possible to combine those queries, how do I use them in the Controller e.g.

$product_photos   = $this->shop->get_product_photos($subcatId);

Thank you in advance

Why would you want to combine them?

I dont think ‘combine’ is the correct word here, Guido. If i’m reading it right, What he’s trying to do is generalize his functions, IE:

public function get_total($type,$Id)
{
    $sql  = "SELECT COUNT(*) AS count
               FROM product_photos
              WHERE ".$type."_id = ?";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(array($Id));
                            
    $row = $stmt->fetch();                                
    return $this->aantal = $row['count'];    
}

and then

$product_photos_count   = $this->shop->get_total("sub_category",$subcatId);
2 Likes

@StarLion Thanks for the reply. That was indeed where I was looking for. Thanks

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.