Geek Answers Handbook

Is there a better way to create an alphabetical pagination index in PHP / MySQL?

One of my standard pagination actions in my CMS is to show the alphabetical quick access bar when sorting by alpha column. For example, if the results are sorted by name, in the pagination section, I display a series of links, from A to Z, so that you immediately get to the page for a specific first character.

Example:

I'm currently doing this, getting all the results for this column, sorted alphabetically, and then scrolling all of them in PHP and writing down which page the record appears on. This works great when you are dealing with only a few hundred results, but now I am working on a project that could potentially have several hundred thousand lines, and this is simply not a viable option.

Is there a more efficient method to create such an index? Note that it should also handle more than just AZ, since lines can start with numbers or punctuation marks.

Edit for clarification : I'm not looking for a simple list of all the first characters, which is easy. I need to calculate on which page from the final results the field starting with this symbol will be included. So say we are looking for someone named Walter, and I have 1000 rows, I need to know where W. starts in this 1-1000 range.

+7
source share
4 answers

I assume this is a varchar field, so you considered the following:

SELECT DISTINCT SUBSTRING(lastname FROM 1 FOR 1) FROM mytable;

This will give you an excellent list of the first letters of the last name.

You can also use UPPER () to make sure you just get uppercase characters. LEFT () will also achieve something similar, so you should experiment to see what works best in your dataset.

Edit: if you also want to read:

 SELECT DISTINCT SUBSTRING(lastname FROM 1 FOR 1) AS firstletter, COUNT(*) AS counter FROM mytable GROUP BY firstletter;

No need to make a second query for each letter.

+8
source 
 $sql = "SELECT left(name, 1) AS firstchar FROM mytable ORDER BY name"; $result = mysql_query($sql) or die(mysql_error()); $letters = array(); $row = 0; while($row = mysql_fetch_assoc($result)) { $row++; if (!isset($letters[$row['firstchar']])) { $letters[$row['firstchar']] = $row; } }

This will give you an array typed using the first letters, and the line number with which they first appeared for the value:

 a => 1, b => 50, c => 51,

etc...

There is probably some way to do this purely in SQL, but MySQL itself does not have native row count support, so that would be a very ugly query.

+1
source

Just like standrd pagination is just a matter of fetching and arranging - just add WHERE with A% (don't forget to create an index in this column)

0
source 
 <?php $result1 = mysql_query("SELECT LEFT(name, 1) AS fl FROM comics GROUP BY fl"); while ($row = mysql_fetch_array($result1)) { $result11 = mysql_query("SELECT * FROM comics WHERE name LIKE '".$row['fl']."%'"); $countresult11 = mysql_num_rows($result11); ?> <a href="?sort=<?php echo $row['fl']; ?>" title="<?php echo $countresult11; ?> Comics"><?php echo $row['fl']; ?></a>&nbsp; <?php } ?>

it may be curious what you are looking for if you replace my variable / table names with yours.

which will check the table, pull the first letter from each group by this letter and display it as

 1 3 7 9 ABRWXYZ

depending on what you have in the table

0
source

More articles:


All Articles