CODEWITHSUNDEEP
phpmysqljson
ASH
ASH

Reputation: 253

how to group mysql results by alphabetical letters?

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'];

    }

--

Upvotes: 1

Views: 2639

Answers (2)

Kevin Vaughan
Kevin Vaughan

Reputation: 15180

You are using very much deprecated mysql functions which will be removed in the future. You should begin switching your knowledge over to the PDO interface for database access. Among many benefits of using PDO, it offers some nice functionality that would benefit you here. It should be noted that the usage I'm indicating below is not at all the most efficient way and if you are dealing with long lists, you should not do this this way. But, its fun to know how to get php to do most of the work for you.

Given a table 'stores' with one column 'name' and the following sample values:

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

You can first use a query to retrieve the data in a meaningful order:

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

Using the PDO classes, we can get that data grouped out directly:

<?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"
  }
}*/

As you can see, the PDO fetchAll has already grouped these for you. Because you want them in a specific format, you can do a simple loop over them without having to worry with looking at or comparing the values at all:

$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"
    }]
}]
*/

Upvotes: 2

Trent
Trent

Reputation: 3103

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.

Upvotes: 1

Related Questions