Jump to content

Archived

This topic is now archived and is closed to further replies.

tleisher

Populating a member alphabet

Recommended Posts

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?

Share this post


Link to post
Share on other sites
Are you getting this data from MySQL?
SELECT * FROM usernames WHERE username LIKE "A%"
SELECT * FROM usernames WHERE username LIKE "B%"
etc

Share this post


Link to post
Share on other sites
I am yes, but I dont want to do 26 queries... I have this:

[code]
<?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;
}
}

?>
[/code]

But its slow once the amount of users gets high.

Share this post


Link to post
Share on other sites
[code]
<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;
}
?>
[/code]

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.

Share this post


Link to post
Share on other sites
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;

Share this post


Link to post
Share on other sites
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:

[code]SELECT username FROM users WHERE LOWER(LEFT(username, 1)) = 'a'[/code]

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

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

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

[code]SELECT LOWER(LEFT(username, 1)) AS firstletter, username FROM users GROUP BY firstletter ORDER BY username[/code]

and echo it with PHP like so:

[code]<?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>';
}
?>[/code]

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

[code]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[/code]

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:

[code]<?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;
}
?>[/code]

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.

Share this post


Link to post
Share on other sites
I have used this in the past. I tried to substitute your values the best I can.
[code]<?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>[/code]

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

Share this post


Link to post
Share on other sites
This gets what I want to happen:

[code]
<?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)."  ";
}
}


?>

[/code]

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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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:

[code]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)[/code]

put that into an array like so:

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

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.

Share this post


Link to post
Share on other sites
Would it be faster to do it that way or just to use a normalized table with active and inactive letters/numbers?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.