user2148116 user2148116 - 2 months ago 7
PHP Question

Print data from two different tables with a shared field (mysql and php)

I'm trying to bring data from two different tables where there is a shared field:
in table1, I have many fields and one of them is the employee Position which is a number.
in table2 I have two fileds: EmpPos(which is equal to Position in table1) and PosName.
Now, I want to print all employees' info from table1 but instead of printing Position(which is a number), I want to print its associated PosName from table2.
My following query is not working!

$sql ="SELECT * FROM table1, table2 WHERE table1.Position=table2.EmpPos";

$result = $conn->query($sql);


if ($result->num_rows > 0)
{

<tr>
<th>Name</th>
<th>Email</th>
<th>Position Name</th>
<th>phoneExt</th>

</tr>";


while($row = $result->fetch_assoc()) {

echo "<tr>";
echo "<td>" .$row['table1.FirstName'] ." " .$row['table1.LastName'] ."</td>";
echo "<td>" .$row['table1.Email'] ."</td>";
echo "<td>" .$row['table2.PosName'] ."</td>";
echo "<td>" .$row['table1.phoneExt'] ."</td>";
echo"</tr>";
}


thanks in advance

Answer

The issue is you didn't define any relationship between the tables. So you should be using INNER or LEFT JOIN for this:

SELECT * FROM Table1 m INNER JOIN
Table2 k ON k.EmpPos = m.Position

Or the following should do:

SELECT * FROM Table1 m LEFT JOIN
Table2 k ON k.EmpPos = m.Position

As you have a foreign key (Shared field), then it's easy and obvious to use joins.