PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Feb 18, 2019 2:38 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Sat Aug 27, 2005 3:19 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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:

Syntax: [ Download ] [ Hide ]
 
$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!


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 3:28 pm 
Offline
Forum Contributor

Joined: Sat Oct 02, 2004 3:04 pm
Posts: 390
You could try something like:

Syntax: [ Download ] [ Hide ]
// 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


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 3:37 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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.
Syntax: [ Download ] [ Hide ]
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): http://forums.devnetwork.net/viewtopic.php?t=25105


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 3:38 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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?


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 3:43 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 4:13 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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
Syntax: [ Download ] [ Hide ]
// 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.
Syntax: [ Download ] [ Hide ]
// 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. :)


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 27, 2005 4:27 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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!


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 12:39 am 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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:

Syntax: [ Download ] [ Hide ]
$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?


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 8:22 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
I often do something like the following pseudo-code:
Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 9:42 am 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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?


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 9:56 am 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
sure. Array of letter buckets:
Syntax: [ Download ] [ Hide ]
$buckets = array();

$A = ord('A');

for($i = 0; $i < 26; $i++)

{

  $buckets[chr($A + $i)] = array();

}


extract member detail and sort into appropriate bucket
Syntax: [ Download ] [ Hide ]
// assume $member is a member record from the database

$bucket = strtoupper(substr($member['last_name'],0,1));

$buckets[$bucket][] = $member;


bucket output loop
Syntax: [ Download ] [ Hide ]
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)

}


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 10:40 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 10:43 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
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.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 10:47 pm 
Offline
Forum Commoner

Joined: Sun Jun 01, 2003 12:33 am
Posts: 73
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?


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 11:00 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
Location: Bothell, Washington, USA
as I said previously, do a complete table query sorting by last name, and apply the second code block above.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group