sql joint query help

DRTechie

hello. I am having problems with this query and I am hoping someone could tell me what I am doing wrong. I want to grab data from 4 different tables and then parse the result. I can't even get the query to work so I don't know if the parse is going to work either...any tips? thanks.

$sql = " SELECT s.sitename, s.address1, s.address2, s.city, s.state, s.postal_code, s.status, s.description, s.notes, c.ckt_id, b.isdn_id, t.ticketnumber
FROM site s, site_circuit c, backup b, troublelog t
WHERE s.sitename = \"$sitename\"
AND s.sitename = c.sitename
AND c.ckt_id = b.ckt_id
AND c.ckt_id = t.ckt>id";

$result = @($sql, $connection) or die ("couldn't execute query.");

while ($row = mysql_fetch_array($result)){
$sitename = $row ['sitename'];
$address1 = $row ['address1'];
$address2 = $row ['address2'];
$city = $row ['city'];
$state = $row ['state'];
$postal_code = $row ['postal_code'];
$status = $row ['status'];
$description = $row ['description'];
$notes = $row ['notes'];
$ckt_id = $row ['ckt_id'];
$isdn_id = $row ['isdn_id'];
$ticketnumber = $row ['ticketnumber'];
}

Sxooter

If that is an exact cut and paste, the problem could be here:

AND c.ckt_id = t.ckt>id"

Note the > symbol where you should have a _ character.

DRTechie

Ok fixed that. I also found a misspelled field name in the SELECT statement. The only thing is that the parse is not working. I can only get the sitename to display but that's about it. any ideas?

DRTechie

This is driving me insane. First I though it might be the way I was parsing the data but now I really do think there's something wrong with my query. I tried running the query from sql and I got an empty set...I don't know where I am going wrong here.

$sql = " SELECT s.sitename, s.address1, s.address2, s.city, s.state, s.postal_code, s.site_status, s.description, s.notes, c.ckt_id, b.isdn_id, t.ticketnumber
FROM site s, site_circuit c, backup b, troublelog t
WHERE s.sitename = \"$sitename\"
AND s.sitename = c.sitename
AND c.ckt_id = b.ckt_id
AND c.ckt_id = t.ckt_id";

------------DB Design-------------------
TABLE site
site_id
sitename
address1
address2
city
state
postal_code
site_status
description
notes

TABLE site_circuit
ckt_id
sitename
loopback
router_type
router_name
carrier_id
corebox
coreport
local_loop
remote_loop

TABLE backup
backup_id
ckt_id
isdn_id
carrier_id
router_name
isdn_spids

TABLE troublelog
ticket_id
site_id
date
last_update
ticket_number
problem
status
initials
action

tomhath

I don't see a column named ckt_id in the troublelog table.