php table row among each other

Question

I have a table and my results from MySql. the first row and the second are among each other but the third not.

Code:

$db = mysqli_connect($host, $user, $pass,$database);

if($db){

    $h.= "";
    $h.= "<form><table class='table table-striped table-hover'>";
    $h.= "<tr>";
    $h.= "<th>Nr.</th>";
    $h.= "<th>Albums</th>";
    $h.= "<th>Artiest</th>";
    $h.= "<th>Nummer</th>";
    $h.= "<th></th>";
    $h.='<ul class="nav nav-pills" role="tablist">';
    $h.="   <li role='presentation' class='active'><a href='http://localhost:8888/?action=album '>Albums<span class='badge'></span></a></li>";
    $h.='   <li role="presentation"><a href="http://localhost:8888/?action=songs">Songs</a></li>';
    //$h.='<input type="button" class="btn btn-primary style="float: right"><a href="http://localhost:8888/?action=add-album">';
    $h.= "<td style='text-align:right;'><a href='/?action=add-album' class='btn btn-primary'>VOEG TOE</a></td>";
    $h.='</ul>';
    $h.="<br>";
    $h.= "</tr>";

    $sql = mysqli_query($db,"SELECT * FROM albums");
    $sql1 = mysqli_query($db,"SELECT * FROM artiesten");

    if($sql){

        if(mysqli_num_rows($sql)>0){


            while ($row = mysqli_fetch_assoc($sql)){

                $h.= "<tr>";
                $h.= "<td>".$row['id']."</td>";
                $h.= "<td>".$row['albumName']."</td>";

                while ($row1 = mysqli_fetch_assoc($sql1)){

                 $h.= "<td>".$row1['artiest']."</td>";

                }

                $h.= "<td>";
                $h.= "<td style='text-align:right;'><a href='/?action=show-song&id=".$row['id']."'' class='btn btn-primary'>Zie nummers</a>&nbsp";
                $h.= "<style='text-align:right;'><a href='/?action=delete-album&id=".$row['id']."'' class='btn btn-danger'>VERWIJDER</a></td>";
                $h.= "</tr>";

            }   

        }else{

            echo "<tr>No Recore Found</tr>";

        }

        $h.= "</table></form>";

    echo $htop;
    echo $h;
    echo $hbot;

    }else{

       echo "Query error".mysqli_error($db);

    }

 }else{

  echo "connection error".mysqli_connect_error();

 }

Answer 1

You need to create an 'albumid' in the second table so you can print the right artist with the right album.

then amend the code to something like:

$sql = mysqli_query($db,"SELECT * FROM albums");  //get the albums

if($sql) {
    if(mysqli_num_rows($sql)>0) {  //if you have some albums to print...
        while ($row = mysqli_fetch_assoc($sql)) {

            //start printing a row
            $h.= "<tr>";
            $h.= "<td>".$row['id']."</td>";
            $h.= "<td>".$row['albumName']."</td>";

            //now get the artiest for the album by querying the correct albumid
            $albumid = $row['id'];
            $sql1 = mysqli_query($db,"SELECT * FROM artiesten where albumid = '$albumid' ");

            while ($row1 = mysqli_fetch_assoc($sql1)){

                 $h.= "<td>".$row1['artiest']."</td>";  //print the right artiest
            }

            $h.= "<td style='text-align:right;'><a href='/?action=show-song&id=".$row['id']."'' class='btn btn-primary'>Zie nummers</a>&nbsp";
            $h.= "<style='text-align:right;'><a href='/?action=delete-album&id=".$row['id']."'' class='btn btn-danger'>VERWIJDER</a></td>";
            $h.= "</tr>";

        }   

    }else{

        echo "<tr>No Recore Found</tr>";

    }