phpmysqlcodeigniteractiverecordescaping

How to compare two table columns passed to CodeIgniter's where() in associative array

I have a very basic statement that I am trying to run and I am have a problem.

$item = $this->db
    ->select("r.CustomerIDs, r.DateAdded")
    ->join("customer_orders_rewards as cor", "r.RewardID = cor.RewardID")
    ->join("customer_orders as co", "co.OrderID = cor.OrderID")
    ->where(array("r.Denomination" => $row['Denomination'], "r.RewardID" => "cor.RewardID"))
    ->get("customer_rewards as r");

In the statement above, it is interpreting cor.RewardID as a string where I want it to be a field from the join.

It is causing the query to look like this:

SELECT `r`.`CustomerID`, `r`.`DateAdded` 
FROM `customer_rewards` as `r` 

JOIN `customer_orders_rewards` as `cor` 
ON `r`.`RewardID` = `cor`.`RewardID` 

JOIN `customer_orders` as `co` 
ON `co`.`OrderID` = `cor`.`OrderID` 

WHERE `r`.`Denomination` = '35' 
AND `r`.`RewardID` = 'cor.RewardID'  <---- Issue

How can I reference a field from the join in my WHERE clause?

Solution

  • A simple workaround is to use

    "r.RewardID = cor.RewardID"

    instead of

    "r.RewardID" => "cor.RewardID"

    That way, cor.RewardID shouldn't be treated as a string literal, but as an actual column.

    On a side note, you're already joining rows when r.RewardID = cor.RewardID, so I'd say that extra condition is redundant (the problematic one), making it not needed.