Paging with PDO

[telmessos]

Hi,

I have an MySQL query which is seems to work fine with a PDO execute and I want to make paging to the results. I want to show the total number of results also. If I make paging by adding "limit :rownumber, :rowperpage" to the end of the query, I have to execute another query to get the total count number. If I don't, I don't know how to make paging. Any ideas?

$searchquery = "Select propertycode,title,price,currency,altbolgeadi,bolgeadi,description,bedrooms,bathrooms from urunler where bolgeID=:location and altbolgeID=:sublocation and price between :pricemin and :pricemax limit :sayfa, :gosterim";
$stmt = $db->prepare($searchquery);
$stmt->execute($degerler);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rowcount = count($rows);

In above code, if I keep the limit section $rowcount becomes the limit given to MySQL. If I don't, I don't know how to make paging with the $rows results.

Many thanks

[TFlan]

Just use math to manipulate the row index you are trying to achieve (i believe this is what you're trying to do).

So if your limit $startLim, $endLim, for example $startLim = 30; $endLim = 50, that gives you 20 rows, to get $i to start at 30, $i = $startLim - which lets you use loops easier

Again - Not sure what your problem is

[telmessos]

Thanks for your reply. So correct me if I misunderstood. I must create a counter variable increasing by 1 at every step of the loop. When the counter number matches the page number, it starts writing on the page until the numberofrows per page number reached. This seems like a good way to do it.

But there's also a concern of performance. Let's say if the total number of records are 200, all of them will be brought from MySQL which is a cost. On the other side if I add a limit to the end of the SQL query, only the rows per page will be displayed.

So still could not decide. Anyway thanks again for your response.