alphabetical paging

[ianevans]

Was getting ready to work on listing of celebs we cover and realized that I was probably getting into reinventing the wheel territory here. I'm already googling for possible code examples but figured I'd ask here to see if anyone has an example.

I'm looking for code that would paginate database results by last name. For example, I specify 50 results a page so looking for "A" names would create a set of links like:

Aardvark-Albert Albertson-Anders Anderson-Atwell

etc...

Any ideas? Thanks!

[optik]

You can just use ORDER BY when selecting the results from the database

[w3schools.com...]

This assuming you have the last name in a separate filed to the first name.

[ianevans]

Optik,

I'm sorry, you must have misunderstood my question. Of course I know about ORDER BY, I was looking for a more complex coding solution to create library style pagination of results.

In other words, instead of creating the standard "previous 1 2 3 4 5 next" pagination links, it would divide the results into a library style pagination using the last names. I've seen ASP examples (see the second part of <snip>) but I was looking for a PHP code snippet.

Thanks.

[edited by: eelixduppy at 12:23 am (utc) on Feb. 16, 2009]
[edit reason] No personal urls, thanks. [/edit]

[rocknbil]

I have this in code somewhere but it's easy enough to follow the logic.

You create a function to generate the links.

var $alphas = Array('A','B','C'....);
foreach ($alphas as $letter) {
$links .= "<a href=\"script.php?letter=$letter\">$letter</a> ¦ \n";
}
....

return $links;

Then in your query, and I'm guessing you would be using mySQL,*

if (isset($_GET['letter'])) {
$select = "select * from table where field like '$_GET['letter']%' order by field";
}

The % is a "wildcard," meaning "any character," so your select says "anything beginning with this letter." If you have any problems with case insensitivity (you shouldn't, my experience shows like as case insensitive) you can use regex instead.

*Do not use input variables from $_GET or $_POST directly, cleanse your data to avoid injection attacks.

[ianevans]

Thanks for the code Rocknbil, but to clarify I wasn't looking for A, B, C etc links but the library style of Aa-Af, Ag-Br, etc.

After extensive searching, I was able to find what I needed at:

<snip>

Thanks!

[edited by: dreamcatcher at 12:49 am (utc) on Feb. 15, 2009]
[edit reason] No personal urls, thanks. [/edit]

[penders]

Just curious as to the eventual solution to this... was it more PHP related, along the lines of rocknbil's solution with multiple SELECTs, or is there a neat SQL answer using something like LIMIT ...?

[ianevans]

Oops, guess I can't point to the URL with the solution. Basically their solution was to place the names in arrays based on the number of results you wanted a page then group the ranges. E.g. If you wanted eighteen names/titles/whatever a page you'd toss the sql result into an array and then grab the first and 18th result out of the array to create the, say, Aa-Al link, the 19th and 36th result to create the Am-Be link, etc. Clicking on those links would use a limit clause to grab the underlying results.

[whoisgregg]

Here's some code I put together that will produce this style of pagination, using the array method ianevans describes:

$results = array('Adams','Alameda','Alan'); // actually populated from MySQL, but you get the idea ;)
$number_of_pages = 10; // edit this to determine the size of the chunks
$count_results = count($results);
$results_per_page = ceil($count_results/$number_of_pages);
$page_titles = array();
for($i=0; $i<$number_of_pages; $i++){
$cur_first = $i*$results_per_page;
$cur_last = (($i+1)*$results_per_page) - 1;
while(!isset($results[$cur_last])){
$cur_last--;
}
$page_titles[$i] = array($results[$cur_first], $results[$cur_last]);
}
foreach($page_titles as $page => $names){
echo 'Page '.($page+1).' '.$names[0].' - '.$names[1].PHP_EOL;
}

[whoisgregg]

And here's the whole thing in MySQL queries using the sample `World` database from MySQL [dev.mysql.com]. Just change the value of @pages to split up the chunks.

SELECT SQL_CALC_FOUND_ROWS * FROM `City` ORDER BY `Name` DESC LIMIT 1; # this is how you get the *last* item 
SET @count = FOUND_ROWS(); 
SET @pages = 18; 
SET @per_page = CEILING(@count/@pages); 
SET @a=0; 
CREATE TEMPORARY TABLE findbreaks SELECT *,@a:=@a+1,@a% @per_page AS mod1,(@a-1)% @per_page AS mod2 FROM `City` ORDER BY `Name` ASC; 
SELECT * FROM findbreaks WHERE `mod1`=0 OR `mod2`=0; # this is where you get the rest

On a table with 4079 rows, this takes my server between 0.0037 and 0.0042 seconds to complete.