CODEWITHSUNDEEP
phpmysql
Gita Prasetya Adiguna
Gita Prasetya Adiguna

Reputation: 389

Most Efficient way to MySQL Query inside query

i'm just learning PHP, and i have a question Let just say, i have MySQL table "A"

    Name | Job
 --------|---------
    Jynx | 1
   Micah | 4
   Nancy | 3
  Turah  | 1

And another table "B"

JobId  | JobName
-------|-----------
   1   |  Lawyer
   2   |  Architec
   3   |  Farmer
   4   |  Mage
   5   |  Warrior

So supposedly in php i want to draw table that showed the content of table "A", but instead of displaying number at the "Job" colomn, they each display Job names from Table "B". What is the most efficient way to do that? For now, i just thinking of using

$conn = My database connect setting
$sql = "SELECT * FROM tableA  ORDER BY Name";
$result = $conn->query($sql);
while($row = mysqli_fetch_assoc($result)) {
    echo "<tr><td>". $row['Name'] ."</td><td>";
    $sql2 = "SELECT * FROM tableB WHERE JobId=$row['Job']";
    $result2 = $conn->query($sql2);
    while($row2 = mysqli_fetch_assoc($result2)) {
        echo "<td>". $row2['JobName'] ."</td></tr>;
       }
    }

But wouldn't it take a lot of calculating proccess if there is multiple similliar colomn with hundreed of rows?

Is there any more efficient way to do this?

Sorry for my bad english

Thank you for your attention.

Upvotes: 0

Views: 70

Answers (2)

akronymn
akronymn

Reputation: 2446

A join is definitely the way to go here.

SELECT a.Name, b.JobName 
FROM tableA a
JOIN tableB b on (a.Job = b.JobId)
ORDER BY a.Name

Upvotes: 2

Gita Prasetya Adiguna
Gita Prasetya Adiguna

Reputation: 389

Well, it is not easy to learn about JOIN, no time for now, but i will get to learn it latter.

As for now, i just get the idea to just use ARRAY instead So before i draw the main table, i assign the supportive table (Table B) into associative array

$sql = "SELECT * FROM tableB  ORDER BY Id";
$result = $conn->query($sql);
while($row = mysqli_fetch_assoc($result)) {
$job[$row['JobId']] = $row['JobName'];
}

And at the main table

$sql = "SELECT * FROM tableA  ORDER BY Name";
$result = $conn->query($sql);
while($row = mysqli_fetch_assoc($result)) {
    echo "<tr><td>". $row['Name'] ."</td><td>". $job[$row['Job']];
    }

Upvotes: 0

Related Questions