Alphabet Style Output

[gaza165]

Hi im trying to write a query that outputs the following.

 

Say I have a list of sports such as:

 

Football

Badminton

Basketball

Cricket

Squash

 

I want to write a query that outputs it like the following...

 

 

B

 

    Badminton

    Basketball

 

C

 

    Cricket

 

F

 

    Football

 

S

 

    Squash

 

 

So the names of the sport including their corrosponding letter.

 

this is what i have so far

 

<div class="sport-wrapper">
<?php
include("./dbconnect/dbconnect.php");
$id = $_GET['id'];

$sql = mysql_query("SELECT (DISTINCT UPPER(LEFT(sport_name,1)) as letters) FROM all_sports ORDER BY letters") or die(mysql_error());


	echo "<ul class='sports'>";
	while($row = mysql_fetch_array($sql)) {	
?>		

				<li>
					<span><?php echo $row['letters'];?></span>
				</li>


<?php }	?>	
</ul>
</div>

[jskywalker]

why not do:

SELECT (DISTINCT UPPER(LEFT(sport_name,1)) as letters), sport_name  FROM all_sports ORDER BY letters, sport_name

 

and than only print the letter when its different from previous line.

[gaza165]

why not do:

SELECT (DISTINCT UPPER(LEFT(sport_name,1)) as letters), sport_name  FROM all_sports ORDER BY letters, sport_name

 

and than only print the letter when its different from previous line.

 

I tried

 

SELECT DISTINCT UPPER(LEFT(sport_name,1)) as letters, sport_name FROM all_sports ORDER BY letters, sport_name

 

But it comes out with duplicate alphabet letters...

 

    * B  Badminton

    * B Basketball

    * C Cricket

    * F Football

    * N Netball

    * S Squash

    * T Table Tennis

    * T Tennis

    * V Volleyball

 

 

[jskywalker]

let /me quoot meself ;-)

and than only print the letter when its different from previous line.

[gaza165]

How do i do that??

[jskywalker]

untested:

while($row = mysql_fetch_array($sql)) {
   if ($prev<>$row['letters']) {
         if ($prev<>"") { echo "</ul>"; }      //end the previous list (if we had one)
        echo $row['letters'];                          // echo the letter
        echo "<ul>";                                     // start a new list
        $prev=$row['letters'];                       // store current letter
   }
    echo "<li><span><?php echo $row['sport'];?></span></li>";
}