How to Fetch data from while loop inside a while loop

Question

I have this code and thing it's a weird but when I get the last_name from the first query which is a unique number and with the help of that I get the Spell of the last name from another table but when I try to get another data just below the second which ends it thinks that the first while loop has ended and then I can't get the rest of the data from the first while loop and I even tried this code but unfortunately it didn't worked for me

<div class="row">
    <?php 
    $sql2 = "SELECT id,last_name,age,height,weight,gender,education,work
             from profile where parent_key!='$parent_key' ";
    $retval2 = mysqli_query($con,$sql2);

    if(! $retval2 ) {
        die('Could not get data: ' . mysql_error());
    }

    while($row = mysqli_fetch_array($retval2, MYSQL_ASSOC)) {
    ?>

    <div class="row">
        <div class="col-lg-6 col-md-6 col-sm-6">
            <span>Unique Id</span>
            <h3><?php  echo $row['id'] ?></h3>
        </div>
    <div class="col-lg-6 col-md-6 col-sm-6">
        <span>Surname</span>
        <h3><?php $surname = $row['last_name']; 
            $sql3 = "SELECT last_name from surname where id='$surname'";
            $retval3 = mysqli_query($con,$sql3);
            if(! $retval3 ) {
                die('Could not get data: ' . mysql_error());
            }
            while($row = mysqli_fetch_array($retval3, MYSQL_ASSOC)) {
                echo $new_last_name ="{$row['last_name']}";
            ?>
            ?>
        </h3>
    </div>

</div>
<!-- /.row -->


<!-- /.row -->

<?php
    }
?>

<?php
    }
?>
</div>
<!-- /.row -->

And Thanks for helping in advance.

Answer 1

You can just use a single query to get the username and last name(surname) from other table

     $sql2 = "SELECT surname.last_name as surname,profile.id as id,profile.last_name,profile.age,profile.height,profile.weight,profile.gender,profile.education,profile.work
                     from profile 
left join surname on surname.id=profile.last_name
    where parent_key!='$parent_key' ";

Now You do not need to write the code twice to fetch the surname

Full Code below

<div class="row">
                <?p$sql2 = "SELECT surname.last_name as surname,profile.id as id,
                                profile.last_name,profile.age,profile.height,profile.weight,profile.gender,profile.education,
                                profile.work
                         from profile 
                            left join surname on surname.id=profile.last_name
                            where parent_key!='$parent_key' ";
                    $retval2 = mysqli_query($con,$sql2);

                    if(! $retval2 ) 
                    {
                        die('Could not get data: ' . mysql_error());
                    }



                    while($row = mysqli_fetch_array($retval2, MYSQL_ASSOC)) 
                    {
                        ?>

                        <div class="row">
                            <div class="col-lg-6 col-md-6 col-sm-6">
                                <span>Unique Id</span>
                                <h3><?php  echo $row['id'] ?></h3>
                            </div>
                            <div class="col-lg-6 col-md-6 col-sm-6">
                                <span>Surname</span>
                                <h3><?php echo $row['surname'];       ?>                                 

                                </h3>
                            </div>

                        </div>


                    <?php
                        }
                    ?>
           </div>

Answer 2

Inside of second while loop just change :

$row = mysqli_fetch_array($retval3, MYSQL_ASSOC)

to

$row1 = mysqli_fetch_array($retval3, MYSQL_ASSOC)

then you can use values from first while with $row['columnname'] and from second while with $row1['columnname']