Merge multiple arrays into one array

Question

I am pulling a list of genres from a database and I am trying to sort them by the most common occurrences. But when I pull them from the database, they are still separated:

Array
(
    [0] => Blues Rock
    [1] => Garage Rock
    [2] => Hard Rock
)
Array
(
    [0] => Garage Rock
    [1] => Blues Rock
    [2] => Hard Rock
)

But I want it to come out into one array that can be sorted to pull the top genres. It should look like Genres: Garage Rock, Blues Rock, Hard Rock

Here's my code:

$genreSql = "SELECT `genres` FROM `song_genres` WHERE `album_id` = '$album_id' AND `band_id` = '$band_id'";
$queryGenre = mysqli_query($conn, $genreSql) or die (mysqli_error($conn));
while ($rowG = mysqli_fetch_array($queryGenre)){

    $genres = $rowG['genres']; // Goes into the db as a string. E.g. "Str1, Str2, Str3"
    $genres = explode(", ", $genres);

    echo "<pre>";
    print_r($genres);
    echo "</pre>";

I haven' gotten any further because I'm just trying to organize it all into one single array.

It might be that I don't know what I'm doing - because I'm still crap when it comes to arrays - but maybe veterans of php will find something.

show us your table structure, sample data in the table, and expected result table please — Alex
– Alex, Commented Feb 4, 2015 at 1:55
You need to learn about database normalisation. Don't store values in a single column separated by commas! Create a many-to-many relationship using an additional relationship table, and you can solve this using a simple SQL query. That's what RDBMSes are for! — deceze
– deceze ♦, Commented Feb 4, 2015 at 2:01

sectus · Accepted Answer · 2015-02-04 02:24:29Z

2

If you change database structure you will by able to make query that returns desirable values.

But now you can do something like this

$array_of_array_of_genres[] = [
    'Blues Rock',
    'Garage Rock',
    'Hard Rock',];
$array_of_array_of_genres[] = [
    'Garage Rock',
    'Blues Rock',
    'Hard Rock'];
$array_of_array_of_genres[] = [
    'POP',
    'Hard Rock'];
$all_genres = call_user_func_array('array_merge', $set_of_set_of_genres);
// {"0":"Blues Rock","1":"Garage Rock","2":"Hard Rock","3":"Garage Rock","4":"Blues Rock","5":"Hard Rock","6":"POP","7":"Hard Rock"}
$count_genres = array_count_values($all_genres);
// {"Blues Rock":2,"Garage Rock":2,"Hard Rock":3,"POP":1}
arsort($count_genres);
// {"Hard Rock":3,"Garage Rock":2,"Blues Rock":2,"POP":1}
$result = array_keys($count_genres);
// {"0":"Hard Rock","1":"Garage Rock","2":"Blues Rock","3":"POP"}

answered Feb 4, 2015 at 2:24

sectus

15.7k5 gold badges59 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Mullvain Over a year ago

I put $array_of_array_of_genres[] within the while loop and I changed call_user_func_array... to array_merge($array_of_array...). It did exactly what I want after I redid the tables in the database.

Dave · Accepted Answer · 2015-02-04 02:50:47Z

2

I would consider storing your data in a better format. A normalized database would look something like:

albums
-id
-name

genres
-id
-name

album_genre
-album_id
-genre_id

album_genre would hold the one (album) to many (genres) relationships:

album_id    genre_id
1           2
1           3
1           10

Getting totals is then as simple as:

SELECT 
    b.id AS genre_id, b.name AS genre_name, COUNT(a.genre_id) as total 
FROM 
    album_genre a 
JOIN 
    genres b ON a.genre_id = b.id
GROUP BY 
    a.genre_id 
ORDER BY 
    total DESC

This model is more flexible than storing a comma separate list. For example, it also makes the process of filtering albums by genre easy and precise.

answered Feb 4, 2015 at 2:50

Dave

3,6581 gold badge18 silver badges9 bronze badges

1 Comment

Mitch Mullvain Over a year ago

I used the other guy's code, but I did rework the database to pull genres from a joint database, so I appreciate that advice. Thanks a bunch!

Collectives™ on Stack Overflow

Merge multiple arrays into one array

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related