Jump to content



Photo

[sql] get first letter of a result


  • Please log in to reply
8 replies to this topic

#1 +unabatedshagie

unabatedshagie

    It used to be good on here.

  • Joined: 06-April 03
  • Location: SUNNY Scotland, like thats gonna happen :)
  • OS: Windows 8
  • Phone: iPhone 5

Posted 06 February 2008 - 15:04

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?


#2 +Nik L

Nik L

    Where's my pants?

  • Tech Issues Solved: 2
  • Joined: 14-January 03

Posted 06 February 2008 - 15:07

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

#3 Mike

Mike

    Neowinian Senior

  • Joined: 11-August 02

Posted 06 February 2008 - 15:16

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


#4 AnthonySterling

AnthonySterling

    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 06 February 2008 - 15:22

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

#5 OP +unabatedshagie

unabatedshagie

    It used to be good on here.

  • Joined: 06-April 03
  • Location: SUNNY Scotland, like thats gonna happen :)
  • OS: Windows 8
  • Phone: iPhone 5

Posted 06 February 2008 - 16:01

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

#6 AnthonySterling

AnthonySterling

    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 06 February 2008 - 16:11

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`


#7 +Nik L

Nik L

    Where's my pants?

  • Tech Issues Solved: 2
  • Joined: 14-January 03

Posted 06 February 2008 - 16:14

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

#8 OP +unabatedshagie

unabatedshagie

    It used to be good on here.

  • Joined: 06-April 03
  • Location: SUNNY Scotland, like thats gonna happen :)
  • OS: Windows 8
  • Phone: iPhone 5

Posted 06 February 2008 - 19:02

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.

#9 Josh

Josh

    Neowinian Senior

  • Joined: 09-August 04
  • Location: Felton, Delaware

Posted 07 February 2008 - 01:11

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 , 07 February 2008 - 01:17.




Click here to login or here to register to remove this ad, it's free!