Mysql query help - error somewehre - cannot find it

[bambinou1980]

Hello,

 

I have this query which is returning a blank page at the moment:

 

$sql = "SELECT r.resellers_id,
               r.reseller_name,
               r.reseller_surname,
               r.reseller_email,
               r.reseller_phone,
               r.reseller_commission,
               r.reseller_vat_number,
               o.orders_id,
               o.reseller_earnings,
               o.order_status,
               o.has_reseller_been_paid,
               SUM(o.reseller_earnings) as total
               
               
               FROM orders AS o
               LEFT JOIN resellers as r
               ON o.orders_resellers_id = r.resellers_id
               WHERE o.due_date BETWEEN '2014-08-14' AND NOW()
               AND o.order_status LIKE '%Order Completed%'

               GROUP BY r.resellers_id
               ORDER BY total DESC";

 

Based on the below tables, could you please let me know if you see an aerror anywhere?

 

All I am trying to do is have all the resellers ids group together and show how much money we owe them each month.

For this I need to put the "UNPAID" resellers from the "o.has_reseller_been_paid"  at the top of the ladder with the sum of all their earnings from the o.reseller_earnings row.

 

 

 

Thank you!

Edited August 26, 2015 by Ch0cu3r

[Ch0cu3r]

How do you know the problem is the query? A blank white page can be caused by anything. I would suggest you either enable error reporting/display errors in PHP's config or look at your servers error logs.

[maxxd]

Definitely turn on error reporting. On everything you dev, always.

 

I don't see anything obviously wrong with the query, which leads me to think it's something in the PHP instead. Error reporting will tell you.

[bambinou1980]

What I am getting now is some entries that are empty. I think the problem comes from my JOIN, I think it should be on the orders and not on the resellers.

[ginerjm]

I'm confused. You started this topic saying that you have a blank page. Now in your last post you state that "some entries ..are empty". Big, big difference.

 

If you want to see the Resellers that have had sales in the last period then yes - use that table as your left side. Or do a right join. Currently you are selecting orders for the period and that should be working I believe. When you say 'blank' do you mean "missing reseller data"? Any blank reseller data would indicate a bad/missing id in that case.

[bambinou1980]

Yes my apology in between I did change the qeury which produced some results.

 

So It is nearly there, the problem is that the reseller_earnings are not suming up, it is weird because it should work without problem, I am not seeing what is wrong here.

$sql = "SELECT r.resellers_id,
               r.reseller_name,
               r.reseller_surname,
               r.reseller_email,
               r.reseller_phone,
               r.reseller_commission,
               r.reseller_vat_number,
               o.orders_id,
               o.reseller_earnings,
               o.order_status,
               o.has_reseller_been_paid,
               SUM(o.reseller_earnings) as total
      FROM orders AS o
      LEFT JOIN resellers as r
      ON r.resellers_id = o.orders_resellers_id
      WHERE o.due_date BETWEEN '2014-08-14' AND '2016-08-14'
      GROUP BY r.resellers_id
               ORDER BY total DESC";​

[bambinou1980]

Ok My apology I have just worked out why.... on the  php output as I using

 

$row['rseller_earnings']; 

instead of

 

 $row['total']; ​

 

Thank you so much everyone!