Figuring out how many items match SELECT statement without pulling all

[LucL]

Hi Guys,

Say I want to know how many items match a select statement like this...

SELECT * FROM products WHERE product_name="bag"

Now say there are 10,000 results found, but I only care to see 100 at a time, yet I want to know how many total were found so I can generate page 1 | 2 | 3 links.

LIMIT 100 will only pull 100 at a time, but it won't tell me how many in total were found.

And the above statement will pull all, but will take up a bit of memory and 99% of the stuff will be useless.

I also know I can do 2 seperate calls, but that seems inefficient, so is there a single call I can use to pull 100 results at a time from product_id X to product_id Y and at the same time know how many results total without reading all of the unnecessary data?

Thanks!
Luc L.

 

[r937]

do a search on the mysql for the SQL_CALC_FOUND_ROWS function

r937.com | rudy.ca

 

[QatQat]

Souns like you are trying to avoid paginating in your scripting language, is that the case?

Cheers

QatQat

Life is what happens when you are making other plans.

 

[LucL]

r937. thanks for that, it really did the trick. that function is AWESOME.

paginating? what is that? i'm working with perl and i'm not aware of any built in page generating functions.

 

[QatQat]

OK,

just asking b'cause I came across a few people in my department using ASP and asking how to avoid having to prepare ADO recordset to paginate through records.

They were thinking about knowing the number of records in advance to then split them into separate pages.

They also were experiencing problems with MySQL and ADO recordset.recordcount() method that does seem to work only with SQL server/Access.

That's why it came to my mind.

Cheers

QatQat


Life is what happens when you are making other plans.

 

[LucL]

Ahh, gotcha. Makes sense. Thanks!