• 0

[sql] get first letter of a result


Question

+unabatedshagie

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?

Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0
+Dick Montage

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

Link to post
Share on other sites
  • 0
Mike

try

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

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

Link to post
Share on other sites
  • 0
+unabatedshagie

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()

Link to post
Share on other sites
  • 0
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`

Link to post
Share on other sites
  • 0
+Dick Montage

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

Link to post
Share on other sites
  • 0
+unabatedshagie
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.
Link to post
Share on other sites
  • 0
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 by Josh
Link to post
Share on other sites
This topic is now closed to further replies.
  • Recently Browsing   0 members

    No registered users viewing this page.