How do I select from multiple mySQL tables and output to PHP?

Question

I have at least 4 tables in MySQL for my PHP application (shortened it for this example)

Agents - Agent_ID - Agent_Name

Country - Country_ID - Country_Name

Job - Job_ID - Job_Type

Line_Items - Line_ID - Agent_ID - Country_ID - Job_ID

Now, I need to select from Line_Items where Agent_ID = 1, and instead of echo-ing the Agent_ID, Country_ID and Job_ID in their integers, I would like to output their names instead (Agent_Name, Country_Name, Job_Type).

1. How do I write the query?
2. How do I output this in PHP, using the well-used $result = mysql_query("select ...."); while($row = mysql_fetch_query($result)){echo .....};

Answer 1

You need to join the tables:

SELECT A.Agent_Name, C.Country_Name, J.Job_Type
FROM Line_Items LI, Agents A, Country C, Job J
WHERE LI.Agent_ID = 1 AND LI.Agent_ID = A.Agent_ID AND
      LI.Country_ID = C.Country_ID AND LI.Job_ID = J.Job_ID

Also, consider using a view, like so:

CREATE VIEW Line_Items_Detail
AS
  SELECT LI.Agent_ID, A.Agent_Name, C.Country_Name, J.Job_Type
  FROM Line_Items LI, Agents A, Country C, Job J
  WHERE LI.Agent_ID = 1 AND LI.Agent_ID = A.Agent_ID AND
        LI.Country_ID = C.Country_ID AND LI.Job_ID = J.Job_ID

Then, using the view, your query is as simple as:

SELECT Agent_Name, Country_Name, Job_Type
FROM Line_Items_Detail
WHERE Agent_ID = 1

With any of these queries, you can the use the PHP code that you wrote to output the results.

Hope it helps.

---

EDIT

Using the first query, your PHP would be something like this (simplified):

$query = "SELECT A.Agent_Name, C.Country_Name, J.Job_Type FROM Line_Items LI, Agents A, Country C, Job J WHERE LI.Agent_ID = 1 AND LI.Agent_ID = A.Agent_ID AND LI.Country_ID = C.Country_ID AND LI.Job_ID = J.Job_ID";
$result = mysql_query($query);
while($row = mysql_fetch_query($result)) {
    echo "Agent:" . $row['Agent_Name']."<br>";
    echo "Country:" . $row['Country_Name']."<br>";
    echo "Job:" . $row['Job_Type']."<br>";
}

Of course, you need to change the LI.Agent_ID if you need a different ID. You can just use a placeholder for it and replace for the correct ID, or concatenate the correct ID to the query.

Answer 2

Just going with JG here, use a join. But here's an alternate (and more accepted??) syntax for joining tables.

SELECT A.Agent_Name, C.Country_Name, J.Job_Type
FROM Line_Items LI
INNER JOIN  Agents A
ON LI.Agent_ID = A.Agent_ID
INNER JOIN Country C
LI.Country_ID = C.Country_ID
INNER JOIN Job J
ON LI.Job_ID = J.Job_ID
WHERE LI.Agent_ID = 1

You can use a LEFT JOIN, instead of inner join if either the country, agent, or job rows may not exist. Using an INNER JOIN, as I have done will cause no rows to be returned if for instance, there is no country.