Query displays first entry in table only - wrong!

Question

$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>";
}

This query outputs only the first Agent in AGents table, and Country table, and Job table... I want to output the corresponding Agent Name, Country Name and Job Type from those tables, based on the entries in the Line Items table.

Please help!

Answer 1

Try using mysql_fetch_array instead:

while($row = mysql_fetch_array($result)) {
    echo "Agent:" . $row['Agent_Name']."<br>";
    echo "Country:" . $row['Country_Name']."<br>";
    echo "Job:" . $row['Job_Type']."<br>";
}

Answer 2

I believe the problem lies it the fact you are attempting to pull values in your SQL that you have no access to in a single/simple SELECT statement. You should be using a JOIN statement for what you are attempting - pulling data from one or more tables based on common column data between two or more tables. A quick attempt at your SQL:

SELECT A.Agent_Name, C.Country_Name, J.Job_Type FROM Line_Items LI INNER JOIN 
( Agents A CROSS JOIN Country C CROSS JOIN Job J ) ON
( 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 );

EDIT: Sorry about missing the FROM - it does need it. I'm not trying to get a perfect SQL statment for you - you know what columns you need and what to match: it's just a rough sketch. Here is a link to the official MySQL join documentation.