I spent the past two days trying to revamp the new Tabulas entry display engine. The one I'm using right now is really outdated and has been hacked so many times it's rather inefficient.

The old method used one massive function; you would pass a variable $pagetype (like $pagetype == "main" or $pagetype == "friends"), which the function would then read, extract the proper SQL statement and execute.

Now the problem here was that there was no real method of doing a prior check to see whether friends-only and private entries should be extracted. They aren't being displayed, but you'll notice on some of your friends page (or even your main page), if you don't have the proper permissions, you'll notice that less than X (X being the number of entries per page you want to display) entries are being displayed.

This was a big no-no. So I decided to try to write a new method of handling the entry display.

It took me two days, but finally I'm on the right track. I knew that with the new system, I would have to have two separate functions: the first one would take the pagetype and then determine which entryids to display, calculating the status of both the entry and categories. The second function would accept an array of entryids and then display those entryids.

The second function was rather easy to write since most of it was prewritten. However, the first function gave me a lot of trouble.

Originally I was using a rather simple SQL statement like :

SELECT * FROM entry WHERE userid = '$userid' ORDER BY timestamp DESC LIMIT 5

I would then loop through each data, checking to see if the entry should be viewable to whoever was trying to view it. But the problem as I mentioned above is that this doesn't take into account entries that are viewable. So on a given page, if an entry that was selected was unviewable to you, then you would see 4 entries instead of 5.

So the logical next step was to try to build a really long SQL statement (hitting the DB only once would be preferable) that would look like this:

SELECT * FROM entry WHERE (userid = '4' AND status = 0) OR (userid = '5' AND status <= 1 AND status >= 0) ORDER BY TIMESTAMP DESC LIMIT 5

This would be generated by looping through the friends table and determining whether each user was allowed to view public entries (status = 0) or also friends-only entries (status <= 1 AND status >= 0). (as a side note, "Community Postings," or "Shared Journal Only" postings have a status of -1).

This seemed to work nice, except when I analyzed the EXPLAIN SQL in mySQL, this SQL resolved horribly. Optimization is the key when you're running a site with thousands of users ... even the slightest bottleneck can balloon out of control and take the whole site down (which happened with AudioMatch before I figured out how to manage bottlenecks).

So the compromised solution is to do a while ($i < $viewable) loop that goes through the entry table once, determines which entryids are viewable on a entryid by entryid basis, and then ships it to the second function for output. The SQL statements are kept rather optimized ... so it's all good. I'm doing a fair share of memory caching on a per-request basis (esp on the friends page; whether you're a friend of somebody is stored in memory while the script executes so it doesn't keep hitting the database).

This was a rather simple solution in retrospect, but the time and experimentation it took to get there took 2 days. Now that I *have* a solution, it's a matter of actually scripting the whole thing ...