Sorting results by Alphabet

[TEENFRONT]

Hey

 

I like to know how to sort and group results by alphabet. I have over 2800 flash games listed in my database and i want to grab the names and the id of each game, order them in alphabetical order then group them that way.

 

So the results will show like this..

 

A

A game

A game 2

A game 3

 

B

B game

B game 2

B game 3

 

C

C game

C game 2

C game 3

 

 

 

What would the query be? Cheers!

[hitman6003]

SELECT * FROM tablename ORDER BY game_name ASC

 

If they are begin with both lower and upper case letters, use the cast function on MySQL to get results similar to php's natsort.

 

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

[TEENFRONT]

Hi

 

SELECT * FROM tablename ORDER BY game_name ASC

 

i know how to simply order results by a column name, sorry if i wasnt very clear.

 

Once the results are sorted that way, how do i then go on to display them all seperatly, not just

 

A game

B game

C game

 

I need the results like

 

A

A game

A game 2

A game 3

 

B

B game

B game 2

B game 3

 

C

C game

C game 2

C game 3

[hitman6003]

The way I would do it would be to break them into separate sub arrays...

 

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $first_letter = strtoupper(substr($row['game_name'], 0, 1));
  $letters[$first_letter] = $row['game_name'];
}

ksort($letters);

echo '<pre>' . print_r($letters, true) . '</pre>';

[Barand]

try

<?php
$sql = "SELECT gamename FROM games ORDER BY gamename";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

$prev = '' ;
while ($row = mysql_fetch_row($res) ) {
    $game =  $row[0];
    $initial = $game{0};
    if ($prev != $initial) {
        echo "<h3>$initial</h3>";
        $prev = $initial;
    }
    echo "$game<br/>";
}

?>

[Daniel0]

What would the query be?

 

SELECT *, UCASE(SUBSTR(gamename,0,1)) as first_letter FROM games ORDER BY gamename;

[Barand]

Daniel,

 

You'll find that needs to be SUBSTR(gamename,1,1) for the first letter with MySql