CODEWITHSUNDEEP
phpmysqlarraysjoin
Rocketman
Rocketman

Reputation: 13

Create a PHP array using left join and MySQL IF statement

Here is my question

Let's say I have these two MySQL tables

Orders
CustID, OrderID, OrderDetails1, OrderDetails2, ..
1, 101, Item1, Item2, ...
1, 102, Item1, Item2, ...
1, 103, Item1, Item2, ...
2, 104, Item1, Item2, ...
2, 105, Item1, Item2, ...
.... and so on

Payments
OrderID, Paid
101, Y
105, Y
.... and so on

Now I want an array that contains all the orders (per customer) with column Paid Y or N

Array => {
(CustID=1, OrderID=101, Paid=Y),
(CustID=1, OrderID=102, Paid=N),
(CustID=1, OrderID=103, Paid=N) }

My current SQL is 

SELECT o.CustID, o.OrderID, if(p.Paid='Y','Y','N') as Paid 
FROM Orders o left JOIN Payments p USING(CustID)
WHERE o.CustID = 123456
AND o.CustID=p.CustID
Group by o.OrderID
Order by o.OrderID;

The issue is that the above SQL returns Y for all rows in the Array. Surely the IF statement within the MySQL should check and replace with 'N' if no record is present in the Payments table for a given OrderID.

OR

Do I have to do this in PHP in two steps. Need to avoid this as this will be a high-traffic function.

Thanks

Upvotes: 1

Views: 372

Answers (2)

Rico_93
Rico_93

Reputation: 108

I'm not sure what you're really having a problem with , but you should consider using a code similar to this to help you with your query , this code should display all that is paid with an id of 123456, if you want you could use the same code and chage 'Y' to "N" to display all that isn't paid.

  "SELECT
        Orders .*,
        Payments .*
        FROM
        tbl_Orders , tblinterviews
        WHERE
        Orders .CustID = 123456 AND Payments .CustID=Orders .CustID AND Orders .Paid='Y'
        Group by Orders .OrderID
    Order by Orders .OrderID;
        ";

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

I'm not understanding USING(CustID) in the join condition. The Payments table shown doesn't show a CustID column.

In the WHERE clause, this condition o.CustID=p.CustID will only be satisfied for non-NULL value of CustID. And that negates the "outerness" of the join, rendering it equivalent to an inner join.

And the inclusion of a GROUP BY clause is odd. Other databases would throw an error (something along the lines of "non-aggregate in SELECT list")

Just fix your query.

   SELECT o.CustID
        , o.OrderID
        , IF(p.Paid='Y','Y','N') AS Paid 
     FROM Orders o
     LEFT 
     JOIN Payments p
       ON p.OrderID = o.OrderID
      AND p.CustID  = o.CustID     -- ? is OrderID not unique
    WHERE o.CustID = 123456
    ORDER BY o.OrderID

Upvotes: 2

Related Questions