[sql] get first letter of a result

[+unabatedshagie Subscriber¹]

I have a list of members in my database.

I would like to make a list of links at the top of a page containing the first letter of the members first name but only make the links appear if there is a particular member that has that last name.

I came up with this

$surname_query = "SELECT DISTINCT last_name FROM members ORDER BY last_name";
   $getlist = mysql_query($surname_query) or die(mysql_error());

   while ($row = mysql_fetch_array($getlist)) {

	   $surname = substr($row['last_name'], 0, 1);
	   echo "<a href='?lname=$surname' title='Surname´s beginning with $surname'>$surname</a> ";

   }

Problem is it makes a seperate link for each surname it finds, so if there is a member with the surname Stewart and another with Sausages the code returns two links for the letter S.

Is there any way of making the query only return a letter once or any other way anyone can think of to do this?

[+Nik Louch Subscriber²]

Would the LEFT keyword be what you are after, in your SELECT statement?

[Mike]

try

SELECT DISTINCT LEFT(last_name,1) FROM members ORDER BY last_name

[AnthonySterling]

Personally, I would add the results to an array then output that.

Example:-

<?php
$dbQuery = "SELECT DISTINCT last_name FROM members ORDER BY last_name";
$dbResult = mysql_query($dbQuery) or die(mysql_error());
$surnameArray = array();
while ($row = mysql_fetch_array($dbResult))
{
	   $surname = substr($row['last_name'], 0, 1);
	   if(!in_array($surnameArray))
	   {
	   	$surnameArray[] = $surname;
	   }
}
foreach ($surnameArray as $surnameValue) 
{
	echo "<a href='?lname=$surnameValue' title='Surname´s beginning with $surnameValue'>$surnameValue</a> ";
}
?>

Although, as njlouch suggests, it would be alot easier to only bring back DISTINCT first letters with your SQL query to avoid 2 PHP loops.

SilverB. ;)

[+unabatedshagie Subscriber¹]

Hmmm, sounds like it should work but I'm getting a error saying Undefined index: last_name and if I try your code SilverBullet I'm told Wrong parameter count for in_array()

[AnthonySterling]

Ooops, Sorry bud.

Try:-

<?php
$dbQuery = "SELECT DISTINCT last_name FROM members ORDER BY last_name";
$dbResult = mysql_query($dbQuery) or die(mysql_error());
$surnameArray = array();
while ($row = mysql_fetch_array($dbResult))
{
	   $surname = substr($row['last_name'], 0, 1);
	   if(!in_array($surname,$surnameArray))
	   {
		   $surnameArray[] = $surname;
	   }
}
foreach ($surnameArray as $surnameValue)
{
	echo "<a href='?lname=$surnameValue' title='Surname´s beginning with $surnameValue'>$surnameValue</a> ";
}
?>

Noob mistake.... :blush:

Dev's SQL query works for me BTW.

Another SQL example:-

SELECT DISTINCT LEFT(`name`,1) FROM `user_users`

[+Nik Louch Subscriber²]

SELECT last_name = DISTINCT LEFT(last_name,1) FROM members ORDER BY last_name

[+unabatedshagie Subscriber¹]

Ooops, Sorry bud.

Try:-

<?php
 $dbQuery = "SELECT DISTINCT last_name FROM members ORDER BY last_name";
 $dbResult = mysql_query($dbQuery) or die(mysql_error());
 $surnameArray = array();
 while ($row = mysql_fetch_array($dbResult))
 {
		$surname = substr($row['last_name'], 0, 1);
		if(!in_array($surname,$surnameArray))
		{
			$surnameArray[] = $surname;
		}
 }
 foreach ($surnameArray as $surnameValue)
 {
	 echo "<a href='?lname=$surnameValue' title='Surname´s beginning with $surnameValue'>$surnameValue</a> ";
 }
 ?>

Noob mistake.... :blush:

Dev's SQL query works for me BTW.

Another SQL example:-

SELECT DISTINCT LEFT(`name`,1) FROM `user_users`

Thanks, worked perfectly. (Y)

SELECT last_name = DISTINCT LEFT(last_name,1) FROM members ORDER BY last_name

Thanks for trying but that doesn't seem to work either.

[Josh]

 SELECT DISTINCT LEFT(lastname,1) AS LastInitial FROM members ORDER BY LastInitial ASC

<?php
 $dbQuery = "SELECT DISTINCT LEFT(lastname,1) AS LastInitial FROM members ORDER BY LastInitial ASC";
 $dbResult = mysql_query($dbQuery) or die(mysql_error());
 while ($row = mysql_fetch_array($dbResult))
 {
	   echo "<a href='?lname=$LastInitial' title='Surname´s beginning with $LastInitial'>$LastInitial</a> ";
 }
 ?>

This is much more efficient in every way. Try to think about how you can slim the dataset to being as small and efficient as it can possibly be before the application code starts in on it's functions. This will make/keep your apps running exponentially faster as the DB apps are generally much faster than application code.

That, and why pass around more data than you'll actually use?

Edited February 7, 2008 by Josh