Jump to content


Photo

Populating a member alphabet


  • Please log in to reply
13 replies to this topic

#1 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 12:29 AM

Alright I want to give the users the ability to search in the memberlist through letter, IE: click on letter A to display all names starting with A.

Now I thought about making a username table, where when a user registers, if the letter name doesnt exist it creates it like so:

ID - letter
1 - A
2 - B

Then using that table to check...

Then I also thought about creating an alphabet array and running each username through to see if it matches that.

Which way is more efficient, faster, etc?

#2 brendandonhue

brendandonhue
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 06 October 2006 - 01:02 AM

Are you getting this data from MySQL?
SELECT * FROM usernames WHERE username LIKE "A%"
SELECT * FROM usernames WHERE username LIKE "B%"
etc

#3 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 01:34 AM

I am yes, but I dont want to do 26 queries... I have this:

<?php

		$alpha = array(
				"A",
				"B",
				"C",
				"D",
				"E",
				"F",
				"G",
				"H",
				"I",
				"J",
				"K",
				"L",
				"M",
				"N",
				"O",
				"P",
				"Q",
				"R",
				"S",
				"T",
				"U",
				"V",
				"W",
				"X",
				"Y",
				"Z");
		
		for( $i = 0; $i <= 26; $i++ )
		{
			$keyword = $alpha[$i];
			$sql = mysql_query("SELECT * FROM `users` WHERE `username` LIKE '$keyword%'") or die(mysql_error());
			if (mysql_num_rows($sql) > 0)
			{
				echo $keyword;
			}
		}

?>

But its slow once the amount of users gets high.

#4 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 02:00 AM

<php

		$alpha = array(
					"A",
					"B",
					"C",
					"D",
					"E",
					"F",
					"G",
					"H",
					"I",
					"J",
					"K",
					"L",
					"M",
					"N",
					"O",
					"P",
					"Q",
					"R",
					"S",
					"T",
					"U",
					"V",
					"W",
					"X",
					"Y",
					"Z");
		
		$arr = array();
		
		for( $i = 0; $i <= 26; $i++ )
		{
			$keyword = $alpha[$i];
			$sql = mysql_query("SELECT * FROM `users` WHERE `username` LIKE '$keyword%'") or die(mysql_error());
			
			if (mysql_num_rows($sql) > 0)
			{
				$arr[] = "<a href=\"members.php?alpha=".$keyword."\">".$keyword."</a> - ";
			}
			else
			{
				$arr[] = $keyword." - ";
			}
		}
		
		foreach($arr as $row)
		{
			echo $row;
		}
?>

Thats what I have no, and it works but my CSS Layout loads, then 2 seconds later it pops up the PHP code where it belongs.

#5 brendandonhue

brendandonhue
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts

Posted 06 October 2006 - 02:03 AM

I can't think of anything better than the way you're doing it but you can make that query a bit faster
SELECT username FROM `users` WHERE `username` LIKE '$keyword%' LIMIT 1;

#6 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 06 October 2006 - 02:49 AM

alright, first off, you should only need one query to accomplish this.  if you want to grab the usernames that start with just one letter, do the following:

SELECT username FROM users WHERE LOWER(LEFT(username, 1)) = 'a'

should be faster than using a LIKE clause.  for numbers, simply do:

SELECT username FROM users WHERE LEFT(username, 1) IN (0,1,2,3,4,5,6,7,8,9)

next, to grab all users and echo them by first letter, run the following query:

SELECT LOWER(LEFT(username, 1)) AS firstletter, username FROM users GROUP BY firstletter ORDER BY username

and echo it with PHP like so:

<?php
$current_letter = 0;
while ($info = mysql_fetch_assoc($resource))
{
  if ($info['firstletter'] != $current_letter)
  {
    echo '<h1>'.$info['firstletter'].'</h1>';
    $current_letter = $info['firstletter'];
  }
  echo $info['username'].'<br>';
}
?>

i'm not sure how this will do with numbers, but you can give that a shot.  worst comes to worse, you run the numbers query once, then run the query to go through all the letters and stipulate in a WHERE clause that the first letter not be a number.

always have a look through the MySQL manual's functions section, as there are often MySQL functions to do what you're after more efficiently.  LOWER() converts a string to lowercase, LEFT() grabs the first x characters (1 here) in the string.

#7 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 05:54 AM

The problem with the script you gave me, is that it lists 1 of every letter username and only once. I want to be able to list:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Where if there is any username that starts with that letter, it makes it a link, otherwise it's just a text-letter.

Also, you never increase $current_letter, so the loop would never end.

#8 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 06 October 2006 - 02:56 PM

granted, i forgot to change $current_letter.  wouldn't make the loop go forever, it would just always echo the first letter header.  i'll edit that in my original post.

as for just echoing the individual letters and making them a link if there are any, you can run one query to get the tally for all letters in the database and one for the numbers.

SELECT DISTINCT UPPER(LEFT(username, 1)) AS firstletter, COUNT(username) AS tally FROM users WHERE UPPER(LEFT(username, 1)) NOT IN (0,1,2,3,4,5,6,7,8,9) GROUP BY firstletter ORDER BY firstletter

run that in phpMyAdmin with and without the where clause, and you'll see what you get.  you won't get a tally for the letters that have no users, so assuming youve loaded the results into an array with the format $array['letter'] => tally:

<?php
$letters = range('A', 'Z');
$letters_with_tally = array_keys($mysql_tallies);
foreach ($letters AS $letter)
{
  echo (in_array($letter, $letters_with_tally)) ? '<a href="somelinktotheletterresults.php">'.$letter.'</a>' : $letter;
}
?>

i guess this means you don't really need the actual tallies, just whether or not the first letter has a count of greater than 0 or not.  could maybe use an IF() to return a boolean, but that should work for your purposes for now.  digits will have to be done separately as you'll want to simply run a COUNT WHERE firstletter is in the digits list.

let me know if this is more what you were after.

#9 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 06 October 2006 - 03:54 PM

I have used this in the past. I tried to substitute your values the best I can.
<?php
// Find First Letters of usernames
$firstlet = "select DISTINCT SUBSTRING(username, 1, 1) AS letter FROM users ORDER BY letter ASC";
 $fres = mysql_query($firstlet) or die (mysql_error());
   print '<p><a name=A1>&nbsp;</a></p>';
   print '<table width=800 align=center>';
   print '<tr>';
   print '<td align=center>';
 while ($f = mysql_fetch_array($fres)){
// Create links to first letters
   print '<a href="'.$_SERVER['PHP_SELF'].'?filter='.$f['letter'].'">'.$f['letter'].'</a>&nbsp;&nbsp;';
   }
   print '</td>';
   print '</tr>';
   print '</table>';
   print '<br><br>';
// Check if filter is set
if(isset($_GET['filter'])){
$filter = $_GET['filter'];
} else {
$filter = '';
}
print '<table width=500 align=center>';
$custsql = "SELECT * FROM users WHERE username LIKE '$filter%' ORDER BY username ASC";
  $custres = mysql_query($custsql);
    while ($crows = mysql_fetch_array($custres)){
       print '<tr>';
       print '<td align=center><a href="link_to_details.php?userid='.$crows['userid'].'">'.$crows['username'].'</a></td>';
       print '</tr>';
    }
print '</table>';
?>
<hr>
<table width=700 align=center>
<tr>
<td width=100% align=center><a href="#A1">Return to Top</a></td>
</tr>
</table>

This will also only get the actual letters being used. So if there are no names in the table that begin with "Q" Q will not show up in the list.


Ray

#10 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 09:28 PM

This gets what I want to happen:

<?php

		$sql = mysql_query("SELECT LOWER(LEFT(`username`, 1)) AS `firstletter`, `username` FROM `users` GROUP BY `firstletter` ORDER BY `username`") or die(mysql_error());
		$arr = array(
					"a" => "0",
					"b" => "0",
					"c" => "0",
					"d" => "0",
					"e" => "0",
					"f" => "0",
					"g" => "0",
					"h" => "0",
					"i" => "0",
					"j" => "0",
					"k" => "0",
					"l" => "0",
					"m" => "0",
					"n" => "0",
					"o" => "0",
					"p" => "0",
					"q" => "0",
					"r" => "0",
					"s" => "0",
					"t" => "0",
					"u" => "0",
					"v" => "0",
					"w" => "0",
					"x" => "0",
					"y" => "0",
					"z" => "0");
					
		while ($info = mysql_fetch_array($sql))
		{
			$i = $info["firstletter"];
			$arr[$i] = "1";
		}
		
		foreach ($arr as $key => $row)
		{
			if($row == "1")
			{
				echo "<a href=\"members.php?alpha=".$key."\">".strtoupper($key)."</a>  ";
			}
			else
			{
				echo strtoupper($key)."  ";
			}
		}


?>


But its still a little slow to pop up, is there anyway to optimize the code so it loads all at once? Someone mentioned output buffering, is this true?

#11 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 06 October 2006 - 10:09 PM

Stress testing this with 100,000 users, it slows down slightly some more.. the whole thing loads in about 2 seconds but still. I'm thinking that a better way to do this would be to create a new table called Alphabet, then when someone goes to the members page it'll query that table to see something like this:

ID - Letter - Active
1 - A - 1
2 - B - 0
3 - C - 1

Etc.. then if its equal to 1, make a link, if not make the text... this would speed things up extremely well but is it the most efficient way to do something when designing for a large user base?

#12 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 07 October 2006 - 12:16 AM

the slowness is likely becuse you're pulling `username` from the database as well.  if you're just wanting to check whether there are any users by that name, the much faster way to do it is:

SELECT DISTINCT LOWER(LEFT(username, 1)) AS letter FROM users WHERE LOWER(LEFT(username, 1)) NOT IN (0,1,2,3,4,5,6,7,8,9)

put that into an array like so:

<?php
$linked_letters = array();
while (list($letter) = mysql_fetch_array($resource, MYSQL_NUM))
{
  $linked_letters[] = $letter;
}
?>

then do a foreach() through your array of letters (which can be reconstructed on the fly using range()).  if the letter is in the $linked_letters array (use in_array()), it needs a link.  otherwise users with that beginning letter were not found in the table, since it won't have found that first letter to SELECT from it.

i didn't realise you were solely after the links; now that i do, you've no need for a GROUP BY clause nor to select any usernames whatsoever in the first query.  hope this helps speed things up.

#13 tleisher

tleisher
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts

Posted 07 October 2006 - 05:51 AM

Would it be faster to do it that way or just to use a normalized table with active and inactive letters/numbers?

#14 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 08 October 2006 - 12:01 AM

probably about the same, but an extra table is unnecessary storage space.  the query i gave you for pulling only the distinct first letters in the table should be fairly quick, and the PHP for going through the letters and checking against the letters pulled from the database should also be quick.

it's up to you, but you'd have to constantly be updating the normalised table, and it presents the risk of being inaccurate.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users