[Resolved] Alphabetical Navigation

juancuca

Hi. I would like to build an alphabetical navigation ( A B C .. W Y Z). The idea is that the link will have this format:

  echo '<a href = "somepage.php?show=A"> A </a>'

and I will have every letter of the alphabet like that that will be done with a with a "for..."

When the user clicks on the "A" they will open "somepage.php" and I will get the value of "show".

$show = $_GET['show']

I will do a query like this:

 $query = "SELECT  usernames FROM users WHERE usernames LIKE '".$show."%'  ";

Now that Im writing this, I wonder, is the above query, case sensitive or case insensitive ? ( will it match 'd%' s and 'D%'s ? )

What I would like to do is to do a query in the first page, to count how many users are in each letter, or if there is at least one user with each letter, whatever is easier.

ie: lets say I have the users : Ana, Charles. (I'd do this for A,B,C instead of the entire A-Z )

The desired output would be:

At this moment I can create the desired output doing a count for each letter ( = one query for each letter).
"SELECT COUNT(usernames) FROM users WHERE usernames like "A%"; and so on...

I wonder if this is not efficient and I would like to know if there is another better way of doing it.

Hope some1 can help me about this.
Thanks in advance !

Pablo
PS: I apologize for grammar/ english mistakes.

TheDefender

FOREACH(range('A', 'Z') as $letter):
	$qry = mysql_query("SELECT count(usernames) as countusernames FROM users WHERE usernames LIKE '".$letter."%'");
	$result = mysql_fetch_array($qry);
	IF($result['countusernames'] > 0 && $letter != "Z"):
		echo "<a href=\"showpage.php?show=".$letter."\">".$letter."</a> | ";
	ELSEIF($result['countusernames'] > 0 && $letter == "Z"):
		echo "<a href=\"showpage.php?show=".$letter."\">".$letter."</a>";
	ELSEIF($result['countusernames'] == 0 && $letter != "Z"):
		echo $letter." | ";
	ELSE:
		echo $letter;
	ENDIF;
ENDFOREACH;

I wouldn't imagine that this would be too much of a drain on the server to run through the loop of letters. I didn't test it yet, but it should work. I wanted to get it posted, and then I will go and test it.

OK, I tested it on a test DB I have, and it worked just fine for me (approximately 1500 usernames in it). BTW, it ran fast, and it is not case sensitive either.

juancuca

Thanks for the code snippet TheDefender, it looks a lot like what I was going to do but nicer 😃 .

I'm marking the post as resolved, but if someone has something else to add, please do so.

Thanks again !

PFS.

TheDefender

No problem, have a nice night. (I threw in a bit of extra checking to know whether to put the pipe symbol between the letters or not.) Hopefully that shouldn't screw you up to take that out if you don't want it. If you have any troubles, let me know, and I will repost the code without that part in there.

juancuca

(I threw in a bit of extra checking to know whether to put the pipe symbol between the letters or not.) Hopefully that shouldn't screw you up to take that out if you don't want it.

I can read that code correctly 😃 . Thanks again.
Later.

LordShryku

Here's another way. Completely untested, but I'll explain.

$sql = "SELECT upper(substr(usernames, 0, 1)) AS letter,
               count(*) AS cnt
          FROM users
      GROUP BY upper(substr(usernames, 0, 1))
      ORDER BY upper(substr(usernames, 0, 1))";
$res = mysql_query($sql);

$results = range('A', 'Z');
$results = array_flip($results);
while($row = mysql_fetch_array($res)) {
   $results[$row['letter']] = '<a href="showpage.php?show='.$row['letter'].'">'.$row['letter'].'</a>';
}

foreach($results as $k => $v) {
   if($k == "Z") {
      echo is_numeric($v) ? $k : $v;
   }
   else {
      echo is_numeric($v) ? $k : $v." | ";
   }
}

You'll save a little server load by doing it with one query. The substr pulls the first letter, and the upper converts them all to upper case. You assign all the letters with the range call, then flip them into the key positions using array_flip. Loop through the results from the query, and replace the numeric values with links for what was found. It'll only return letters that exist, so no need for numeric comparison. Now that I think about it, the count is fairly useless, and this could be accomplished with a SELECT DISTINCT substr(upper(usernames, 0, 1)) and no grouping. Have to see which one's faster. Anyway, once all the results are set, loop through them, check if it's "Z", if so, no trailing pipe. Then echo if the result is numeric, that means it didn't exist, just echo the number. Otherwise, echo the link.

TheDefender

I like this Lord, but one thing... He/She wants the letter to print as a non-link if there was no count for that specific letter. It may be in your code, but I didn't see it.

LordShryku

In the loop, we're doing

echo is_numeric($v) ? $k : $v;

$k is the letter, and $v is the value.

juancuca

Thank you guys.
I will test both of the snippets as soon as I get a chance to implement this.

I like this Lord, but one thing... He/She wants the letter to print as a non-link if there was no count for that specific letter. It may be in your code, but I didn't see it.

Is a He btw. I can figure out how to print no-links using some PHP.
I will just have to test which is the best method. Second method uses only 1 query so it looks it will be a little faster.
I was thinking on doing a loop from A - Z , foreach(range("A", "Z," ) ), and then testing in_array($row). So basically, if it exists in the array of the results it means it has users with that letter and it will print out the link, else it will just print the letter.
I have to program it and test it, but it looks like it will work this way.

Thanks both of you, I can figure out how to work it out from here 😃

TheDefender

OK, I couldn't get your query to work for me, because it kept giving me the error:
mysql_fetch_array(): supplied argument is not a valid MySQL result resource

But, when I just did a select *, it found the data, so my connections were fine.

I tinkiered around with the query a bit, and got this to work instead... I believe it is accomplishing the same thing...

$sql = "SELECT upper(left(usernames, 1)) AS letter, count(*) as cnt FROM mytable GROUP BY letter ORDER BY letter";

Now, the trick is this. The display didn't come out quite right. (See attached picture) See how the Q and R run together (without the pipe?) Same as the U,V,X,Y and Z.

I am going to play with your code some more, because this would definitely come in handy for me, for a script I just wrote.

P.S. The second method (LordShryku's) worked faster, because I tested it on a remote server with about 6000 more entries, and it worked at the same speed as my previous test with only 1500 names on a local DB (so it tells me it's quicker).

TheDefender

OK, I got it. The ternary operator ( ?: ) was throwing me off. I was trying to terminate the first part of the ternary with a semi-colon... Below is the updated code. Thanks Lord. That's come good code!

foreach($results as $k => $v) {
   if($k == "Z") {
      echo is_numeric($v) ? $k : $v;
   }
   else {
      echo is_numeric($v) ? $k." | " : $v." | ";
   }
}