Page 1 of 1

Advice request: Best way to display directory of names

Posted: Thu Nov 18, 2010 3:36 pm
by erika
I'm looking for advice on the best (most efficient, CPU/RAM-wise) way to display a directory of names and associated personal details.

Eventually the directory will be separated onto multiple pages, so this issue will be moot, but for at least a year I'll be displaying all of the names on a single page. Here are my parameters:

Nearly all of the last names are the same (it's a family directory), so I am sorting by first name. If there are no individuals whose name starts with a given letter, I just want to skip that letter and not display it.

I thought I had an effective plan for this, but somehow it got lost in the details.

Here's what I've done so far:

I created an associative array, $alphabet with keys as all the letters of the alphabet and set the value of each to 0.

I pulled the data from the database and ordered by first_name.

I ran a while loop to determine which letters have entries:

Code: Select all

    while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {
        // Get the first character of the first name
        $first_name = $x['first_name'];
        $first_char = strtolower(substr($first_name,0,1));

        // Increment the value in the $alphabet Array for the first letter.
        $alphabet[$first_char]++;
}
Initially I ended the loop here and then printed a list of bookmark links at the top of the page:

Code: Select all

    print "<p>\n";
    foreach ($alphabet as $key => $value) {
        if ($value > 0) {
            print '<a href="#' . $key . '">' . strtoupper($key) . "</a> &nbsp; ";
        }
    }
Then I realized that if I continued along this path I was going to have to do another foreach on the $alphabet array and then do a while loop for each individual letter, checking to see if the first name started with that letter and then displaying it.

And I thought that would be rather inefficient... so I backtracked in an attempt to resolve the problem but I can't figure out how to do so.

I went back to the first while loop and created arrays for all the data--I was trying to avoid doing this initially because I thought a huge array would be a problem (some of the individuals have extensive data in their entries). I changed the first while loop to this:

Code: Select all

    while ($x = mysql_fetch_array($result, MYSQL_ASSOC)) {

        // Get the first character of the first name
        $first_name = $x['first_name'];
        $first_char = strtolower(substr($first_name,0,1));

        // Increment the value in the $alphabet Array for the
        // first letter.
        $alphabet[$first_char]++;

        // Create a temporary array
        $person = Array();

        // Stuff the data for the current individual into the $person Array
        $person['last_name']     = $x['last_name'];
        $person['first_name']     = $x['first_name'];
        $person['suffix']     = $x['suffix'];
        $person['image_path']     = $x['image_path'];
        $person['year_of_birth'] = $x['year_of_birth'];
        $person['occupation']     = $x['occupation'];
        $person['location']     = $x['location'];
        $person['contact']     = $x['contact'];
        $person['letter']     = $first_char;

        // Now append it to the $listings array
        $listings[] = $person;
    }
So now I have a giant multi-dimensional array called $listings but I'm not sure what to do with it... or if I should even use it at all or if I should try another method.

If I continue with the array $listings, I still have to figure out how to separate it by letter. There are a couple ways I was thinking of doing this:

1) I could go ahead with the foreach on the $alphabet array with up to 26 iterations of a while loop checking to see if $listings[#]['letter'] = $key... this seems clunky.

2) I could create a variable, $current_letter with an initial value of NULL and then just cycle through the whole array once with an if statement at the top of the loop that is something like this: if $listings[#]['letter'] != $current_letter print a header for the new letter and set $current_letter to the new letter.

Obviously I'm not the first person to write a directory script, so I'm hoping there is an elegant and accepted way of doing this.

Any suggestions would be most appreciated.

Re: Advice request: Best way to display directory of names

Posted: Thu Nov 18, 2010 4:29 pm
by AbraCadaver
Not sure I completely understand, but two main options as I see it (and several variations on these):

1. Run two queries, one for the letters and one for all the people:

Code: Select all

SELECT LEFT(first_name, 1) as letter FROM table_name GROUP BY letter ORDER BY letter
Loop through and echo the letters. Then:

Code: Select all

SELECT * FROM table_name ORDER BY first_name
Loop through and echo people.

2. Run one query, loop through and echo letters, and build a $listing array to loop through after:

Code: Select all

SELECT *, LEFT(first_name, 1) as letter FROM table_name ORDER BY first_name

Code: Select all

$letter = '';
while($row = mysql_fetch_assoc($result)) {
   if($letter != $row['letter']) {
      //echo $row['letter'] hyperlink;
      $letter = $row['letter'];
   }
   $listing[] = $row;
}
// foreach over $listing
I would probably go with 1, because later when you paginate your results you can use a WHERE clause in the second query to only retrieve certain names beginning with that letter:

Code: Select all

$letter = $_GET['letter'];
$query = "SELECT * FROM table_name WHERE LEFT(first_name, 1) = $letter ORDER BY first_name