Multiple table queries

fontener

I am trying to query multiple database tables... the end result I want is a listing of all the radio stations with the respective data (some of which is in different tables).. right now this code IS replacing the id's with their respective values from other tables.. however, I only get one table row for output... any help would be MUCH appreciated.. thanks in advance.

<?php
$color1 = "#CCFFCC"; 
$color2 = "#CEC5A6"; 

$result =mysql_query( "select r.*,f.format,m.md_name, p.city, p.state_province from station r,format f,md m, postal p where r.format_id = f.format_id and r.md_id = m.md_id and r.postal_id = p.postal_id");  

if (!$result) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  }
  while ($row = mysql_fetch_array($result))
  {
            $callabcs = $row["call_letters"];
            $station = $row["station_name"];
            $link = $row["station_website"];
            $phone = $row["station_phone"];   

            $md = $row["md_name"];
            $pd = $row["pd_name"];   

            $format = $row["format"];
            $level = $row["level"];   

            $city = $row["city"];
            $local = $row["state_province"];
            $notes = $row["additional_notes"];
}
?>

<table cellpadding="0" cellspacing="0" cellpadding="0" border="0" class="megatable"><tr><th>Call Letters</th><th>Station ID</th><th>Phone</th><th>MD</th><th>PD</th><th>Format</th><th>Level</th><th>City</th><th>Province</th><th>Notes</th></tr>
<?php
$row_count = 0; 
$row_color = ($row_count % 2) ? $color1 : $color2; 
echo "<tr><td bgcolor=\"$row_color\" id=\"hundo\">$callabcs</td><td bgcolor=\"$row_color\" id=\"hundo\"><a href=\"$link\">$station</a></td><td bgcolor=\"$row_color\" id=\"hundo\">$phone</td><td bgcolor=\"$row_color\" id=\"hundo\">$md</td><td bgcolor=\"$row_color\" id=\"hundo\">$pd</td><td bgcolor=\"$row_color\" id=\"hundo\">$format</td><td bgcolor=\"$row_color\" id=\"level\">$level</td><td bgcolor=\"$row_color\" id=\"hundo\">$city</td><td bgcolor=\"$row_color\" id=\"hundo\">$local</td><td bgcolor=\"$row_color\" id=\"hundofity\">$notes</td></tr>"; 
$row_count++;
?>
</table>

Weedpacket

The bit you want repeated for each row needs to be inside the loop; otherwise all you end up with is what's in the last row.

fontener

Pardon the lack of technical terminology...

The problem is that rather than just replacing the id with its value equivalent from another table.. it's setting conditionals...

I have attached a .jpg to further point out what I'm looking for..

i need to display the entire list of ALL stations within the radiostation table with all the elements within that table (call letters, phone number, etc)... some of these elements are IDs from other tables.. i.e. FormatID.. but I dont want to display the ID; rather their data equivalent from the other table..

the way I currently have it set up with the WHERE statement... it's making conditions and only displaying 1 row where the conditions are met...:mad:

Please Help! Thanks.🙂

Exangelus

As Weedpacket said you need to put the echo statement inside the loop. Otherwise the loop will run and than you'll post only the last row of the loop because the echo statement isn't in the loop.

The code should look like this:

<table cellpadding="0" cellspacing="0" cellpadding="0" border="0" class="megatable"><tr><th>Call Letters</th><th>Station ID</th><th>Phone</th><th>MD</th><th>PD</th><th>Format</th><th>Level</th><th>City</th><th>Province</th><th>Notes</th></tr> 

<?php 
$color1 = "#CCFFCC"; 
$color2 = "#CEC5A6"; 

$row_count = 0;
$result =mysql_query( "select r.*,f.format,m.md_name, p.city, p.state_province from station r,format f,md m, postal p where r.format_id = f.format_id and r.md_id = m.md_id and r.postal_id = p.postal_id");   

if (!$result) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  } 
  while ($row = mysql_fetch_array($result)) 
  { 
	$callabcs = $row["call_letters"]; 
	$station = $row["station_name"]; 
	$link = $row["station_website"]; 
	$phone = $row["station_phone"];    

	$md = $row["md_name"]; 
	$pd = $row["pd_name"];    

	$format = $row["format"]; 
	$level = $row["level"];    

	$city = $row["city"]; 
	$local = $row["state_province"]; 
	$notes = $row["additional_notes"]; 

$row_color = ($row_count % 2) ? $color1 : $color2; 
echo "<tr><td bgcolor=\"$row_color\" id=\"hundo\">$callabcs</td><td bgcolor=\"$row_color\" id=\"hundo\"><a href=\"$link\">$station</a></td><td bgcolor=\"$row_color\" id=\"hundo\">$phone</td><td bgcolor=\"$row_color\" id=\"hundo\">$md</td><td bgcolor=\"$row_color\" id=\"hundo\">$pd</td><td bgcolor=\"$row_color\" id=\"hundo\">$format</td><td bgcolor=\"$row_color\" id=\"level\">$level</td><td bgcolor=\"$row_color\" id=\"hundo\">$city</td><td bgcolor=\"$row_color\" id=\"hundo\">$local</td><td bgcolor=\"$row_color\" id=\"hundofity\">$notes</td></tr>";
$row_count++; 
} 
?> 

</table>

And to make it even better you can use a for statement instead of a while statement to count the amount of rows outputted. This makes the code easier readable and smaller with the same functionality:

for($row_count = 0; $row = mysql_fetch_array($result); $row_count++)

fontener

thanks guys.. I was using everything in the loop without results.. thats why I tried something else... turns out.. it wasn't a PHP problem to begin with.. the default values in the SQL tables werent matching when fields were left blank... thanks all.🙂