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.

Student Suspended Over Suspected Use of PHP

I know this is old, but I have loved it for years. I try and share it every so often, just for those who have never seen it before. Today I found the original here on BBspot.

Jeremy


Wednesday  June 14 10:10 PM EDT

Student Suspended Over
Suspected Use of PHP

By Brian Briggs

Topeka, KS – High school sophomore Brett Tyson was suspended today after teachers learned he may be using PHP.

“A teacher overheard him say that he was using PHP, and as part of our Zero-Tolerance policy against drug use, he was immediately suspended. No questions asked,” said Principal Clyde Thurlow.   “We’re not quite sure what PHP is, but we suspect it may be a derivative of PCP, or maybe a new designer drug like GHB.”

php_logoParents are frightened by the discovery of this new menace in their children’s school, and are demanding the school do something.  “We heard that he found out about PHP at school on the internet.  There may even be a PHP web ring operating on school grounds,” said irate parent Carol Blessing.  “School is supposed to be teaching our kids how to read and write.  Not about dangerous drugs like PHP.”

In response to parental demands the school has reconfigured its internet WatchDog software to block access to all internet sites mentioning PHP.  Officials say this should prevent any other students from falling prey like Brett Tyson did.  They have also stepped up locker searches and brought in drug sniffing dogs.

Interviews with students suggested that PHP use is wide spread around the school, but is particularly concentrated in the geeky nerd population.  When contacted by BBspot.com, Brett Tyson said, “I don’t know what the hell is going on dude, but this suspension gives me more time for fraggin’.  Yee haw!”

PHP is a hypertext preprocessor, which sounds very dangerous.  It is believed that many users started by using Perl and moved on to the more powerful PHP.  For more information on how to recognize if your child may be using PHP please visit http://www.php.net.

MySQL Query Time and Counter

It has been a little while since I made an entry, but there is good reason for that. I have been hard at work redesigning my Gallery layout for my picture galleries on My Air Force Life. The main thing I have been pushing for was integration into MySQL. There are obvious benefits of switching to that rather than just having static pages, but that is not the point of this entry.

The reason for this entry is to show how I did the little counter at the bottom of the pages. You know the ones…

Page created in x.xxx seconds with X queries.

Well I was wanting to figure out how to get this to work on my site. Part of the reason, is I am using my laptop as a testbed, so I want to see how resource intensive some of my queries may be. This is my first time actually developing something with MySQL, rather than editing existing code, and I wasn’t sure that my queries were going to be all that resource friendly.

Well to start since I will be doing many different queries on many different pages, I felt that I needed to create a function in a separate file that I would just include on my other files. I called the file func.query_time.php

Within that file, I had two separate functions. One which will time the query, add it to the total time, and add the amount of queries. The second function is just to display the actual info at the bottom of the pages. I will go through these functions separately, so people understand how they work. But I will have everything all together at the bottom.

So to start out we need to declare the function and we are calling it query.

function query($sql, $querycount, $totaltime)
{
   if (empty($querycount))
      $querycount=0;

   if (empty($totaltime))
      $totaltime=0;

All this does is recieve the query ($sql) and the querycount and totaltime amounts. And if the latter two were not passed when the function was called then it automatically sets them at zero. Next we need to get the time it takes to process the query.

   list($usec, $sec) = explode(' ',microtime());
   $querytime_before = ((float)$usec + (float)$sec);

   // run the query
   $result = mysql_query($sql);

   list($usec, $sec) = explode(' ',microtime());
   $querytime_after = ((float)$usec + (float)$sec);

The first part grabs the time before the query was run and stores it in $querytime_before. Then we actually run the MySQL query, then we get the time again and store it in $querytime_after. Now we need to get the totals of everything.

   $querytime = $querytime_after - $querytime_before;
   $totaltime += $querytime;

   $querycount++;

The first line take the time before and subtracts it from the time after which gives us the time it takes to make that query. Then we add the $querytime to $totaltime (the += is for adding another variable to the one it will be stored in ie. $totaltime = $totaltime + $querytime;). The last line is to add 1 to the querycount. The final part of this function is to return the info so you can use it on your regular page.

   return array($result, $querycount, $totaltime);
}

This returns the results back into the orginal file and closes out the function. Now onto the file itself and how you call the query.

Now the first time you call the function “query” there will be no variables set for $querycount or $totaltime, so you can just leave those out, but those variables will be returned. And since they are returned in an array there are a couple of ways to get them out. I prefer the list() function as it is simple and you can name the variables however you want.

   "sql = SELECT * FROM database";
   list($result, $querycount, $totaltime) = query($sql);

So the $sql query we have stored above is being passed to the function on the right, and then it runs through the function and returns us our info. On the second query it is only slightly different.

   $sql = "SELECT * FROM database WHERE id = 1";
   list($result2, $querycount, $totaltime) = query($sql, $querycount, $totaltime);

In this one we need to pass the $querycount and the $totaltime to the function. Otherwise it won’t know the numbers. Also note that you should change the variable for the MySQL query result depending on how your page is setup.

Now we have the displaying of the info. In the func.query_time.php file I had a second function declared. This function is small enough I won’t break it down.

function display_time($querycount, $totaltime)
{
   $strQueryTime = 'Page created in %01.4f seconds';
   echo '

' . sprintf($strQueryTime, $totaltime) . ' with ' . $querycount . ' queries.

}

So the function gets the querycount and totaltime variables passed to it. Then we store the string for the page created in x seconds into $strQueryTime. The %01.4f is used to shorten up the totaltime. Right now it will pad any unused spaces with 0’s and will go four decimal places to the right. We then use the sprintf function to display that with the totaltime added in and then the total number of queries that were counted. All that is left to do is show that on the page. This is done with

display_time ($querycount, $totaltime);

Now to show it all together. First the php file followed by the function file.


' . sprintf($strQueryTime, $totaltime) . ' with ' . $querycount . ' queries.

'; } ?>

So there you have it. Any questions or things you believe I could do better, let me know in the comments. And I am sure that there will be more code that I will post before the gallery overhaul is complete. Stay tuned…

Bash Thumbnailer Script with Imagemagick

A while ago, I was working on creating a script to create thumbnails of all the pictures I had taken so I could put them up on my personal site for family/friends to view. It started out as a very basic script but as I developed my website, I found new ways to display the photos. Namely using css for the image to come up double sized by mousing over on the picture. Well this created quite a challenge to put all the code for my pictures in the HTML. So I went a step further and adjusted the script to create the php necessary for the galleries. The way I create all my pages is to use a standard header and footer file and include them into my individual pages. So I then changed the script so it created a full blown page ready to be used. The script is below. You may have to modify it slightly for your own needs, but it should work fine. The main requirement is the imagemagick software. In Slackware it is included with the default install. With your own distro you may need to add it yourself.

[code=’bash’]
#!/bin/bash

echo “Resizing Pictures and Creating PHP”
mkdir thumbs previews view
cp ../rhine-river/view.php .
cp ../rhine-river/download.php .

totalcount=0
currentcount=0

if [ $1 = y ]
then
name=$2
picnum=0
for i in *.jpg *.JPG; do

picnum=$(($picnum + 1))

pic=`echo $i | awk -F . ‘{print $1}’`
rename=”${name}-`printf “%03d” $picnum`.jpg”
mv “$i” $rename

done
fi

for i in *.jpg; do

totalcount=$(($totalcount + 1))

done

echo “There are $totalcount pictures to convert.”

# Create the header
echo \<\?php > index.php
echo \$pagetitle \= \”Insert Title Here\”\; >> index.php
echo include_once\(\”../../includes/header.php\”\)\; >> index.php
echo ?\> >> index.php
echo \Page Description Here\ >> index.php
echo \