在单个查询中使用2个表的方法

Question

I had an assessment on pulling data from a table but i had to use two tables to do a calculation, i figured out a way to do it but it doesn't look good neither it would be good to keep using this way

        $query = mysqli_query($connection, "SELECT * FROM metal");
                $query2 = mysqli_query($connection, "SELECT * FROM conversion WHERE type = 'EUR'");
                $data2 = mysqli_fetch_array($query2);
                $query3 = mysqli_query($connection, "SELECT * FROM conversion WHERE type = 'USD'");
                $data3 = mysqli_fetch_array($query3);

                while($data = mysqli_fetch_array($query)){
                    echo "<tr>" . "<td>" . $data['metal'] . "</td>" . "<td>" . $data['cost'] . "</td>" . "<td>" . ($data['cost'] * $data3['amount']) . "</td>" . "</tr>";
                }

This grabs cost on metal type then using another table calculates cost in Euro and USD, I don't want to use 3 querys and fetch them 3 times as it isn't computationaly effective is there a better way to do it

Answer 1

You'd need at least two, since you're fetching from two essentially unrelated tables

SELECT * FROM metal
SELECT * FROM conversion WHERE type IN ('EUR', 'USD')

the second query will fetch BOTH records, and then you'll just need some extra code to sort out which record is which.