Jump to content

individual union-SQL_CALC_FOUND_ROWS


birbal

Recommended Posts

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

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

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

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...