CODEWITHSUNDEEP
phpsqldatabasecodeigniter
yaxe
yaxe

Reputation: 365

Mysql-Codeigniter Get Products Related to a Product

I am making an SQL query where I want to get related products to a product based on category. My Query is as follows:

$sql=
        "SELECT * FROM `products` WHERE products.id IN (
        SELECT product_categories.product_id FROM `product_categories` WHERE product_categories.category_id=(
        SELECT product_categories.category_id FROM `product_categories` WHERE product_categories.product_id=".
        $this->db->escape($product_id)."));";
        return $this->db->query($sql)->result(static::$model_name);//return related products array

But this also gives me the original product I don't want the original product, I couldn't do it using JOINS so I switched to basic queries still stuck. how can I exclude it in this query?

Upvotes: 1

Views: 1259

Answers (1)

Claudio
Claudio

Reputation: 5183

I think you can solve that with a join:

$sql = 'SELECT products.*
        FROM products
        JOIN product_categories ON  product_categories.product_id = products.id
        WHERE product_categories.product_id != '. $this->db->escape($product_id);
return $this->db->query($sql)->result(static::$model_name);//return related products array

Upvotes: 2

Related Questions