CODEWITHSUNDEEP
phpmysqlfor-loop
Mitch Mullvain
Mitch Mullvain

Reputation: 185

Pull the top three items from the database in php

I am trying to pull a list of genres from the database. I enter a list of genres into the database for each song, and then it (is supposed to) pull each song's genre into a list and order them by the top three most common occurrences.

The genres get put into a single text field in such a fashion:

(basic fashion, not an actual result):

blues rock, garage rock, hard rock

Here's my code:

$sql = "SELECT `song_name`, `song_genres` FROM `songs` WHERE `album_id` = '$songAlbumId'";
$query = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_array($query)){

    $song_name = $row['song_name'];
    $song_genres = explode(", ", $row['song_genres']);

    for ($i = 0; $i < count($song_genres); $i++){
        $count=array_count_values($song_genres);//Counts the values in the array, returns associatve array
        arsort($count);//Sort it from highest to lowest
        $keys=array_keys($count);//Split the array so we can find the most occuring key
        echo $keys[$i] . "<br>";

    }   
}

This ends up giving me:

Hard Rock
Garage Rock
Hard Rock
Garage Rock
Psychedelic Rock
Blues Rock
Garage Rock
Hard Rock

Also note there is nothing wrong with the album id or anything else. It is specifically to do with just the genres being ordered right.

Upvotes: 0

Views: 92

Answers (3)

Mitch Mullvain
Mitch Mullvain

Reputation: 185

I spent a couple days trying to figure it out. I ended up reworking the database and posting the songs genres into a database for the song, album, and band sections to easily pull from.

For anyone in the future who wants help with a problem like this, the solution is here: Merge multiple arrays into one array.

I appreciate the input from the other people though.

Upvotes: 0

Arjan
Arjan

Reputation: 9884

Normalize the genres. Use a genres table instead of a comma separated list, and an additional songs_genres table to link songs to genres. Then you can get the data from the database without further logic in php

SELECT g.name, COUNT(DISTINCT(sg.song_id)) cnt
FROM genres g
INNER JOIN songs_genres sg ON sg.genre_id = g.id
GROUP BY g.name
ORDER BY cnt DESC
LIMIT 3

Upvotes: 1

Mihai
Mihai

Reputation: 26804

You need another loop so your genres echo for each songname

while ($row = mysqli_fetch_array($query)){

    $song_name = $row['song_name'];
    $song_genres = explode(", ", $row['song_genres']);

foreach($song_name as $songname){
    for ($i = 0; $i < count($song_genres); $i++){
        $count=array_count_values($song_genres);//Counts the values in the array, returns associatve array
        arsort($count);//Sort it from highest to lowest
        $keys=array_keys($count);//Split the array so we can find the most occuring key
        echo $keys[$i] . "<br>";

    }   
                                }
}

Upvotes: 0

Related Questions