CODEWITHSUNDEEP
phpmysqlmysqli
Anderson Oliveira
Anderson Oliveira

Reputation: 251

PHP/MySQL sort/arrange from tables

I'm starting to learn some PHP/Mysql and I'm strugle how can I make this works.

I have 2 tables. One called ALBUM and the other TRACKS On the ALBUM I have all the album info that I need (title, release date, cover) and on the TRACKS I have all the tracks name and the album id related.

I could make the SELECT works fine and grab all the content, but how can I filter by ALBUM name for example and display all tracks related for that album?

Right now I have this

define("MYSQLUSER", "root");
define("MYSQLPASS", "root");
define("HOSTNAME", "localhost");
define("MYSQLDB", "music");

$connection = new mysqli(HOSTNAME, MYSQLUSER, MYSQLPASS, MYSQLDB);

if ($connection->connect_error) {
  die('Could not connect: ' . $connection->connect_error);
}else{

$query = "SELECT * from album AS a JOIN tracks AS t ON t.album = a.id ORDER BY a.id,  t.trackNumber, t.album";

$result_obj = '';
$result_obj = $connection->query($query);

while($result = $result_obj->fetch_array(MYSQLI_ASSOC)){
  $items[] = $result;
}


 foreach ($items as $item){
  echo "<ul><li>";
  echo $item['name'];
  echo " - "
  echo $item['file'];
  echo "</li></ul>";
}

Problem is that will output all album/tracks separate from each other.

Like this:

<ul>
<li>Album 1 - file 1</li>
</ul>

<ul>
<li>Album 1 - file 2</li>
</ul>

<ul>
<li>Album 2 - file 1</li>
</ul>

How can I make the albums on the same ul/li tags? I knwo is my foreach loop. But what I can do?

So will be like this:

<ul>
<li>Album 1 - file 1, Album 1 - file 2, Album 1 - file3</li>
<li>Album 2 - file 1</li>
</ul>

Thanks

Upvotes: 0

Views: 126

Answers (2)

FedeX
FedeX

Reputation: 446

You can leave the query as it is and just improve the loop as follow:

echo "<ul>";
$first = true;
$previous_album = '';
while($result = $result_obj->fetch_array(MYSQLI_ASSOC)){
    if( $previous_album!=$result['album'] ){
        $previous_album = $result['album'];
        if( !$first ){
            echo "</li>";
        }else{
            $first = false;
        }
        echo "<li>";
    }else{
        echo ", ";
    }
    echo $result['name'];
    echo " - ";
    echo $result['file'];
}
echo "</li></ul>";

Also note that I'm doing everything in just one loop, right now you are using two loops (one to retrieve the rows from the DB and the other one to display the results). I'm doing everything in just one loop, which will increase the performance of your app.

Let me know if you need further help. FedeX

Upvotes: 1

Mayko Faria
Mayko Faria

Reputation: 3

You will have to use the GROUP BY directive.

SELECT * from album AS a JOIN tracks AS t ON t.album = a.id GROUP BY a.id ORDER BY a.id,  t.trackNumber, t.album

You can use other parameters on this directive, producing different result, pick the one the suits the best for you.

Upvotes: 0

Related Questions