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

Question

希望以json格式列出mysql结果。需要按字母顺序排列

json需要是这样的：

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

需要对mysql结果进行分组，并将它们添加到适当字母的"result“下。基于mysql中"name“列的第一个字母。

下面是我的代码：

    $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

您始终可以减字符串名称的第一个字母，然后使用它来对查询进行排序。

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

因此，这将返回如下内容

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

在PHP中，您需要构建这些值的数组，然后您应该可以很容易地遍历数组并获得每个字母的名称。

Answer 2

您正在使用非常不推荐使用的mysql函数，这些函数将在将来删除。您应该开始将您的知识转换到PDO接口以进行数据库访问。在使用PDO的许多好处中，它提供了一些很好的功能，这将使您在这里受益。应该注意的是，我在下面指出的用法根本不是最有效的方法，如果你正在处理长列表，你不应该这样做。但是，知道如何让php为你做大部分工作是一件很有趣的事情。

给定一个表“store”，其中有一列“name”和以下示例值：

name
----
Black Pencil theatre Company
Bluebird Films
Foggy Camel Films
Fork and Spoon Marketing
Opaque Cat Web Design
Transparent Toaster Marketing
Content Panda Company
For Yours and Everyone Else's Eyes Only

您可以首先使用查询以有意义的顺序检索数据：

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

FirstLetter   name
B             Black Pencil theatre Company
B             Bluebird Films
C             Content Panda Company
F             Foggy Camel Films
F             For Yours and Everyone Else's Eyes Only
F             Fork and Spoon Marketing
O             Opaque Cat Web Design
T             Transparent Toaster Marketing

使用PDO类，我们可以直接将数据分组：

<?php
$dbh = new PDO('mysql:host=localhost;dbname=sotest', 'testuser', 'testpass');
$stmt = $dbh->query('SELECT left(name,1) as FirstLetter, name FROM stores ORDER BY FirstLetter, name');
$results = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_GROUP);

/* $results is now =
array(5) {
  ["B"]=>
  array(2) {
    [0]=>
    string(28) "Black Pencil theatre Company"
    [1]=>
    string(14) "Bluebird Films"
  }
  ["C"]=>
  array(1) {
    [0]=>
    string(21) "Content Panda Company"
  }
  ["F"]=>
  array(3) {
    [0]=>
    string(17) "Foggy Camel Films"
    [1]=>
    string(39) "For Yours and Everyone Else's Eyes Only"
    [2]=>
    string(24) "Fork and Spoon Marketing"
  }
  ["O"]=>
  array(1) {
    [0]=>
    string(21) "Opaque Cat Web Design"
  }
  ["T"]=>
  array(1) {
    [0]=>
    string(29) "Transparent Toaster Marketing"
  }
}*/

如您所见，PDO fetchAll已经为您对这些内容进行了分组。因为您希望它们采用特定的格式，所以您可以对它们进行简单的循环，而不必担心查看或比较这些值：

$structured = array();
foreach ($results as $firstLetter => $companies) {
    $structured[] = array(
        'letter' => $firstLetter,
        'result' => array_map(
            function($c) { return array('name' => $c); },
            $companies
            )
    );
}
$structured = json_encode($structured);

/* $structured is now =
[{
    "letter": "B",
    "result": [{
        "name": "Black Pencil theatre Company"
    }, {
        "name": "Bluebird Films"
    }]
}, {
    "letter": "C",
    "result": [{
        "name": "Content Panda Company"
    }]
}, {
    "letter": "F",
    "result": [{
        "name": "Foggy Camel Films"
    }, {
        "name": "For Yours and Everyone Else's Eyes Only"
    }, {
        "name": "Fork and Spoon Marketing"
    }]
}, {
    "letter": "O",
    "result": [{
        "name": "Opaque Cat Web Design"
    }]
}, {
    "letter": "T",
    "result": [{
        "name": "Transparent Toaster Marketing"
    }]
}]
*/