Help with count( ) with a query containing math 2

[clone45]

Hello! I'm having some trouble getting my syntax right. Here's my original query:

SELECT name, longitude, latitude, address, id, created, image,(((acos(sin(($latitude*pi()/180)) * sin((latitude*pi()/180)) + cos(($latitude*pi()/180)) * cos((latitude*pi()/180)) * cos((($longitude - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM items $search_sql HAVING distance <= $distance $sortOrder LIMIT 5

This works great, but I now need to count the total number of rows withouth the LIMIT for pagination purposes. In other words, something like this:

SELECT count(*) as item_count,(((acos(sin(($latitude*pi()/180)) * sin((latitude*pi()/180)) + cos(($latitude*pi()/180)) * cos((latitude*pi()/180)) * cos((($longitude - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM items $search_sql HAVING distance <= $distance $sortOrder

Unfortunately, this doesn't work because I'm not using "group by" with my count(*). Can someone help me figure this out? Thanks!

- Bret

 

[r937]

look up the FOUND_ROWS() function



r937.com | rudy.ca

 

[clone45]

Fantastic! I never knew that existed!

 

[harmmeijer]

Sorry to dig up an old thread here but I was wondering if I could use that found_rows and return data based on that.
Here is what the manual says:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

"it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. "

So how do I return the rows without running the statement again? And if there are too many rows return something else?

(note that the next statement has errors since I don't know how to use the if statement in mysql

http://dev.mysql.com/doc/refman/5.1/en/if-statement.html

)

SELECT distinct SQL_CALC_FOUND_ROWS latitude, longitude, count(latitude) as 'cnt'
from users
group by latitude, longitude;
SET @rows = FOUND_ROWS();
IF @rows < 10 THEN
-- return the previously select statement
-- according to the manual we do NOT need to re-execute it
ELSE
-- select @rows as 'numrows'
end if

Greetings, Harm Meijer

http://www.niceinchina.com

 

[r937]

it appears you're not using LIMIT

FOUND_ROWS is for those situations where you're using LIMIT and want to display something like

rows 1 to 10 of 2300

r937.com | rudy.ca

 

[TonyGroves]

As far as I can tell, FOUND_ROWS will work for any select statement, with or without a LIMIT.

You could store the query result in a temporary table:
[tt] CREATE TEMPORARY TABLE t AS SELECT ...[/tt]
then return that entire table if required. It would probably be a lot faster than running the query again. On the other hand, re-running the original query might not take as long as the first time, as the query and its results would probably have been cached by the MySQL server.

 

[harmmeijer]

Thank you for your replies, I'll just have php get a count first and depending on the result select grouped set or not.
This is because I can't even get a simple if statement to run in mysql nor setting a variable.


The following only creates errors in mysql:
set @row = select count(distinct concat(latitude, ' - ', longitude)) as 'num' from users;
if @row > 13 then
-- select grouped set
else
-- select non grouped set
end if

or the one using FOUND_ROWS()

SELECT distinct SQL_CALC_FOUND_ROWS latitude, longitude, count(latitude) as 'cnt'
from users
group by latitude, longitude;
SET @rows = FOUND_ROWS();
IF @rows < 10 THEN
-- return the previous select statement
ELSE
-- group them all and count
end if

Greetings, Harm Meijer

http://www.niceinchina.com