radar Posted July 12, 2010 Share Posted July 12, 2010 Okay so in my database I have 3 tables. One named Customers, One named Orders and one Named Deals I need to select * from Customers and count(id) from Orders where d_id == cust.id and count(id) from deals where d_id == cust.id But I have no clue how to query that result. any help is appreciated. Only issue to this, is I have modified a smarty paginate class to work with my system so I am using Smarty Paginate to do pagination and so I would still need to be able to run the limit modifier, as well as SQL_CAL_FOUND_ROWS from the customers table. Quote Link to comment https://forums.phpfreaks.com/topic/207509-some-counting-join-questions/ Share on other sites More sharing options...
Psycho Posted July 12, 2010 Share Posted July 12, 2010 Not tested: SELECT cust.field1, cust.field2, cust.field3, COUNT(Orders.id) as order_Count, COUNT(Deals.id) as deals_count FROM Customers as cust, LEFT JOIN Orders ON Orders.d_id = cust.id LEFT JOIN Deals as d ON Deals.d_id = cust.id GROUP BY Orders.d_id, Deals.d_id Quote Link to comment https://forums.phpfreaks.com/topic/207509-some-counting-join-questions/#findComment-1084922 Share on other sites More sharing options...
radar Posted July 12, 2010 Author Share Posted July 12, 2010 Okay now with that how would I add in the SQL_CALC_FOUND_ROWS after the select, as well as the Limit x,x onto it? my current query (robbed from my employees code), is like: $sql = sprintf("SELECT SQL_CALC_FOUND_ROWS * FROM customers LIMIT %d,%d", SmartyPaginate::getCurrentIndex(), SmartyPaginate::getLimit()); though I can get rid of the sprintf easily, though the SQL_CALC_FOUND_ROWS is essential for the pagination, as is the limit. Quote Link to comment https://forums.phpfreaks.com/topic/207509-some-counting-join-questions/#findComment-1084937 Share on other sites More sharing options...
radar Posted July 12, 2010 Author Share Posted July 12, 2010 Had to modify the original query you gave me a little bit but I have it working. $sql = "SELECT SQL_CALC_FOUND_ROWS cust.id, cust.name, cust.email, cust.password, cust.optin, cust.status, o.c_id, d.d_id, COUNT( o.id ) AS orders_cnt, COUNT( d.id ) AS deals_cnt FROM customers AS cust LEFT JOIN orders AS o ON o.c_id = cust.id LEFT JOIN deals AS d ON d.d_id = cust.id GROUP BY o.c_id, d.d_id LIMIT".SmartyPaginate::getCurrentIndex().", ".SmartyPaginate::getLimit(); havent done the trials through PHP yet, but it works in phpmyadmin, which would mean it would theoretically work elsewhere too. Quote Link to comment https://forums.phpfreaks.com/topic/207509-some-counting-join-questions/#findComment-1084958 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.