On Friday, June 7, 2002, at 03:06 AM, rory oconnor wrote:
> I have a mysql database table of about 7000 images (they are not
> actually in the db, just references), as yet uncategorized. I need to
> put them online and allow my client to browse 12-18 thumbnails per page.
>
> what would be the most efficient method to code that with PHP? any help
> is appreciated!
First a disclaimer -- what I am about to tell you may sound very
complicated, but only because it's hard to explain. Really, the concept
is simple once you "get" it.
Some people have said look into the LIMIT clause in a MySQL SELECT
query. This is the right direction, but specifically you need to use
the (implicit) OFFSET argument to LIMIT.
Fundamentally, what you do is this:
You perform a SELECT as normal, which would return all results from your
database, but you use LIMIT 0, 10 which means "Limit it to 10 records
starting at the first one". [Yes, it's weird since your row's primary
key column probably starts with 1, but if you think about it it's not
really weird, MySQL doesn't care what value is in your row's primary key
column. The first record/row is 0.]
The offset is going to be passed via a GET variable, so your query will
look something like:
SELECT row_id,
row_content
FROM table
WHERE where conditions here
LIMIT $offset, 10;
This is because if the person clicks the "next 10 rows" link, which you
will provide, this link will have a querystring attached that looks
something like "?offset=10" or whatever offset it is.
So how do you implement this into a PHP script? Well, sure there's a
lot of ways to do it but essentially, you do this:
On the first page, you have two queries -- first a COUNT to see how many
records are in the table, and second the above SELECT query that does
the work you want done (getting the information).
Now, using the first query's results (which are nothing more than the
number of records in the table), you figure out how many more rows are
left AFTER the offset that you are at. If this is the first page, and
you are displaying ten results on the first page, then simply subtract
ten from the total. If this is the second page, you have now displayed
twenty records, so subtract twenty. Save this value as a variable like
$rowsLeft or something. Also, you want to save the number of rows
PREVIOUS to your current offset, which for the first page would be 0 but
for the second page would be 10, and for the third page would be 20.
Get it? This is how you will make a link that says "next 10 thumbnails"
or "previous 10 thumbnails" or whatever.
Next step is easy: you simply display the data however you wish to do
it. I'm not going to tell you how to do this because it's the same way
you'd display the data normally.
Finally, you need to make the links to go backward or forward. This is
the whole trick of the operation. You make a link to the page's self,
but you pass along the offset so that the next query knows how to build
the proper SELECT statement to return the -next- ten rows, etc. Like
this:
print("<a href=\"" . $_SERVER['PHP_SELF'] . "?offset=" . $offset .
"\">Next 10 Thumbnails</a>");
Of course, the above link is really too simple. You would want to
determine the number of rows that will be returned, which will usually
be 10 but not always -- on the second to last page, if there are only 4
rows left to display, you don't want to say "Next 10 thumbnails" if
there are only 4 left. So you need to do a simple bit of processing to
get the number of rows left and print that value.
Do the same for the "previous 10 thumbnails", although if you do this
right you will always have a value of 10 in this section. (When would
you ever end up at an offset that is not a multiple of 10?)
That's all there is to it. It's complicated at first, but once you get
it, you'll always be able to do it. I strongly recommend that you
create a class called "ThumbnailResults" and store all of this logic in
the class, since it'll keep your script cleaner.
Good luck,
Erik
----
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
