如何按字母顺序对mysql结果进行分组？

Question

Looking to list mysql result in json. Needs to be alphabetical

json needs to be like this:

[
  {
    "letter": "A",
    "result": [
      {
        "name": "A&W"
      }
    ]
  },
  {
    "letter": "B",
    "result": [
      {
        "name": "Best Buy "
      }
    ]
  }
]

Need to group mysql results and add them under "result" for the appropriate letter. Based on the first letter of the "name" column in mysql.

heres my code:

    $sql = "SELECT name AS nameletter FROM stores ORDER BY FirstLetter";


    $result = mysql_query($sql);

 while($data = mysql_fetch_assoc($result)){

       echo $data['name'];

    }

--

Answer 1

You can always substr the first letter of the name and then use that to order your query.

SELECT name, SUBSTR(name, 1, 1) FirstLetter AS nameletter FROM stores ORDER BY FirstLetter

So this will return something like

Jason, J
Henry, H
Harry, H
Larry, L
Lenny, L

In PHP you need to build the array of these values, then you should be fairly easily able to loop through the array and get out the names per letter.