homepage Welcome to WebmasterWorld Guest from 54.167.41.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
alphabetical paging
ianevans




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

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




msg:3849198
 2:40 am on Feb 14, 2009 (gmt 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.

ianevans




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

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




msg:3849471
 3:50 pm on Feb 14, 2009 (gmt 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.

ianevans




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

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




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

ianevans




msg:3850131
 7:44 pm on Feb 15, 2009 (gmt 0)

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




msg:3853129
 2:56 pm on Feb 19, 2009 (gmt 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;
}

whoisgregg




msg:3853174
 3:54 pm on Feb 19, 2009 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved