CODEWITHSUNDEEP
phpmysqlwhile-loop
Stantastic
Stantastic

Reputation: 102

MySQL - 2 while loops not working as expected

In my table i have 'artist' and 'song' now I want to output the artist name followed by all songs from this artist in my database. Using one while loop like i did in my code below doesnt work.

<div class="panel-group space" id="accordion"> <!-- ACCORDION FOR FOLDER/TRACK SELECTION -->

  <?php
    $con=mysqli_connect('localhost','root','raspberry','raspberryfm'); 
    $result = mysqli_query($con,"SELECT DISTINCT artist FROM playlist ORDER BY artist");

      while($row = mysqli_fetch_array($result)) {
        $artistnbsp = str_replace(' ', '_', $row['artist']);
        $artist = $row['artist'];

        $result2 = mysqli_query($con,"SELECT song FROM playlist WHERE artist= $artist");
        while($row = mysqli_fetch_array($result2)) {

        $song = $row['song'];
        echo '
          <div class="panel panel-default">
            <div class="panel-heading">
              <h4 class="panel-title align2 color2">
                <a data-toggle="collapse" data-parent="#accordion" href="#collapse'.$artistnbsp.'">
                  '.$artist.'
                  <button type="button" class="btn btn-success btn-sm pull-right pb">Play All</button>
                </a>
              </h4>
            </div>
          <div id="collapse'.$artistnbsp.'" class="panel-collapse collapse">
            <div class="panel-body color2">'
            . $song .
            '</div>
          </div>
          </div>';
        }
        }
    mysqli_close($con);
  ?>
</div>

Upvotes: 1

Views: 242

Answers (3)

Steve
Steve

Reputation: 20469

Well done fixing it yourself, however your nested queries are unnecicary and can cause performance issues.

With a little array manipulation you can have one query instead of one per artist:

<?php
$con=mysqli_connect('localhost','root','raspberry','raspberryfm');
$result = mysqli_query($con,"SELECT * FROM playlist");

$artists_songs=array();
while($row = mysqli_fetch_array($result)) {

  $artists_songs[$row['artist']][]=array(
      'song'=>$row['song'],
      'artist'=>$row['artist'],
      'artistnbsp'=>str_replace(' ', '_', $row2['artist'])
  );

}

foreach ($artists_songs as $artist) :?>
<div class="panel panel-default">
    <div class="panel-heading">
        <h4 class="panel-title align2 color2">
            <a data-toggle="collapse" data-parent="#accordion" href="#collapse<?php echo $artist[0]['artistnbsp'];?>">
                <?php echo $artist[0]['artist'];?>
                <button type="button" class="btn btn-success btn-sm pull-right pb">Play All</button>
            </a>
        </h4>
    </div>
    <div id="collapse<?php echo $artist[0]['artistnbsp'];?>" class="panel-collapse collapse">
        <div class="panel-body color2">
            <?php foreach($artist as $details):?>
                <p><?php echo $artist['song'];?></p>
            <?php endforeach;?>
        </div>
    </div>
</div>

<?php endforeach;?>

Upvotes: 1

Stantastic
Stantastic

Reputation: 102

I managed to find and fix the problem myself. Thank you guys for helping me that fast!

            <div class="panel-group space" id="accordion"> <!-- ACCORDION FOR FOLDER/TRACK SELECTION -->

            <?php
                $con=mysqli_connect('localhost','root','raspberry','raspberryfm'); 
                $result = mysqli_query($con,"SELECT DISTINCT artist FROM playlist ORDER BY artist");

                    while($row2 = mysqli_fetch_array($result)) {

                        $artistnbsp = str_replace(' ', '_', $row2['artist']);
                        $artist = $row2['artist'];


                        echo '
                            <div class="panel panel-default">
                                <div class="panel-heading">
                                    <h4 class="panel-title align2 color2">
                                        <a data-toggle="collapse" data-parent="#accordion" href="#collapse'.$artistnbsp.'">
                                            '.$artist.'
                                            <button type="button" class="btn btn-success btn-sm pull-right pb">Play All</button>
                                        </a>
                                    </h4>
                                </div>
                            <div id="collapse'.$artistnbsp.'" class="panel-collapse collapse">
                                <div class="panel-body color2">';

                                    $result2 = mysqli_query($con,"SELECT song FROM playlist WHERE artist= '$artist'");
                                    while($row3 = mysqli_fetch_array($result2)) {

                                        $song = $row3['song'];

                                            echo $song;
                                            echo "<br/>";

                                    }
                        echo '  
                                </div>
                            </div>
                            </div>';

                    }
                mysqli_close($con);
            ?>
        </div>

Upvotes: 1

Robbert
Robbert

Reputation: 6592

You're overwriting $row from your first loop with your second while loop. You should rename it to $row2 or something

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

  $artistnbsp = str_replace(' ', '_', $row['artist']);
  $artist = $row['artist'];

  $result2 = mysqli_query($con,"SELECT song FROM playlist WHERE artist= $artist");
  while($row2 = mysqli_fetch_array($result2)) {

Also, is $artist a numeric value? If not, the query above will fail unless you put quotes around $artist. You should also consider using mysqli's parameter feature instead of putting the variable directly in the query.

Upvotes: 1

Related Questions