PHP/MySQL sort/arrange from tables

Question

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

FedeX · Accepted Answer · 2012-07-11 01:12:29Z

1

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

answered Jul 11, 2012 at 1:12

FedeX

4462 silver badges8 bronze badges

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

Comments

Mayko Faria · Accepted Answer · 2012-07-09 23:06:59Z

0

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.

answered Jul 9, 2012 at 23:06

Mayko Faria

33 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL sort/arrange from tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related