user2381309 user2381309 - 3 months ago 9
Ajax Question

Password Protected Photo Album PHP/MySQL Server Load

I am making a site that will allow users to share a searchable photo album with thumbnails that will be protected with a password. My plan was to authenticate the password and set the PHP session equal to the id of the photo album. Then when the user loads the album, the server will provide a list of all the pictures reference id's. Using ajax calls the client will then request all the picture that meets the request criteria with GET requests including photo id. Then using PHP I will check if the current session is authorized for that album and then make a MySQL query for the pictures location and then return the picture.

Now that you have read my design idea, I am concerned that the server loading will quickly increase if an album is sufficiently large because every thumbnail will require a separate request and MySQL query. I have yet to implement and test but wanted to know if I should start over with my design.

Is there a better way to go about this?

Will the loading from a simple PHP script and MySQL query be very big?

Answer

There's no reason why each individual thumbnail will require a separate request or even a separate query. You can do both in a single request and a single query.

Imagine your thumbnails are stored as URLs in your database and associated with the album Id. This means that a single query like this can retrieve all thumbnails for the album.

SELECT
    thumbnails.url
FROM
    thumbnails
JOIN
    albums ON thumbnails.album_id = albums.id
WHERE
    thumbnails.album_id = 1

Now you can simply paginate these results to reduce server load. Let's say you want to load at most 50 thumbnails per page.

SELECT
    thumbnails.url
FROM
    thumbnails
JOIN
    albums ON thumbnails.album_id = albums.id
WHERE
    thumbnails.album_id = 1
LIMIT
    0,50

Then for the next page you can do LIMIT 50,50, and the next page LIMIT 100,50 and so and so forth.

You can send the list of thumbnail results back to the client in your AJAX response as JSON, and the client can load each thumbnail onto the page via javascript.

Why you probably think this won't work

If your thinking is that you need to have an individual request for these thumbnails, because you want PHP to verify that the user has access to view the album, then you're trying too hard to solve two many problems at once.

The problem of getting the thumbnail URLs to the client is one step. That's easily accomplished by checking the user's $_SESSION, just like you plan on doing, and making sure they have access to the album_id before you do the query and send them back the JSON result.

The next step is restricting access to the album and its thumbnail images. You have to do a separate request for this in HTTP/1.1, unfortunately. There's no way around that, but this still need not be expensive.

You simply only use PHP to verify that the $_SESSION variable contains the required album_id check and rely on your webserver's Sendfile capabilities to deal with serving up the actual image to the client.

So PHP just handles the part of verifying that the user has access to view the image. And your webserver handles the part of serving it up. There's no database query to be performed in this request. So it isn't a database load concern.

In PHP, using Apache httpd (as an example), this would work something like this.

if (isset($_SESSION['album_id']) && $_SESSION['album_id'] === $_GET['album_id']) {
    // User has access
    header("X-Sendfile: " . $pathToImageOnFileSystem);
    exit;
} else {
    // User is not authorized :/
    echo "Unauthorized access!";
}

What this does is send a response header back to your webserver to let it know "you need to serve up the file specified in $pathToImageOnFileSystem to the client". PHP's role ends after sending that header back to your web server. The webserver then does the rest. So PHP is not tied up.

The tricky part is to make sure you define your webserver rewrite rules in a way that points the URL to the image Id where you can map it to a file on your filesystem. For example, you could store the images in /home/myapp/images/{album_id}/{image_id} so that a URL like http://www.example.com/images/1/1 gets read by your web server and sent to PHP then PHP can determine that $pathToImageOnFileSystem should be /home/myapp/images/1/1. PHP already knows if the user has access or not by checking the albumb_id is in the $_SESSION as it would have been stored them from prior authentication.

Of course, there's also making sure that the file sends back the correct mime type and file extension when served up by your webserver. So you may want to map /home/myapp/images/{album_id}/{image_id} to /home/myapp/images/{album_id}/{image_id}.* or something similar to that.