[Release] Smilies Pagination Hack

Well, here is my first vB hack. I have not cut any code for a long time so that if anyone wants to clean up my PHP, please feel free to comment. I do wonder if there is a way to get the total number of records without a second query; I thought of counting to see if fewer than 30 (my page limit) smilies were returned, but then I would have to give up the "First to Last of Total" report.

I added the following variables: $startsmilies, $smilieslink, $linkstartsmilies, $smiliescountresult, $smiliescountrow and $smiliescount

Any way:

Replace the section in index.php (the main one, not the /admin/ one) that deals with "action=showsmilies" -- I have not posted it here as all my code has been added between the start and end of the section so that it should be simple for you to find (lines 44 to 56 in an un-hacked index.php).

if ($action=="showsmilies") {

// 2000-07-26 PEH - hacked to add LIMIT clause to break up ShowSmilies page
  if (!isset($startsmilies)) {
    $startsmilies=0;
  }

// added LIMIT statement, hard coded "30" as the per page value
// (should move this to a variable in the control panel, though)
  $smilies=$DB_site->query("SELECT smilietext,title,smiliepath FROM smilie ORDER BY title LIMIT $startsmilies,30");
  while ($smilie=$DB_site->fetch_array($smilies)) {
    $smilietext=$smilie[smilietext];
    $smiliepath=$smilie[smiliepath];
    $title=$smilie[title];

    eval("\$smiliebits .= \"".gettemplate("smiliebit")."\";");
  }

// $smilieslink sets up the page links, if needed
  $smilieslink = "";

// display a link to the previous page, if needed
  if ($startsmilies>29) {
    $linkstartsmilies=$startsmilies-30;
    $smilieslink .= "<a href=\"index.php?action=showsmilies&startsmilies=$linkstartsmilies\">Previous</a> / ";
  }

// $smiliescount is the total number of smilies in the database
  $smiliescountresult=$DB_site->query("SELECT COUNT(*) AS Total FROM smilie");
  $smiliescountrow=$DB_site->fetch_array($smiliescountresult);
  $smiliescount=$smiliescountrow["Total"];

// display the current range for information only
  $smilieslink .= "Smilies " . ($startsmilies+1) . " to " . min(($startsmilies+30),($smiliescount+0)) . " of " . $smiliescount;

// display a link to the next page, if needed
  if ($startsmilies<$smiliescount-30) {
    $linkstartsmilies=$startsmilies+30;
    $smilieslink .= " / <a href=\"index.php?action=showsmilies&startsmilies=$linkstartsmilies\">Next</a>";
  }

  eval("echo dovars(\"".gettemplate("smilies")."\");"); // added line to "smilies" to show link to previous and/or next page
}

In the "smilies" template you have to add the "$smilieslink" variable wherever you want your Previous/Next links to appear. I put mine after the table closes but before the centering closes:

• $smiliebits
  </table>
  $smilieslink
  </center>

Peter E. Humphries

[Edited by phumphries on 07-26-2000 at 01:14 PM]

$smilies=$DB_site->query("SELECT smilietext,title,smiliepath,COUNT(title)as Total FROM smilie ORDER BY title LIMIT $startsmilies,30");

You should be able to do that and the result set would have the total smilies in the "Total" field. I am not sure if the count will be set to 30 by the LIMIT statement though. Hmm?

[Edited by rangersfan on 07-26-2000 at 01:28 PM]

I will have to try that, again. I did try "COUNT(*) AS Total" and received a nice little "Your database is having problems" type error.

Peter E. Humphries

That should work also.

It is valid SQL syntax as I have this code in one of my files:

 "SELECT post.userid, count(post.userid) as count, user.username from " . 
 "post, user WHERE post.dateline >= $date1 and post.dateline <= $date2 " .
 "and post.userid = user.userid and user.username = 'freddie' group ".
 "by post.userid order by count DESC");

Well, I have not tried to change the query, although it was with that intention that I openned the script for modification. I ended up adding a much nicer navigation bar.

To use it, find your favourite First, Previous, Next and Last graphics and replace nav_first_on.gif, nav_first_off.giff, nav_prev_on.gif, &c, in the following code. I added one more variable, $smilieslinklast, to determine whether or not to enable the navigation for the last page button.

if ($action=="showsmilies") {

// 2000-07-26 PEH - hacked to add LIMIT clause to break up ShowSmilies page
  if (!isset($startsmilies)) {
    $startsmilies=0;
  }

// added LIMIT statement, hard coded "30" as the per page value
// (should move this to a variable in the control panel, though)
  $smilies=$DB_site->query("SELECT smilietext,title,smiliepath FROM smilie ORDER BY title LIMIT $startsmilies,30");
  while ($smilie=$DB_site->fetch_array($smilies)) {
    $smilietext=$smilie[smilietext];
    $smiliepath=$smilie[smiliepath];
    $title=$smilie[title];

    eval("\$smiliebits .= \"".gettemplate("smiliebit")."\";");
  }

// $smiliescount is the total number of smilies in the database
  $smiliescountresult=$DB_site->query("SELECT COUNT(*) AS Total FROM smilie");
  $smiliescountrow=$DB_site->fetch_array($smiliescountresult);
  $smiliescount=$smiliescountrow["Total"];

// display the current range for information only
  $smilieslink .= "Smilies " . ($startsmilies+1) . " to " . min(($startsmilies+30),($smiliescount+0)) . " of " . $smiliescount . "<br>";

// display a link to the previous page, if needed
  if ($startsmilies>29) {
    $linkstartsmilies=$startsmilies-30;
    $smilieslink .= "<a HREF=\"index.php?action=showsmilies&startsmilies=0\"><img src=\"images/nav_first_on.gif\" border=0 alt=\"First Page\"></a>";
    $smilieslink .= "<a HREF=\"index.php?action=showsmilies&startsmilies=$linkstartsmilies\"><img src=\"images/nav_prev_on.gif\" border=0 alt=\"Previous Page\"></a>";
  } else {
    $smilieslink .= "<img src=\"images/nav_first_off.gif\" border=0 alt=\"At first page, already\">";
    $smilieslink .= "<img src=\"images/nav_prev_off.gif\" border=0 alt=\"At first page, already\">";
  }

// display a link to the next page, if needed
  if ($startsmilies<$smiliescount-30) {
    $linkstartsmilies=$startsmilies+30;
    $smilieslink .= "<a HREF=\"index.php?action=showsmilies&startsmilies=$linkstartsmilies\"><img src=\"images/nav_next_on.gif\" border=0 alt=\"Next Page\"></a>";
  } else {
    $smilieslink .= "<img src=\"images/nav_next_off.gif\" border=0 alt=\"At last page, already\">";
  }

// add the "Last Page" link
    $smilieslinklast=$smiliescount-$smiliescount%30;
  if ($smilieslinklast>$startsmilies) {
    $smilieslink .= "<a HREF=\"index.php?action=showsmilies&startsmilies=" . $smilieslinklast . "\"><img src=\"images/nav_last_on.gif\" border=0 alt=\"Last Page\"></a>";
  } else {
    $smilieslink .= "<img src=\"images/nav_last_off.gif\" border=0 alt=\"At last page, already\">";
  }

  eval("echo dovars(\"".gettemplate("smilies")."\");"); // added line to "smilies" to show link to previous and/or next page
}

Further to the COUNT() problem, I tried all sorts of combinations to no avail. However, the mySQL manual states that running "SELECT COUNT(*) AS Total FROM smilie" is optimized to return the fastest possible result so that it is, in any case, the right way to get the value.

Ah, well.

rangersfan: I thought that you might, for interest's sake, like to know that my COUNT() error was due to not having a GROUP BY clause. To quote:

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

A bit of an embarrassingly silly error, but I did end up using the most efficient code, any way.

I still don't see how running two queries is going to be faster than putting the count with a group by into one query.

I read that statement to mean that using count(*) is going to return the fastest answer as opposed to running count(some field name). Which I think doesn't imply that you should use 2 queries but you should use count(*) with the group by to achieve the fastest answer as opposed to doing two select statements.

(make any sense?)

[Edited by rangersfan on 08-10-2000 at 04:50 PM]

Well, I have a couple of little hacks to do for my Intranet people -- while I have the source open, I will try it out and let you know what blows up.