Alphabetical Navigation

CrimsonBlur

I have a MySQL database with hundreds of records including fields for first and last name among others. What I am doing is basically displaying the records in a gallery view (each one has an image associated with it) and the names. What I need to do is have an alphabetical navigation that never returns any empty results and stays consistent. I want to display 18 results at a time. I want the navigation to look like this:

Ab - Ce | Ce - Ga | -- etc.

So, the first letters are the first two letters from the first name of the person that is displayed on that page, and the last letters are the first two letters from the last person displayed on the page. Basically, I am navigating paginated results, but instead of displaying page numbers, I am displaying it in this way so it is much easier to know where you need to go. I don't have enough knowledge to figure this out on my own, and I've read all of the books I have and looked all over the Web and found nothing that really helps me do this.

I need to know how to make a SQL statement that will retrieve the letters for each page and I need to know how to display it using PHP. This is an important project for me and any help would be greatly appreciated!

alfoxy

Do you always want 18 results at a time?
If so, you need to first count the number of database entries.
Then divide this by 18. to get an approx number of users that should be in each group.
Say you get 180 users so 10 in each group.

Select * from users order by last_name DESC Limit 0,10 - this will give you the first 10 users.

Then print out the first two letters of the first and last entries

Then proceed 18 timeswith the limit changing to:
Limit 10,10
Limit 20,10
Limit 30,10
(use a "for loop" if you are comfortable with them)

Sorry I have no code posted as Im a book head, i need my bible in front of me and Im away at the minute.

Ill post some code snippets tomorrow if you need any more help.
Hope that all amde some kind of sense ??

kakki

so i think the post from alfoxy shall show you how to get 18 lines only,
the second part of your question:

CrimsonBlur wrote:
......
Ab - Ce | Ce - Ga | -- etc.
.......

the alphabetical navigation may be:
SELECT * FROM names WHERE substr( name,0,2 )>='Ab' AND substr( name,2 ) < 'Ce'
( for the first Ab-Ce navigation )

( i dont know if this will work, but there is a "substr"-method in mysql )

maybe this will give you a hint

Roger_Ramjet

LEFT(lastname, 2) will return the first 2 chars from the lastname column.

Problem: what if record 18 and 19 share the same 2 chars at the beginning of their name? eg Simon Collins and Andrew Cooper, or worse Andrew Jones and Simon Jones

// basic query

$sql = "SELECT * , LEFT(lastname, 2) AS pre FROM table ORDER BY lastname, firstname";
$result = mysql_query($sql);

// you now have a sorted list of all entries, 
//	each with it's first 2 chars of the lastname in a field called pre

$count = mysql_num_rows($result);

// gives the total count with which you control the loop exit

// loop counter and array index - array index starts at 0 but count starts at 1
$i = 0;  

while ($i < ($count)) {
	$row = mysql_fetch_array($result[$i]);		// get start record
	$list .= $row['pre'] . ' - ';					// output start
// check if there are 18 records left? 
//	if there are increment index by 18 : 
//	if not set it to count - 1
	if (($i+18)<$count) {
		$j = $i+18;
		$str = ' | ';
	}
	else {
		$j = $count-1;
		$str = '';
	}
	$row = mysql_fetch_array($result[$j];		// get end record
 	$list .= $row['pre'] . $str;					// output end
 	$i = $j;
}

// now you have the index list in the strin $list
// of course you need to actually include the html for the page links as well
// as the initials. I have not done it here to keep things easy to understand

// typical page link would look like

$list .= '<a href="page.php?offset=' . $i . '>' . $row['pre'] . ' - ';

$list .= $row['pre'] . '</a>' . $str;

// page.php use the $_GET['offset'] to limit the query and display the records for that page

can you take it from there

CrimsonBlur

Thanks for the help, guys! I think I can take it from here and make it work with my design!