can we simplify “while” inside of “while”?

i need some little help ,,,

I have a data like this.., (I use php mysql)

Initially there was no problem with this, but as time goes by, my data is getting bigger and bigger. finally its made my program slow .. I think maybe because I used “while” inside of “while”. make SQL is called multiple times. is there a solution to make it faster ??

I have hundreds of data in tb1 and thousands in tb2 T.T

Answer

You should not be using loops to iterate over tables. SQL is an inherently set based declarative language. So, you should just join the tables, order the result set, and then use a single loop with presentation logic. Use this query:

SELECT tb1.id_a, tb2.id_b, tb2.data
FROM tb1
INNER JOIN tb2 ON tb2.id_a = tb1.id_a

Then, use this PHP script:

echo "<table>";
echo "<tr><th>No.</th><th>id_b</th><th>data</th></tr>";
$a = null;
while ($row = mysql_fetch_array($result)) {
    if ($a == null || $row['id_a'] != $a) {
        echo "<tr>";
        echo "<td>" . $row['id_a'] . "</td>";
        echo "<td colspan=2>data" . $row['id_a'];
        echo "</tr>";
        $a = $row['id_a'];
    }
    echo "<tr>";
    echo "<td></td>";
    echo "<td>" . $row['id_b'] . "</td>";
    echo "<td>" . $row['data'] . "</td>";
    echo "</tr>";
}
echo "</table>";