getting variable into my active record call (left join)

[eluser]skattabrain[/eluser]
Any idea why the $product_id variable comes up blank in my queries?

Code:

function get_product_ingredients($product_id=''){    
            
if($product_id == '') {
return false;
}
            
$this->db->from('ingredient_item');

$this->db->join('ingredient_type', 'ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id');

$this->db->join('ingredient_to_product', 'ingredient_to_product.product_id =  '.$product_id.' AND ingredient_to_product.ingredient_id =  ingredient_item.ingredient_id','left');


$this->db->order_by('ingredient_type');
$this->db->order_by('ingredient');
$query = $this->db->get();                
return $query;        

}

the 2nd join doesn't pull the $product_id ... here's what runs ...

SELECT * FROM (`ingredient_item`) JOIN `ingredient_type` ON ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id LEFT JOIN `ingredient_to_product` ON ingredient_to_product.product_id = AND ingredient_to_product.ingredient_id = ingredient_item.ingredient_id ORDER BY `ingredient_type`, `ingredient`

[eluser]mdowns[/eluser]
I believe this is your problem...shouldn't it be $product_id == ''

Code:

if($product_id != '') {
return false;}

[eluser]skattabrain[/eluser]
actually, it's not ... ignore that if statement ... that was an edit mistake when posting the code here.

[eluser]mdowns[/eluser]
Interesting. If it ends up being the join, why not use a where clause?

[eluser]skattabrain[/eluser]
sorry! my initial post was not the problem.

i made some edits, but still have an issue ....

here is what i want to run ...

where '1003' was created by the variable $product_id

SELECT * FROM (`ingredient_item`)
JOIN `ingredient_type` ON ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id
LEFT JOIN `ingredient_to_product` ON ingredient_to_product.product_id = '1003' AND ingredient_to_product.ingredient_id = ingredient_item.ingredient_id
ORDER BY `ingredient_type`, `ingredient`


here is what runs ...



SELECT * FROM (`ingredient_to_product`)
JOIN `ingredient_item` ON ingredient_item.ingredient_id = ingredient_to_product.ingredient_id
JOIN `ingredient_type` ON ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id
WHERE `product_id` = '1003'
ORDER BY `ingredient_type`, `ingredient`


so it pulls the variable ... but my query is not waht i want.

basically ... my query wants "all ingredients" and if the product 1003 has that ingredient, get that result.

[eluser]skattabrain[/eluser]
i think i might just need to skip active record on this query

[eluser]mdowns[/eluser]
What's the code look like now?

Is there a difference in the results of the query?

[eluser]skattabrain[/eluser]
heh heh, i'm a mess

remember kids, put the crack pipe down and wait a few minutes before coding again.

[eluser]mdowns[/eluser]
Well it doesn't seem to me like the query results would be different...If all else fails, just don't use active record for this query if you want absolute control over your queries.

Good luck