PHP - How do I run mysql query in while loop?

Question

My original query was to select data from phpadmin table and display into table.

$sql="select * from table";
$result=mysql_query($sql) or die(mysql_error());

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[0]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[1]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[2]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[3]."</td>";    
}

Then I need to display another column using value of $myrow[0] to execute another query to get the value.

$sql="select * from table";
$result=mysql_query($sql) or die(mysql_error());

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);

    $id = $myrow[0];
    $sql="select amount from table2 where id like '%$id%'";
    $result = mysqli_query($conn, $sql);

    echo "<td align='left' bgcolor='$bgclr'>".$myrow[0]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[1]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[2]."</td>";    
    echo "<td align='left' bgcolor='$bgclr'>".$myrow[3]."</td>";    

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

            echo "<td align='left' bgcolor='$bgclr'>".$row[0]."</td>";echo "</tr>";
        }   
    }

And my page became blank. And the error was at

$result = mysqli_query($conn, $sql);

Is it the correct method or how should I do it?

usually you're better off using a join statement, so that you wouldn't need to repeat the query every iteration — Kevin
– Kevin, Commented Jul 6, 2018 at 6:15
It would be much better if you combined both these SQL statements into 1 query. — Nigel Ren
– Nigel Ren, Commented Jul 6, 2018 at 6:21

Lovepreet Singh · Accepted Answer · 2018-07-06 06:17:26Z

3

You overwrite variable $result while executing query inside the loop here:

$sql="select amount from table2 where id like '%$id%'";
$result = mysqli_query($conn, $sql);

Change variable name $result inside the loop and code will be like:

$sql="select amount from table2 where id like '%$id%'";
$result2 = mysqli_query($conn, $sql);

Also update it here:

while ($row=mysqli_fetch_row($result2)) {

            echo "<td align='left' bgcolor='$bgclr'>".$row[0]."</td>";echo "</tr>";
        }   
    }

answered Jul 6, 2018 at 6:17

Lovepreet Singh

4,8701 gold badge21 silver badges39 bronze badges

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

2 Comments

hatched Over a year ago

Thanks Mate !!! It was just so simple and I couldn't see my mistake !!! U saved my day !!!

Lovepreet Singh Over a year ago

Also don't forget to check the comments above about prepared statements!

Collectives™ on Stack Overflow

PHP - How do I run mysql query in while loop?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related