Question 192315 in PHP Question posted in
Official manual can be found here.

Php – SQL selecting last month values if none are used still select them

SELECT COUNT(wo.id), wm.*         
FROM webshop_orders as wo         
LEFT JOIN webshop_merchants as wm ON wo.merchant_id = wm.id         
LEFT JOIN webshop_merchant_order mo ON     mo.merchant_id = wm.id AND mo.language_id = 1
WHERE   (mo.hidden = 0) AND wm.status = 1         
AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH         
GROUP BY wo.merchant_id         
ORDER BY COUNT(wo.id) DESC

I am selecting last month orders most used payment method and filtering the most used ones at the top of the list on the payment method page. The only problem i am now facing is that when a payment method doesn’t get used for example an entire month it will skip the payment method and not show it.

My question is does somebody have a good example of this kind of problem because i cant find a good one on stackoverflow.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have made some changes to my query and it works know.

    SELECT wm.*,
        (SELECT COUNT(wo.id) as order_count FROM webshop_orders AS wo WHERE wo.merchant_id = wm.id AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH AND wo.language_id = %d) as order_count
        FROM webshop_merchants AS wm
        LEFT JOIN webshop_merchant_order mo ON mo.merchant_id = wm.id AND mo.language_id = %d
        WHERE
        mo.hidden = 0
        AND wm.status = 1
        ORDER BY order_count DESC

    results


  2. 0

    Have you tried changing the first WHERE statement ((mo.hidden = 0))with:

    (mo.hidden = 0 OR mo.hidden IS NULL)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search