how to retrieve data using two while loops

Question

The first query works fine and it retrieves the class id but I have a problem with the second query as it does not retrieve the child name

<select name="child-name" id="Schild">
<?php
    $username = $_SESSION['username'];
    $sql1 = "SELECT Class_id FROM Class WHERE Emp_id = '$username'";
    $result = mysqli_query($database,$sql) or die(mysqli_error($database));

    while($row = mysqli_fetch_array($result))
    {
        $classID = $row['Class_id'];
        $sql2 = "SELECT Name FROM Children WHERE Class_id = '$classID'";
        $result2 = mysqli_query($database,$sql2) or die(mysqli_error($database));
        while($row = mysqli_fetch_array($result2))
?>
    <option><?php echo $row['Name'];?></option>  

<?php 
        }
?> 
</select>

Your code is vulnerable to SQL injection attacks. You should use [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) or [PDO](http://php.net/manual/en/pdo.prepared-statements.php) prepared statements as described in [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 07 '17 at 14:27
You are using `$row` in both resultset fetches. Therefore destroying it for the outer loop — RiggsFolly, Apr 07 '17 at 14:27
you use the same row variable. just have another variable name. also turn error reporting — Rotimi, Apr 07 '17 at 14:28
... whereas I'm still trying to work out what *"restive data"* is ... is it a bit fidgety? Aaaaaaah *retrieve* maybe? — CD001, Apr 07 '17 at 14:28
@RiggsFolly - yeah, there's a pending grammar edit on the post now from *Akin*; needs a couple more approvals — CD001, Apr 07 '17 at 14:38
@CD001 yup I approved already I am afraid, still need 1 more — RiggsFolly, Apr 07 '17 at 14:39

RiggsFolly · Answer 1 · 2017-04-07T15:17:05.397

1

You are using $row in both resultset fetches. Therefore destroying it for the outer loop

While testing always add these lines, specially if you are testing on a live/hosted server where error reporting will almost definitely be turned off

<?php
    ini_set('display_errors', 1); 
    ini_set('log_errors',1); 
    error_reporting(E_ALL); 
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
?>

<select name="child-name" id="Schild">
<?php
    $username = $_SESSION['username'];
    $sql1 = "SELECT Class_id FROM Class WHERE Emp_id = '$username'";
    $result = mysqli_query($database,$sql1) or die(mysqli_error($database));
    // Late breaking fix             ^^^^

    while($row = mysqli_fetch_array($result))
    {
        $classID = $row['Class_id'];
        $sql2 = "SELECT Name FROM Children WHERE Class_id = '$classID'";
        $result2 = mysqli_query($database,$sql2) or die(mysqli_error($database));
        while($row2 = mysqli_fetch_array($result2)) {
    // fix    ^^^^^
?>
    <option value="<?php echo $row2['Name'];?>"><?php echo $row2['Name'];?></option>  
<?php 
    // fix             ^^^^^
        }
?> 
</select>
<?php
    // also add terminator for first while loop
    }
?>

edited Apr 07 '17 at 15:17

answered Apr 07 '17 at 14:29

RiggsFolly

93,638
21
103
149

Any different results? – RiggsFolly Apr 07 '17 at 14:36
See suggested error reporting additions to make to your script – RiggsFolly Apr 07 '17 at 14:38
1

Are u sure is the children equal that class id? @SamahAbuSaud did you try run the query from php my admin and hand code the id? – Masivuye Cokile Apr 07 '17 at 14:46
yes, the class id retrieved successfully, but the problem is retrieving the names with that specify class id – Samah Abu Saud Apr 07 '17 at 14:51
1

does a name with that class id exist? – Masivuye Cokile Apr 07 '17 at 15:04
@RiggsFolly you made a error here : `$sql1 =....` then `$result = mysqli_query($database,$sql)` the query never runs – Masivuye Cokile Apr 07 '17 at 15:14
@MasivuyeCokile No you just spotted the other error that I missed. Thank you. So much for the first query working!! I wonder what code they are really running??? – RiggsFolly Apr 07 '17 at 15:15
1

@RiggsFolly I believe that was the reason he's second query failed. Op was lying when he said he got results from the first query, that's impossible 1st query never execute – Masivuye Cokile Apr 07 '17 at 15:16
@SamahAbuSaud Please check the code again. I have made another fix – RiggsFolly Apr 07 '17 at 15:19
@MasivuyeCokile Got go now. Maybe catch you later – RiggsFolly Apr 07 '17 at 15:21
@RiggsFolly.. I added the new part that you add in the code but nothing happen – Samah Abu Saud Apr 07 '17 at 15:33

how to retrieve data using two while loops

1 Answers1