Create Member Directory with Alphabetical Listings

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Create Member Directory with Alphabetical Listings

Post by curseofthe8ball »

I'm trying to create a page where it sorts through a member database and lists the users by lastname underneath the Alphabetical heading. For example, at the top of the page, I want to display anchor links:

A | B | C | D | E... etc

Then beneath the anchor links I want to display the letter header with its results in two columns, such as:

A
Adams, Bob Anderson, Mark
Allen, Tom Anthony, Craig

B
Black, Jack Booth, Nick
Boone, Ed Bronson, Fred


I've tried to put all of the pieces together and I've gotten most of it completed, except for breaking the results down to two columns. So far, here is what I've got:

Code: Select all

 
$result2 = mysql_query("SELECT DISTINCT SUBSTRING(last_name, 1, 1) AS letter FROM members ORDER BY letter");
 
$num_rows = mysql_num_rows($result2);
$i = 0;
 
while ($row = mysql_fetch_assoc($result2)) {
$letter_link = $row['letter'];
 
$i++;
if ($i == $num_rows) {
echo "<a href='#$letter_link'>$letter_link</a>"; }
 
else { echo "<a href='#$letter_link'>$letter_link</a> | "; }
}
 
echo "<br><br>";
 
$result = mysql_query("SELECT * FROM members ORDER BY last_name ASC");
 
while($row = mysql_fetch_array($result)) {
$id = $row['id'];
$first_name = $row['first_name'];
$middle_name    = $row['middle_name'];
$last_name  = $row['last_name'];
 
$array_letter = substr($row['last_name'], 0, 1);
if($array_letter != $letter) {
 
$letter = $array_letter;
 
echo "<br><a name='". $letter ."'></a><b>". $letter ."</b><br><img src='images/divider.jpg' width='500' height='21'><br>"; }
 
echo "<a href='attorneys.php?action=view&id=$id'>$last_name, $first_name"; if (!empty($middle_name)) { echo " $middle_name"; } else { echo ""; } echo "</a><br>";
}
 


This does everything I want it to do, except break the results down into 2 columns. If someone could aid me how to edit the above code to allow for a 2 column result and also I will probably be looking to setup the 2 columns to display a different result set. For example, the first column would have a subheader of Partners and the 2nd column would have a subheading of Associates. I would get these results from a "position" column that is in the database.

Any help would be much appreciated!
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

You could try something like:

Code: Select all

// Alphabetic list which makes the links. 
function drawAddressList() { 
$seperator = "-"; 

// Loop through all 26 characters. 
for ($i = 1; $i <= 26; $i++) { 
$letter = chr($i + 64); 
echo $seperator . "<a href='{$_SERVER['PHP_SELF']}?letter={$letter}'>{$letter}</a>"; 
} 
echo $seperator . '<br />'; 
} 

// Gets a parameter or the default. 
function getParm($VariableName, $Default = '') { 
return (isset($_GET[$VariableName])) ? $_GET[$VariableName] : $Default; 
} 

drawAddressList(); 


require 'db.php';

$letter = $_REQUEST['letter'];

$sql = mysql_query( );
So one can click on a letter and have matching results display.

hth
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how are you wanting to break the columns down? Do you want it to be a list view, where it fills a column, then switches to a new column. Or do you want to tile them horizontally? i.e.

Code: Select all

Option 1:
---------
a f
b g
c h
d i
e

Option 2:
---------
a b
c d
e f
g h
i
If option 1, would you want the wrap to happen at a certain height, or balance the lengths of the sides as much as possible?


Here's a solution that does option 2 (found in the Useful Posts thread): viewtopic.php?t=25105
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Jim_Bob,

Thanks for the reply, but I really would like to keep the member list all on one page, as opposed to making one click the link and goto that specified page. Anchor links at the top would allow them to page down the page to the letter they are looking for.

Also, the biggest concern I have is trying to get the results to display in multiple columns (2).

Any ideas on how that can be tied into my code, or your code?
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Feyd,

Option 1 would work best with it trying to balance out the columns (ie, if there are 11 entries, it puts 6 in the results 1-6 in the first column and results 7-11 in the 2nd column).

Although, it might be beneficial for me to see both Option 1 and Option 2 so I can decipher the difference in the code so I can make the change if I decide against my initial thoughts.

Also, any idea on how to get the two columns to display a different result set? For example, the first column would display results from a query where position = 'partner' and the 2nd column would display the results from a query where position = 'associate'. This wouldn't be hard if I was just trying ti display the results by themself, but trying to break the results down by letter makes it a little more difficult. I could always do 27 different queries but that is sloppy.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

basically what happens for Option 1 is you cache the first half (at least) of the results, then slap in the second half. Here's an untested example

Code: Select all

// assume $result is the result set

// cache the data
$cache = array();
while($row = mysql_fetch_assoc($result))
{
  $cache[] = $row;
}

$col2 = ceil(count($cache) / 2);
for($i = 0; $i < $col2; $i++)
{
  echo '<tr><td>'.$cache[$i].'</td><td>'.(isset($cache[$i + $col2]) ? $cache[$i + $col2] : '&nbsp;').'</td>';
}
now, as for displaying 1 result set in column A and a second in column B, it's similar, you have to cache the results but have a lot more checking and stuff.

Code: Select all

// assume $col1 and $col2 are the two result sets

// cache the data
$cache1 = array();
while($row = mysql_fetch_assoc($col1))
{
  $cache1[] = $row;
}

$cache2 = array();
while($row = mysql_fetch_assoc($col2))
{
  $cache2[] = $row;
}

$len = max(count($cache1),count($cache2));
for($i = 0; $i < $len; $i++)
{
  echo '<tr><td>'.(isset($cache1[$i]) ? $cache1[$i] : '&nbsp;').'</td><td>'.(isset($cache2[$i]) ? $cache2[$i] : '&nbsp;').'</td>';
}

You can somewhat easily combine that with letter seperation. Basically, you cache the results of a complete query. Sort the results (mysql may not sort it well), then create a two dimensional array of letter and name information. Each seperated letter group can be sent to either of the above to write out that group. :)
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Feyd,

I appreciate your insight on this. I'll give your examples some looking over and see what I can come up with. My experience with Arrays are very limited so I have a feeling I'm going to get stuck in the part where I am caching the results into an array, so you may be hearing from me again shortly.

Thanks again!
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Feyd,

Okay, this is what I've got so far. It seems to split the results to 2 columns like Option 1 but it is duplicating the entries if it finds more then 1 result. The code is:

Code: Select all

$result2 = mysql_query("SELECT DISTINCT SUBSTRING(last_name, 1, 1) AS letter FROM members ORDER BY letter");

$num_rows = mysql_num_rows($result2);
$i = 0;

while ($row = mysql_fetch_assoc($result2)) {
$letter_link = $row['letter'];
$i++; 

if ($i == $num_rows) { 
echo "<a href='#$letter_link'>$letter_link</a>";  
} 

else { echo "<a href='#$letter_link'>$letter_link</a> | "; 
}
}
					
echo "<br><br>";
				   
				   
$result = mysql_query("SELECT * FROM members ORDER BY last_name ASC");

while($row = mysql_fetch_array($result)) {
$array_letter = substr($row['last_name'], 0, 1);

if($array_letter != $letter) {
$letter = $array_letter;

echo "<br><a name='". $letter ."'></a><b>". $letter ."</b><br><img src='images/divider.jpg' width='500' height='21'><br>";
}

$result22 = mysql_query("SELECT * FROM members WHERE last_name LIKE '$letter%' ORDER BY 'last_name'");
$cache = array(); 

while($row = mysql_fetch_assoc($result22)) 
{
$cache[] = "<a href=members.php?action=view&id=". $row['id'] ."'>". $row['last_name'] .", ". $row['first_name'] ." ". $row['middle_name'] ."</a>"; 
} 

echo "<table width='100%'  border='0' cellspacing='0' cellpadding='0' class='twelve'>";

$col2 = ceil(count($cache) / 2); 
for($ci = 0; $ci < $col2; $ci++) 
{

echo '<tr><td width=50%>'.$cache[$ci].'</td><td width=50%>'.(isset($cache[$ci + $col2]) ? $cache[$ci + $col2] : '&nbsp;').'</td>';
}

echo "</tr></table>";
}
I can tell why it is duplicating the entries, because of the last 2 loops but I'm not sure how everything should be organized to create the proper output. Any ideas? Can you decipher my hack job of code?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I often do something like the following pseudo-code:

Code: Select all

Create a new array with a key for every letter and the elements are arrays.
Query for all members ordering by last name.
Analyze the last name of each member, tossing them into the proper letter bucket.

Now, start a loop through the letter buckets.
Output the letter header.
This letter's bucket is the cached array in previous code.
Continue down the previous code paths.
Hope that helps.
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Feyd,

Thanks for the outline... but my lack of overall knowledge of PHP is going to keep me from being able to put that altogether. Anyway you could give me some code samples of the various pieces so I can get started with some new code?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sure. Array of letter buckets:

Code: Select all

$buckets = array();
$A = ord('A');
for($i = 0; $i < 26; $i++)
{
  $buckets[chr($A + $i)] = array();
}
extract member detail and sort into appropriate bucket

Code: Select all

// assume $member is a member record from the database
$bucket = strtoupper(substr($member['last_name'],0,1));
$buckets[$bucket][] = $member;
bucket output loop

Code: Select all

foreach($buckets as $letter => $members)
{
  usort($members, 'strnatcasecmp'); // natural case insensitive sort (just in case)
  // ADD: letter output here
  // ADD: member output here (Option 1, Option 2, etc)
}
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Feyd,

I'm strugling trying to add the output code. I'd like to query the database to figure out what letters the page should be printing (for example, if there are any Bob Adams and Tom Smith in the database then it would only print "A" and "S") as its suheaders.

Then with your code, how would I query the database to print the members under their respective letters?

Sorry for all the questions but your code is much more advanced then I'm used to, so I'm pretty much learning on the fly.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you don't query the database for individual letters, it's a waste of time. You can use the sizeof() the buckets to tell you whether it has anything in it or not.
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

I'm not familar with the sizeof() function...I'll check it out.

Where does the querying of the database come in play for how I pull out the various records?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

as I said previously, do a complete table query sorting by last name, and apply the second code block above.
Post Reply