MySQL PDO LIMIT

Question

I found myselfe lately in need of pagination therefore i dug up my old mysql_query script that have served its purpose. I tried to redone it so it fits for my current MySQL PDO class but one thing isnt right.

The problem is that to find out how many pages query will produce, i need to first conduct entire query without LIMIT'ation, than i have to conduct again the same query to be able to limit it, it feels like waste of resources and time, those queries are really long, take approx 20 different filters to do it, so i believe that i must be doing something wrong.

Is there a way to check using PDO how many rows are there in total in the same time as applying LIMIT into it?

For now this is what i have to do:

exec i.e. this query:

SELECT * FROM inventory WHERE
    season = ?,
    category = ?,
    code = ?,
    storage = ?,
    price > ?,
    price < ?,
    purchase_date > ?,
    purchase_date < ?,
    index_date > ?,
    index_date < ?,
    product_class = ?

and than i got access to afected rows so i can exec it again and append LIMIT:

SELECT * FROM inventory WHERE
    season = ?,
    category = ?,
    code = ?,
    storage = ?,
    price > ?,
    price < ?,
    purchase_date > ?,
    purchase_date < ?,
    index_date > ?,
    index_date < ?,
    product_class = ?
    LIMIT {$BEGIN}, {$END}

Thank you in advance for all the help :)

Answer 1

Why wouldn't you store all the query's result into an array, and print only the 10 first, then [11-20]. (10 is the arbitrary number of results showed)

Something like

 for($i = ($page - 1) * 10; $i <= ($page * 10); i++)
{
  echo $result[i]['season'];
  // other things
}