Welcome to WebmasterWorld Guest from 23.20.79.227

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

alphabetical paging

     
10:38 pm on Feb 13, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 5, 2003
posts: 237
votes: 5


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!

2:40 am on Feb 14, 2009 (gmt 0)

Full Member

5+ Year Member

joined:Aug 17, 2007
posts:320
votes: 0


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.

3:23 am on Feb 14, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 5, 2003
posts: 237
votes: 5


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]

3:50 pm on Feb 14, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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.

7:13 pm on Feb 14, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 5, 2003
posts: 237
votes: 5


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]

12:44 pm on Feb 15, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 0


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 ...?
7:44 pm on Feb 15, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 5, 2003
posts: 237
votes: 5


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.
2:56 pm on Feb 19, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
posts:3416
votes: 0


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;
}
3:54 pm on Feb 19, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
posts:3416
votes: 0


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.