Erwin007 Posted July 11, 2023 Share Posted July 11, 2023 Hi, Things worked out fine but I still have this (small) problem: The totals are not correct: Barcelo Gran Anejo(13) should be 12 (amount) and 156 (total) Brugal Leyenda(40) should be 7 (amount) and 280 (total) Somewhere in the query I am making a mistake, I hope someone can find it. Thanks. $query = " SELECT products.product_name, sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON (products.product_id = 55 OR products.product_id = 51) WHERE salesdetails.salesdetails_productid = 55 OR salesdetails.salesdetails_productid = 51 AND sales.sales_date BETWEEN ? AND ? GROUP BY products.product_name"; Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/ Share on other sites More sharing options...
Barand Posted July 11, 2023 Share Posted July 11, 2023 Your query evidently isn't processing the records you think it should. Check. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610437 Share on other sites More sharing options...
Erwin007 Posted July 11, 2023 Author Share Posted July 11, 2023 12 minutes ago, Barand said: Your query evidently isn't processing the records you think it should. Check. Yes that's my understanding too. Maybe the reason for my post.... Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610439 Share on other sites More sharing options...
gizmola Posted July 11, 2023 Share Posted July 11, 2023 Not that this explains the problem, but if you want a grouping for these 2 products, then group by product.product_id, not product_name. Also, no reason to do this: JOIN products ON (products.product_id = 55 OR products.product_id = 51) You should be joining from the salesdetails row to product. JOIN products ON (products.product_id = salesdetails.salesdetails_product_id) Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610441 Share on other sites More sharing options...
Erwin007 Posted July 11, 2023 Author Share Posted July 11, 2023 Thanks Gizmola, that did the trick. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610442 Share on other sites More sharing options...
Barand Posted July 11, 2023 Share Posted July 11, 2023 If you remove the SELECT and GROUP BY clauses from your original query and just SELECT salesdetails.*, giving something like this SELECT salesdetails.* FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id IN (51,55) WHERE salesdetails.salesdetails_productid IN (51,55) AND sales.sales_date BETWEEN ? AND ? ... it will list the actual details records that are being processed in the query - some probably more than you expected because of the wrong join. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610443 Share on other sites More sharing options...
Erwin007 Posted July 16, 2023 Author Share Posted July 16, 2023 (edited) Unfortunately it doesn't work. After trying for days it's time to give up I guess. I am so close with this SQL: SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total FROM `sales` JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr JOIN products ON products.product_id = salesdetails.salesdetails_productid WHERE (sales.sales_date BETWEEN ? AND ?) GROUP BY products.product_name"; But instead of all the products I only need this SQL for productid's 50 till 56. The code after the SQL is this: $result = mysqli_prepare($con,$query); $result->bind_param('ss', $from, $to); $result->execute(); $result->bind_result($product_name, $amount, $total); Maybe there is something incorrect. Edited July 16, 2023 by Erwin007 Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610512 Share on other sites More sharing options...
Erwin007 Posted July 17, 2023 Author Share Posted July 17, 2023 Let me try somewhere else for some help. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610548 Share on other sites More sharing options...
Barand Posted July 17, 2023 Share Posted July 17, 2023 Your post yesterday said you had given up - feel free. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610549 Share on other sites More sharing options...
Solution Barand Posted July 17, 2023 Solution Share Posted July 17, 2023 On 7/16/2023 at 3:19 PM, Erwin007 said: But instead of all the products I only need this SQL for productid's 50 till 56. Then include that condition in your WHERE clause WHERE (sales.sales_date BETWEEN ? AND ?) AND products.product_id BETWEEN 50 AND 56 Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610550 Share on other sites More sharing options...
Erwin007 Posted July 17, 2023 Author Share Posted July 17, 2023 14 minutes ago, Barand said: Then include that condition in your WHERE clause WHERE (sales.sales_date BETWEEN ? AND ?) AND products.product_id BETWEEN 50 AND 56 I am stunned.....it works.....🙂👍 And so simple...when you know it... Appreciate your time, thanks. Quote Link to comment https://forums.phpfreaks.com/topic/317075-still-some-problems/#findComment-1610551 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.