Sign in to follow this  
Followers 0

[sql] get first letter of a result

9 posts in this topic

Posted

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&acute;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?

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

try

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

Share this post


Link to post
Share on other sites

Posted

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&acute;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. ;)

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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&acute;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`

Share this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Posted

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&acute;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.

Share this post


Link to post
Share on other sites

Posted (edited)

 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&acute;s beginning with $LastInitial'>$LastInitial</a>&nbsp;";

 }

 ?>

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.