NYCPHP Meetup

[Mike Myers]

First: I am a newcomer to MySQL and PHP. So I will be asking many newbie
questions, which I hope is not a drag!

Environ: Macintosh G4 running OSX, MySQL and PHP installations provided by
the inimitable Marc Liyanage. Webserver is Apache.

Experience: Proficient with UserLand Frontier scripting and webserving;
moderate experience with perl and javascript. PHP looks pretty
straightforward.

I have already built a few MySQL databases from scratch, so I have some
experience with the mysql client, mysqladmin, and mysqlimport. Lately I have
also been using the web-based frontend phpMyAdmin.

----

What are the implementation strategies for allowing a user to page through a
result set that is too large to view in its entirety?

I see that for simple queries (eg. 1 table or 1 join), the LIMIT statement
is the easy solution, which entails re-running the SQL query as the user
browses.

But what if the query has multiple joins? It seems inefficient to re-run the
query each time. If I want to cache the original result in a new MySQL
table, then I have to manage that on a per-user basis. This implies using
cookies or session ID to track the user.

This also suggests I need to code a separate process that periodically drops
these temporary result tables after a defined time has passed. Thus, I need
to continually track the time of last access to each temp table. That data
could go in another MySQL table, or a file of my own convention.

Whew! There are probably other aspects of this arrangement that require
management code.

Am I on the right track here, or am I making it harder than it needs to be?

-- mike myers