Show all columns separate by specific column-CodePudding

In mysql i have 4 columns for example:

Table name is 'categories'

 ______________________________
|id | name | lastname    | slug |
 _______________________________|
|1  | Adam | Walkers     | Test1|
|2  | Nick | Myerson     | Test2|
|3  | John | Nicolas     | Test1|
|_______________________________|

The output should be:

Test1
Adam Walkers
John Nicolas

Test2
Nick Myerson

Should i make something like this? SELECT * FROM categories GROUP BY slug and use foreach?

CodePudding user response：

You're nearly there.

For an easy solution, if you're a beginner, I suggest you to use before it an ORDER BY statement

SELECT slug, name, lastname FROM categories ORDER BY slug

Result

Test1 | Adam | Walkers 
Test1 | John | Nicolas
Test2 | Nick | Myerson

and then group it by slug value with loops via code.

Example of implementation in php

$grouped_types = array();
$example_categories = [["Test1", "Adam", "Walkers"],["Test1", "John", "Nicolas"],["Test2", "Nick", "Myerson"]];
 
//SET categories grouped
foreach($example_categories as $value){
  $grouped_types[$value[0]][] = "{$value[1]} {$value[2]}";
}

//GET categories grouped
foreach(array_keys($grouped_types) as $slug){
    echo "<h1>{$slug}</h1>";
    foreach($grouped_types[$slug] as $names){
        echo "<p>{$names}</p>";
    }
}

Final result

Test1
Adam Walkers
John Nicolas

Test2
Nick Myerson

Alternative, use an GROUP_CONCAT statement as mentioned by saurabh kackar like this

SELECT slug, GROUP_CONCAT(CONCAT(name, ' ', lastname)) as names
FROM categories
GROUP BY slug;

Result

Test1 | Adam Walkers,John Nicolas |
Test2 | Nick Myerson              |

Finaly, use an explode function to split the names

Good work!

CodePudding user response：

If you are willing to do in a single query then you may use GROUP_CONCAT() function in MySQL.