虽然循环不会停止迭代数组

Question

I have a php file in which I created a class Bank - has 2 methods - first one produces 5 users and populates random transactions in the database The second one should display the balance of the users.

I've searched on the site and no one had such an example on here.

I've successfully connected to database and wrote a (not a really good) query to fetch me all data from both tables.

$query = "SELECT * FROM users, transactions";

The problem I'm facing is that I'm not that good at php and the syntax is driving me crazy - my while loop loops through the result that the query produces but it does not stop after it goes through all the result rows in the array - it just repeats itself endlessly.

$connection = mysqli_connect('localhost', 'root', 'root', 'ipromapp', '3306');

class Bank
{
    function balance() 
    {
        global $connection;

        if($connection)
        {
            echo ("Connection online<br>");
        }

        $query = "SELECT * FROM users, transactions";

        $result = mysqli_query($connection, $query) or die(mysql_error());

        echo "<table border='1px'>";
        echo "<tr>";
        echo "<td> ID </td>";
        echo "<td> Name </td>";
        echo "<td> Balance </td>";
        echo "</tr>";

        while($row = mysqli_fetch_array($result))
        {
            $id = $row[0];
            $name = $row[1];
            $balance = $row[2];

            echo "<tr>";
            echo "<td> {$id} </td>";
            echo "<td> {$name} </td>";
            echo "<td> {$balance} </td>";
            echo "</tr>";
        }
        echo "</table>";
    }
}
$bank = new Bank;
$bank->randomize();
$bank->balance();

The results are as following ( there are 5 users in the database atm ) http://prntscr.com/mf6edg

It just goes and goes.

I'm 100% it is the while loop but I don't have the knowledge about how to change the condition inside it, if one would be kind enough to please help me out.

Answer 1

Your query will select all the rows of the second table for each row of the first table (your select produces a cartesian product).

Assuming your tables are related by a key eg: user_id you should try a join

  SELECT * 
  FROM users u 
  INNER JOIN  transactions t  on u.user_id =  t.user_id 

in this way you should obtain only transaction related to the specific user_id