MySQL Results in Multiple Pages

In working on my new site layout, one thing I was wanting is when there are a lot of results (read, pictures in a particular gallery), is to split the results up into pages. Currently I just have it set to 12 pictures (and the pictures go 4 wide), so after 3 rows, I wanted it to have a link to a new page. I also wanted links to each individual page (this may change in the future when there are too many pictures in one gallery and there ends up being 30 some odd pages, but I will change it when it happens).

So to start out, I will assume you already have some basic things set up with your php document. One would be the correct variables passed through the address bar, another is a database setup with enough rows to have enough results to go into multiple pages.

So we first need to start with an sql query to find out how many results we will be dealing with. This gets us one of the primary numbers we need to start the calculations. In my db, I have a pic table that list all the info for all the pictures with a cell for it to link to the album table. In my address bar it passes the album id, which I then store in the $album variable.

// Specify the maximum amount of results per page, you could add a drop down box where the end-user
// can change it themselves
$max_pics=12;

// Pretty straightforward mysql query
$sql="SELECT count(id) AS count FROM pic WHERE album=$album";
$result=mysql_query($sql);
$pic_count=mysql_fetch_assoc($result);

// This is part of the magic. First thing we do is take the amount of results returned ($pic_count['count'])
// and then we divide it by the maximum amount of pics allowed per page.

// Then we run the ceil function on the result which will round the answer up to the next whole number.
// This will give us the total number of pages that all the results will take.
$total_pages=ceil($pic_count['count']/$max_pics);

So the above gets us a starting point with the total amount of pages that will display with the results and the maximum number of results we want on a page. The next process is to get the page number that we are currently on. Currently I am just doing this with a $_GET from the address bar (so the address would read index.php?page=2), but I may choose to redo this in the future to be more Search Engine Friendly.

We first need to do some error checking

// Check to make sure a page was passed and it is a number, if not set the page to the first one
if (isset($_GET['page']) && is_numeric($_GET['page']))
	$cur_page=$_GET['page'];
else
	$cur_page=1;

// Now we need to verify that the page is within the range of pages that we have, if not set to the first one
if ($cur_page < 1 || $cur_page > $total_page)
	$cur_page=1;

Now we need the mysql query that will get us just the results we want for the page. My query is pretty long, so I will be just putting in a simple query that will get you the results you need.

// First we need to get the starting result id, this is simply done by getting the current page minus one
// and then multiplying that by the maximum results you chose.
$start_result = ($cur_page - 1) * $max_pics;

// Next comes the actual query
$sql="SELECT * FROM pic WHERE album=$album LIMIT $start_pic, $max_pics";
$result=mysql_query($sql);

Now we need the html code to display the pages and the links to the pages. I haven’t done much in the way of css or making it look nice, but it is still functional.

// First display the mandatory html, along with the current page, and how many total pages there are
echo '
Page: ' . $cur_page . ' of ' . $total_page . '

'; // Now if there is more than one page create the links for the other pages if ($total_page > 1) { echo ' '; } // Then close the div for the page info echo '
';

I think that is all of the pertinent code out of my page. I will look over this when I get home so I can check and verify this all works properly. Hopefully I won’t have to make any changes.

WordPress 2.7.1 Upgrade Failed

If you want the quick fix (that worked for me) scroll to the bottom.

I noticed last night at work that 2.7.1 was available to upgrade. So I clicked on the link to upgrade it and it never proceeded past the downloading wordpress-2.7.1.zip. I figured it might be a problem because of the severe web-browsing restrictions my work has or the possibility of me running IE. I figured I would just do it when I got home. I did think this was kinda weird because I was able to update one of my plugins without a problem.

So I got home and the link still wouldn’t work. I then ssh’ed into my server (hosted with 1&1) and found that multiple copies of the wordpress-271.zip were saved in my wp-contents folder (additional numbers were added before the extension) all at 0 bytes. So my next step was to google the problem. Unfortunately with this being a new release, not much was indexed at that point. I then went onto wordpress’s support forum and found that some people were having the same issue, but no one had posted a response. In one of the threads that I looked at (which ended up being unrelated to my issue) one of the posters had asked if the topic starters host was listed in the Core Update Host Compatibility list that wordpress had put out. I had never even heard of this list, so I figured I would look through it. It showed 1&1 under the “Works with caveats” section. It mentioned that the upgrade fails with PHP4, but works properly in PHP5. I thought I was already using PHP5, but I figured lets give it a shot. The instructions are very simple.

Go to the root of your wordpress installation and edit the .htaccess file (you might have to FTP in and copy the file to your machine and make the changes then upload it back). Add AddType x-mapp-php5 .php to the end of the file (you can put it above the # END WordPress comment if you like, but it won’t matter either way).

Maybe this will make it so I can install plugins through the dashboard (they would always fail before), but I will look into this later.

EDIT: I just checked and I am now able to install plugins through the dashboard.

EDIT2: As others have mentioned in the comments, the WordPress Automatic Upgrade plugin is incompatible with WordPress 2.7+. All the functionality (that I used at least) has been integrated directly into WordPress. As such, I had already disabled mine, but if you have not disabled it, you will run into issues with upgrading. Your best option is to Deactivate the plugin. You can then ftp/ssh in and delete the directory (not necessary, but I see no reason to keep the plugin). After that, you should be able to upgrade without a problem.

UPDATE (20 FEB 2009): The author of the Worpress Automatic Upgrade plugin has released a fix for the incompatibilities with 2.7.1. I am not sure if the plugin has any benefit over the built-in upgrade feature, but the fix should at least fix issues with the upgrade process. More details here.

Also for those of you who can’t find your .htaccess file. Under most webservers, a period (.) at the beginning of a file makes the file hidden, so you need to be able to view hidden files. If it still doesn’t show up, it may not exist. If not you can just create it with your favorite text editor (do not use a file extension), and add the information and upload it to your server.