do I need two queries? 1

[RobBroekhuis]

I'm writing a simple web forum (mysql/php), and would like to display search results 20 hits at a time. To know how many pages of hits there are, I can execute a query

select * from posts where condition=met
followed by
pages=ceil(mysql_num_rows()/20)

and to display the posts on a particular page, I can do

select * from posts where condition=met limit (page*20),20
while mysql_fetch_rows {...}

Is there a way I can get both out of one query? Would that be worth it? I'm new to mysql and don't know how it optimizes for speed, but figure that in the background, it has to do the exact same thing twice if I use two queries.


Rob

 

[tshot]

what i understand is that u wish to find out total count of records by that query and also individual records.

i don't think it is possible because record count is a group function. and Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause.

Besides, it would not result in any effective time saving.





----------------
ur feedback is a very welcome desire

 

[hos2]

ofcourse it are 2 different queries since you also use 2 different pages. one where you show the url to the results and the other where you show the results

 

[RobBroekhuis]

hos2: no, not quite. It's all happening on one page. The url links I put on the page refer to a particular record - on the next page I do not need to execute the search again, just a simple query to an indexed field.
But based on the replies, it sounds like I'll need two queries nonetheless. Is there no easy way to do the query to return all the results, and then just pick the nth set of 20 from this set?


Rob

 

[hos2]

there is absolutely no problem with 2 queries in one page. it's sometimes easier to use 2 queries since it's more transparent in the code about what you are doing. some pages of my website have 8 queries in one page.

I use also the same construction as you describe above.

 

[tshot]

u still require 2 queries but mysqlo (latets version) has something more elgant for u called found_rows(). followinfg from its manual

FOUND_ROWS() 
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To get this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, then invoke FOUND_ROWS() afterward: 
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
       WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
 The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. (If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not.) Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client. SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result. The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole. 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. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are: 

 The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION. 
The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate. 
If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION. SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL version 4.0.0.

----------------
ur feedback is a very welcome desire

 

[cdlvj]

Try this

select count(*)as ct, * from posts where condition=met
followed by
pages=ceil(ct/20)

 

[RobBroekhuis]

tshot,
That's great information! Just about exactly what I needed, and I just verified that it works for me. Thanks a lot.


Rob