Alphabetical Pagination #'s | A | B |...| Z

I am looking to make an alphabetical index type pagination script that alphabetizes my MySQL table results according to letter or number and then if too many results are for a particular page (>say 50/page), it will paginate it out accordingly (A1, A2 etc.). An example visually would be like this:

Symbols | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z
<< | 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | >>

Now I have a few pagination scripts that work for the page part
(<first… 2 3 4 …last> part but doing the alphabetical part makes my head spin. I think using simple HTML links that pass a variable via the GET method via the URL string such as

[http://www....details.php?var=A](http://www....details.php/?var=A)

might be part of the solution but really I am at a loss on how to do this.

Hopefully some of you have some insight on how to accomplish the MySQL select queries (where name like %A%) and the actual pagination code.

I have working numerical pagination code that I can post as a starting point if needed. If anyone requests this I will do a follow-up post.

Thank you!

Take a look at ord() and chr(), lets you turn numbers to letters and letters to numbers.

http://www.php.net/ord and http://www.php.net/chr respectively.

You could foreach() over a range(‘A’, ‘Z’) array to create the links and have each link goto a ?var=B type url. Then have the mysql do a “… WHERE foo LIKE '%”.$_GET[‘var’].“'”; type thing. A bit vague but you might get the general idea :o


$cnx = @mysql_connect('localhost','username','password'); 
mysql_select_db('database',$cnx); 
 
$query .= "SELECT DISTINCT games.id, games.esrb, games.genre, ";
$query .= "games.title, games.platform, games.release_date, games.dev, ";
$query .= "developers.did, developers.developer_link, developers.developer ";
$query .= "FROM games, developers WHERE games.dev = developers.did ";
 
 if($_GET['letter']=="num") {
  $query .= "AND title REGEXP '^[0-9]+'"; 
 } elseif($_GET['letter']=="a") {
  $query .= "AND title LIKE 'a%'";
 } elseif($_GET['letter']=="b") {
  $query .= "AND title LIKE 'b%'";
 } elseif($_GET['letter']=="c") {
  $query .= "AND title LIKE 'c%'";
 } elseif($_GET['letter']=="d") {
  $query .= "AND title LIKE 'd%'";
 } elseif($_GET['letter']=="e") {
  $query .= "AND title LIKE 'e%'";
 } elseif($_GET['letter']=="f") {
  $query .= "AND title LIKE 'f%'";
 } elseif($_GET['letter']=="g") {
  $query .= "AND title LIKE 'g%'";
 } elseif($_GET['letter']=="h") {
  $query .= "AND title LIKE 'h%'";
 } elseif($_GET['letter']=="i") {
  $query .= "AND title LIKE 'i%'";
 } elseif($_GET['letter']=="j") {
  $query .= "AND title LIKE 'j%'";
 } elseif($_GET['letter']=="k") {
  $query .= "AND title LIKE 'k%'";
 } elseif($_GET['letter']=="l") {
  $query .= "AND title LIKE 'l%'";
 } elseif($_GET['letter']=="m") {
  $query .= "AND title LIKE 'm%'";
 } elseif($_GET['letter']=="n") {
  $query .= "AND title LIKE 'n%'";
 } elseif($_GET['letter']=="o") {
  $query .= "AND title LIKE 'o%'";
 } elseif($_GET['letter']=="p") {
  $query .= "AND title LIKE 'p%'";
 } elseif($_GET['letter']=="q") {
  $query .= "AND title LIKE 'q%'";
 } elseif($_GET['letter']=="r") {
  $query .= "AND title LIKE 'r%'";
 } elseif($_GET['letter']=="s") {
  $query .= "AND title LIKE 's%'";
 } elseif($_GET['letter']=="t") {
  $query .= "AND title LIKE 't%'";
 } elseif($_GET['letter']=="u") {
  $query .= "AND title LIKE 'u%'";
 } elseif($_GET['letter']=="v") {
  $query .= "AND title LIKE 'v%'";
 } elseif($_GET['letter']=="w") {
  $query .= "AND title LIKE 'w%'";
 } elseif($_GET['letter']=="x") {
  $query .= "AND title LIKE 'x%'";
 } elseif($_GET['letter']=="y") {
  $query .= "AND title LIKE 'y%'";
 } elseif($_GET['letter']=="z") {
  $query .= "AND title LIKE 'z%'";
 } else {
  $query .= "AND title LIKE 'a%'";
 } 
 
$query .= "ORDER BY games.title";
 
$rs = new MySQLPagedResultSet($query, 10, $cnx);
 

This way prevents users from messing up your page when they enter HTML tags into the query string. I suppose you could restrict those tags, but this way only allows you to define absolutely what is allowed.

TO sort by numbers, you call to the page like page.php?letter=num, otherwise the letter. I am going to add strtoupper into mine later on and make it so it always has to be in caps. :slight_smile:

You can see this in action at http://www.nintendonow.com/games_index.php

You can shorten the code a bit by using:

// connect here
 // basic query 
 $query = '...' ;
 
 $letters = range('A', 'Z');
 
 if (in_array(strtoupper($_GET['var']), $letters)) {
 	$query .= " AND title LIKE '%" . $_GET['var'] . "'";
 }
 else {
 	$query .= ' AND TITLE LIKE '%A';
 }

:eek2:

Ugliest code ever.

Ever heard of regular expressions?

I never get a regular expression right the first time, so I don’t know if this will work, but how about this?


if (preg_match('/\\A[a-z]\\Z/', $_GET['letter']))
  $query .= "AND title LIKE '$_GET[letter]%'";
else
  $query .= "AND title LIKE 'a%'";


Wow, thanks everyone for some great insight! I for sure did not have a clue as to where to start on this. It seemed to me, I could get my head around the tutorials on simple pagination on the net just fine but I could not find one on this type of pagination. I look forward to trying these suggestions out.

:smiley:

Here is a function I use to display a playlist for an internet radio station. Might be usefull


    function GetAlphabetArtist($db) {
        $listtoabbreviate=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        $doneOther=0;
        $sql=mysql_query('SELECT DISTINCT left(artist,1) AS letter FROM songs_list ORDER BY letter ASC', $db);
        while ($list=mysql_fetch_array($sql)) {
            if(!in_array(strtoupper($list['letter']),$listtoabbreviate)) {
                if($doneOther==0) {
                    echo "<font color=\\"#00ffff\\"><b> &nbsp;<a href=\\"".$_SERVER['PHP_SELF']."?letter=".$list['letter']."\\">Other</A>&nbsp; </b></font>";
                    $doneOther==1;
                }
            } else {
                echo "<font color=\\"#00ffff\\"><b> &nbsp;<a href=\\"".$_SERVER['PHP_SELF']."?letter=".$list['letter']."\\">".$list['letter']."</A>&nbsp; </b></font>";
            }
        }
    }

It may be a little confusing/misleading to call this pagination, as usually pagination involves splitting up a result set into pages based on a certain number of results per page. In this case, you’re not doing that, but instead you’re searching for a result based on the first letter of a field. In my opinion, this is not as complicated as pagination because you don’t have to do the maths to work out how many pages exist.

I agree with you in part on your thinking concerning this not being true pagination. I too thought this and as such has led me on this journey in the first place because I could not figure out how to do it as I was thinking in numerical pagination terms.

However I think my description of my problem might not have been as precise as I had wished either. I know an alphabetical index is necessary to start with. A | B | C |… etc. and then on each letter’s linked page, the MySQL results would be listed and limited to some type of amount (like 10/page) and THEN paginated according to the number of results.

Anyhow thanks for your input!

I did find a terrific little script that gives me the alphabetical index to start from anyhow. Its super short and sweet. I hope others can use/modify it too. It was initially created by a guy named Stephen Fugowski. I am going to modify it according to the other suggestions posted here earlier.


<?php
// Lists the letters of the alphabet with
// links associated with each letter
// Uses the ASCII values so you can do
// the for loop
for($i=65; $i<=90; $i++) {
	$x = chr($i);
	$pagelink = "PAGE_".$x;
?>
	<a href="<?php echo $pagelink ?>.php"><?php echo $x; ?></a>
<?php
 echo "  |  ";
}
?>

Again, thank you all for your input. It has really helped me out! :slight_smile:

:frowning:

Dont worry, atleast you had a go and know how you can do it shorter in the future :slight_smile: