kinda complicated attempt at displaying a 3rd level variable

[badgoat]

Heya!

I have a table for firms we work with:
firm_name
address1
address2
city_id
state_id
zip

I also have a table for the city
city_id
city_name

and also for the state
state_id
state_name

I am running into trouble when I try to display the city by name rather than by the id. Here's a line where I am trying to display a row with the firm_name, address1, address2, city_name (from city table) and state_name (from state table)

What am I doing wrong?

<TR>  ';
   mssql_data_seek($queryresult,0)  ;
while($row = mssql_fetch_array($queryresult)) {

$firm_id = $row['firm_id'];
$sqlquery1 = "SELECT * from firm WHERE firm_id = '$firm_id'";
$query1 = mssql_query($sqlquery1) or die (mssql_error());
$row1 = mssql_fetch_array($query1);



if ($tdcount == 1) echo "<tr>";
echo '
    <td> '.$row['id'],'</td>
    <td> '.$row['last_name'],',  '.$row['first_name'],'</td>
    <td>'.$row1['firm_name'].'</td>  ';
    $query = "SELECT * FROM con_info_tech WHERE id = '$id'";
    $result = mssql_query($query);
    $row = mssql_fetch_array($result);

$city_id = $row2['city_id'];
$query2 = "SELECT * FROM city WHERE city_id = '$firm_city_id'";
$result2 = mssql_query($query2);
$row2 = mssql_fetch_array($result2);

echo'
<td> '.$row2['city_name'].'</font></td>  ';
 if ($tdcount == $numtd) {
 echo "</tr>";
 $tdcount = 1;
 } else {
 $tdcount++;
 }
}

etc.

I think the problem arises from trying to get the city_name from the table, which is pulling the firm name with city id from the 2nd table, while trying to display the record of the whole darn thing. Of course I can echo the id, but when I try to display the city_name, it fails.

Anyone have a clue what I am doing wrong?

[mmonaco]

I'm not exactly sure what the con_info_tech table is... but say you have three tables, tblFirms, tblCities, tblStates....

SELECT DISTINCT f.*, c.city_name AS cityName, s.state_name AS stateName
FROM tblFirms f, tblCities c, tblStates s 
WHERE c.city_id=f.city_id AND s.state_id=f.state_id;

Will return all the fields from firms, the name of the city as cityName and the name of the state as stateName.

[jazz_snob]

Will this help?

$query = "select firm.*, city.city_name from firms left join city on firms.city_id=city.id";

Can also adapt likewise to get state name.