PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Aug 22, 2019 3:15 am

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


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


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


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


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


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 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: Majestic-12 [Bot] and 9 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