birbal Posted November 14, 2010 Share Posted November 14, 2010 HOW to use sql_calc_found_rows with individual union?1) i have two select statement one with limit one without limit2) i want to calculate total found rows in first select staement only.i have used like this. but it is not giving desired output. mysql doc is clear about sql_calc_found_rows in the case ofSELECT but i am not sure about individual union $qry="(SELECT SQL_CALC_FOUND_ROWS....................LIMIT 0,10)UNION ALL(SELECT....................}"; $qry2="SELECT FOUND_ROWS() AS totrows"; Link to comment Share on other sites More sharing options...
boen_robot Posted November 14, 2010 Share Posted November 14, 2010 The whole UNION itself returns a table, and the counted rows are the rows of the whole UNION...I think you may have to do a separate query for that... to directly get the number, I think you can use: SELECT COUNT(*) ....................LIMIT 0,10 And to ensure the number before and after the UNION query is the same, you can use transactions. Link to comment Share on other sites More sharing options...
birbal Posted November 14, 2010 Author Share Posted November 14, 2010 is not any way to get partial union (count of one select statement)? or to do it with union in another way? And to ensure the number before and after the UNION query is the same, you can use transactions.i am not getting how to use transaction here? can you explain more? Link to comment Share on other sites More sharing options...
boen_robot Posted November 14, 2010 Share Posted November 14, 2010 There may be, but I can't think of one.It's actually an interesting issue... have some sample data I could play with? i am not getting how to use transaction here? can you explain more?Queries in transactions are executed at once, with no "waiting" from the client between the queries. Imagine the following scenario:1. You perform the first query.2. Another user of the database (or another instance of this or another PHP file) inserts data into one of the tables, thereby affecting the results of one of the number of rows without the limit (with the limit in mind, the results would probably be the same, but you need the full count, so the limit is disregarded).3. You make a second query to get the number of rows from one of the selects. In the short time between the first query was executed, we had another script alter the data, so you'll now get misleading results.With transactions, "2" will only happen after "1" and "3", thereby giving you the expected results. Link to comment Share on other sites More sharing options...
birbal Posted November 14, 2010 Author Share Posted November 14, 2010 the query is working ok else the total row returning part of non pined topic. ( SELECT SQL_CALC_FOUND_ROWS a.tid, a.subject, a.owner AS towner, a.status, a.lock, a.description, a.views, b.owner AS powner, max(a.born) AS lasttopic, count(b.pid) AS totalpost, max(b.born) AS lastpost, c.nick AS townername, d.nick AS pownername FROM topic a LEFT OUTER JOIN post b ON a.tid=b.topic LEFT OUTER JOIN usergen c ON a.owner=c.usrid LEFT OUTER JOIN usergen d ON b.owner=d.usrid WHERE a.forum='%s' AND a.status != 'pined' GROUP BY a.tid ORDER BY lastpost DESC LIMIT %s,%s ) UNION ALL ( SELECT a.tid, a.subject, a.owner AS towner, a.status, a.lock, a.description, a.views, b.owner AS powner, max(a.born) AS lasttopic, count(b.pid) AS totalpost, max(b.born) AS lastpost, c.nick AS townername, d.nick AS pownername FROM topic a LEFT OUTER JOIN post b ON a.tid=b.topic LEFT OUTER JOIN usergen c ON a.owner=c.usrid LEFT OUTER JOIN usergen d ON b.owner=d.usrid WHERE a.forum='%s' AND a.status = 'pined' GROUP BY a.tid ORDER BY lastpost DESC ) 2nd query to get the found row without limit $res=mysql_query("SELECT FOUND_ROWS() AS totaltopic",$con);$row=mysql_fetch_assoc($res) Link to comment Share on other sites More sharing options...
boen_robot Posted November 14, 2010 Share Posted November 14, 2010 OK, and some sample data dump?(i.e. the creation statement(s) for the table(s), and a few INSERTs of some data) Link to comment Share on other sites More sharing options...
Fmdpa Posted November 15, 2010 Share Posted November 15, 2010 On a side note, why do you use sprintf() for your query preparation, instead of MySQLi prepared statements? Link to comment Share on other sites More sharing options...
birbal Posted November 18, 2010 Author Share Posted November 18, 2010 On a side note, why do you use sprintf() for your query preparation, instead of MySQLi prepared statements?i dont know mysqli. i know its little bit same but as i did not use it before so i am avoiding it for now. i will surely use it after some days when i will be comfortable with mysqli.boen its working now. i left to use SQL_CALC_FOUND_ROWS instead of that i am running a small query to get the desired data.msql are saying....The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT.as they are saying it will only work for global limit (if i use a global limit at the end). i guess my situation is not like so.after reading that i change my mind to give time behind it.sorry for late replyi am realy thankful for your help boen. thank you. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.