Add more then two while loop in one while with multiple tables

Question

I want to try multiple while in one while with multiple tables but it's doesn't work.

I have three table and I want a different-different value from table.

Here is my code.

$table1 = GROUPMASTER;
$table2 = LEDGERMASTER;
$table3 = TRANSECTIONMASTER;



$groupmasterdata = mysql_query("select * from $table1 WHERE groupname = 'Capital' OR groupname = 'Fund and Reserves' OR groupname = 'Long Term liabilities' OR groupname = 'Current Liabilities' ");
echo "<table align='center' border=1>";
echo "<tr>";
echo "<td colspan=2  style='text-align:center'><b> CAPITAL and LIABILITIES</b></td>";
echo "<td style='text-align:center'><b> Amount </b></td>";
echo "</tr>";
while($recored = mysql_fetch_array($groupmasterdata))
{
    $onegroupname = $recored ['groupname'];
    echo "<tr>";
    echo "<td colspan=3><b>" . strtoupper($onegroupname) . "</b></td></tr>";
    $groupnamelist = mysql_query("select groupname from $table1 WHERE under = '". $onegroupname ."'");
    while($data = mysql_fetch_array($groupnamelist))
    {
        $gname = $data ['groupname'];
        $getledgername = mysql_query("select ledgername from WHERE groupname = '". $gname ."'");
        while($lname = mysql_fetch_array($getledgername))
        {
            $gettocashbal = mysql_query("select *, SUM(amount) AS totalto from $table3 WHERE voucherto = '". $ledgername ."'"); 
            while($datato = mysql_fetch_array($gettocashbal))
            {
                $tototal = $datato['totalto'];
            }
            
            $getbycashbal = mysql_query("select *, SUM(amount) AS totalby from $table3 WHERE voucherby = '". $ledgername ."' "); 
            while($databy = mysql_fetch_array($getbycashbal))
            {
                $bytotal = $databy['totalby'];
            }
            
            $ledgertotal = $opbal - $tototal + $bytotal;
            
            echo "<tr>";
            echo "<td class='test'>" . $gname . "</td>";
            echo "<td>Rs. $ledgertotal</td>";
            echo "<td></td>";
            echo "</tr>";
        }
    }
    echo "<tr>";
    echo "<td style='text-align:right'>Total</td>";
    echo "<td></td>";
    echo "<td>Rs.</td>";
    echo "</tr>";
}
echo "</table>";

I want to try multiple while but doesn't work.

How can I solve these issues?

You might want to read [Why shouldn't I use mysql_* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?noredirect=1&lq=1) — brombeer, Oct 26 '20 at 09:13
You should also read up on database normalisation - it seems (though it might not be the case) that your first table is storing group _names_ in every row. You should have a table for groups, and store the unique-id instead. — droopsnoot, Oct 26 '20 at 09:19
Ok, Thank you.. Does it mean I will not be able to use multiple while loop..? — SATSANGI, Oct 26 '20 at 09:22
It's bad idea to do multiple while and move your data back and forth to join three tables. Just use JOIN: https://dev.mysql.com/doc/refman/8.0/en/join.html — astentx, Oct 26 '20 at 09:26
Anyone can help me in INNER JOIN QUERY, Because I tried but never succeed.... — SATSANGI, Oct 29 '20 at 12:45
**Hello, I tried subquery** `$getbycashbal = mysql_query("SELECT SUM(amount) from $table3 WHERE voucherby IN (select ledgername from $table2 WHERE groupname = '". $gname ."' )");` **I want to sum of amount column but this query doesn't work, Please anyone can help me, Thank you in advance** — SATSANGI, Oct 31 '20 at 07:29

score 0 · Answer 1 · answered Nov 06 '20 at 14:40

I tried different way to solve this...

//TABLE 1 = GROUPMASTER
$table1 = $_SESSION['table1'];
//TABLE 2 = LEDGERMASTER
$table2 = $_SESSION['table2'];
//TABLE 3 = TRANSECTIONMASTER
$table3 = $_SESSION['table3'];

echo "<table align='center' border=1>";
echo "<tr>";
echo "<td colspan=2  style='text-align:center'><b> CAPITAL and LIABILITIES</b></td>";
echo "<td style='text-align:center'><b> Amount </b></td>";
echo "</tr>";
echo "<tr>";

$liabilitytotal = 0;

    echo "<td colspan=3><b>CAPITAL</b></td></tr>";
    $categorytotal = 0;
    $groupnamelist = mysql_query("select groupname from $table1 WHERE under = 'Capital'");
    while($data = mysql_fetch_array($groupnamelist))
    {
        
        $grouptotal = 0;
        $gname = $data ['groupname'];
        echo "<tr>";
        echo "<td class='test'>" . $gname . "</td>";
        $ledgername = mysql_query("SELECT ledgername from $table2 WHERE groupname = '". $gname ."' ");
        while($datato = mysql_fetch_array($ledgername))
        {
            $lname = $datato['ledgername'];
            $getbytotalquery = mysql_query("SELECT SUM(amount) FROM $table3 WHERE voucherby = '". $lname ."' ");
            $gettototalquery = mysql_query("SELECT SUM(amount) FROM $table3 WHERE voucherto = '". $lname ."' ");
                  
                  $getopbal = mysql_result($getopbalquery,0,0);
                  $getbytotal = mysql_result($getbytotalquery,0,0);
                  $gettototal = mysql_result($gettototalquery,0,0);
                  
                  $result = $getopbal - $gettototal + $getbytotal;
          
            if ($result == null)
            {
               $result = 0;
            }
            $grouptotal = $grouptotal + $result;
        }
        $categorytotal = $categorytotal + $grouptotal;
        echo "<td style='text-align:right'>$grouptotal</td><td></td></tr>"; 
    }
    $liabilitytotal = $liabilitytotal + $categorytotal;
    echo "<tr>";
    echo "<td></td>";
    echo "<td></td>";
    echo "<td style='text-align:right'>$categorytotal</td>";
    echo "</tr>";
echo "</table>";```

I tried my best..

And I will try to also solve this query to another way...

Thank you.. I think and I hope It's helpful..

Add more then two while loop in one while with multiple tables

1 Answers1