Effective paging with mysqli + bind_result

knowj

I have been looking around recently for a decent paging class to work with mysqli and mysqli stmt.

Currently I have a method such as:

public function getAll()
		{
			if ($stmt = $this->db->prepare("SELECT `id`, `name`, `url`, `order` FROM `categories` ORDER BY `order` ASC"))
				{
					$stmt->execute();
					$stmt->bind_result($this->id, $this->name, $this->url, $this->order);
					while ($stmt->fetch())
						{
							$entry[] = $this->objectToArray();
						}
					return $entry;
				}
			errorLog($this->db->error.$stmt->error);
		}

$this->objectToArray() just generates an array of the object variables which is pushed through to what is essentially the controller -> view.

I have been thinking what the most effective way of paging my results would be. I could easily do it at array level but it would be a huge waste of resource pulling out 500+ rows then to only use 30 of them. But the bit I am struggling with is how to get the paging data into query level without having to write into each query the paging results.

Ideally I would extend the database class and call the paging object rather than db
if ($stmt = $this->paging->prepare("SELECT id, name, url, order FROM categories ORDER BY order ASC"))

and extend the prepare method to include the paging additions for the query.

Any input would be greatly appreciated

Cheers
J

knowj

bump

laserlight

In the case of MySQL (and some other database systems that support the syntax), you can use a LIMIT clause to retrieve those results that belong on a single page. You can use PHP to compute the offset and number of rows to limit by based on the page number and number of entries per page.

knowj

That's the easy bit. The thing I'm struggling with at the moment is paging without having to manually write in a limit and offset into each query.

Ideally I would extend my database/mysqli class with a paging class that would perform all the calculations for the paging and append a limit/offset onto the query.

laserlight

knowj wrote:
Ideally I would extend my database/mysqli class with a paging class that would perform all the calculations for the paging and append a limit/offset onto the query.

On the contrary, that will not be ideal. Why subclass when all you want to do is augment some existing functionality? Just write a function that will "write in a limit and offset into each query".

mj22pk

Hello, I have the same problem and wonder how did you solve this?