sql output for 3 tables with multiple data

[tinyblue]

Hello all,

my first post here and have a question to save some of my hair that i have been pulling out. I am a beginner at php/mysql that i have been dabbing into lately and have a question about an output i am trying to do.

I have 3 tables and it is about golf.

1) table -> course
field (1) -> course_id [ int ]
field (2) -> cname [ course name ]

2) table -> hole
field (1) -> course_id [ int ]
field (2) -> hole_no [ int ]
field (3) -> tee_color_id [ int ]

3) table -> tee_color
field (1) -> tee_color_id [ int ]
field (2) -> color [ varchar ]

what i am trying to do is output table (1) course name with all the associates tee color, table (3) with all the hole numbers assocaited with the course and tee color. here is the code i have so far and it is only outputing 1 row only. I know i need to search all the rows, but i dont know where to go.

this is what i would like to show:

Course name [ single course name ] - Tee color [ specific tee color ] - Hole numbers [ all the hole numbers 1 - 9 or 1 - 18 ]

<?php
// GOLF HOLE LIST BELOW

$columns = 9;

$holequery = "SELECT * FROM course AS a, hole AS b, tee_color AS c WHERE a.course_id = b.course_id AND b.tee_color_id = c.tee_color_id AND b.hole_no ORDER BY a.course_id";
$result = mysql_query($holequery);

$num_rows = mysql_num_rows($result);

//we are going to set a new variables called $rows
$rows = ceil($num_rows / $columns);

echo "<TABLE BORDER=\"1\">\n";

//to do this display, we will need to run another loop
//this loop will populate an array with all our values
while($row = mysql_fetch_array($result)) {
$coursename = $row['cname'];
$tcolor = $row['color'];
$data[] = $row['hole_no'];

//echo "<TABLE BORDER=\"1\">\n";

echo "<tr>\n";
echo "  <td rowspan=\"$rows\">\n";
echo "$coursename\n";
echo "  </td>\n";
echo "  <td>\n";
echo "$tcolor\n";
echo "  </td>\n";

//here we changed the condition to $i < $rows
for($i = 0; $i < $num_rows; $i++) {
//$row = mysql_fetch_array($result);
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
echo "<TD>" . $row['hole_no'] . "</TD>\n";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
}

}
echo "</TABLE>\n";
?>

oh yeah my output:

Monarch Bay White

1 1 1 1 1 1
Monarch Bay White

2 2 2 2 2 2
Monarch Bay White

3 3 3 3 3 3
Monarch Bay White

4 4 4 4 4 4
Monarch Bay White

5 5 5 5 5 5
Tilden Park Middle

1 1 1 1 1 1

should be:

Monarch Bay | white | 1 2 3 4 5
Tilden Park | Middle | 1

etc

Thanks for your time viewing this issue for me

[tomhath]

Not sure if something is missing from your query or the "AND b.hole_no" is a mistake.

Anyway, here are a couple of suggestions:

1) Write your joins by explicitly stating INNER JOIN, LEFT JOIN, etc. MySQL will accept the shorthand style but it's it's better to learn the right way so you'll be used to it when the queries get more complicated.

2) Instead of "SELECT *", list the columns you want to return. It's more efficient and less likely to break if you ever add a column to one of the tables involved.

The problem with your output is the "for" loop. You shouldn't loop on the number of rows returned, since that is all rows for all courses; and you since you don't fetch a row inside the loop it's stuck on the same hole number each time through. Get rid of the "for" loop, fetch a row each time through the "while" loop and start a new row of display each time you see a new course name, otherwise add another <TD> column. You don't need all that logic about number of columns, just check for a change of course name..

[tinyblue]

Thanks for the reply

I have gotten part of the solution, but i am stuck on your step 3 solution looking for the amount of holes for each course.

this is where i am at and made it much simplified

code

<?php
// GOLF HOLE LIST BELOW

//$holequery = "SELECT * FROM course AS a, hole AS b, tee_color AS c WHERE a.course_id = b.course_id AND b.tee_color_id = c.tee_color_id AND b.hole_no ORDER BY a.course_id";

$holequery = "
SELECT
*
FROM course
LEFT JOIN hole ON course.course_id = hole.course_id
LEFT JOIN tee_color ON hole.tee_color_id = tee_color.tee_color_id
ORDER BY course.course_id";

$result = mysql_query($holequery);

$num_rows = mysql_num_rows($result);

//we are going to set a new variables called $rows
//$rows = ceil($num_rows / $columns);

echo "<TABLE BORDER=\"1\">\n";

//to do this display, we will need to run another loop
//this loop will populate an array with all our values
while($row = mysql_fetch_array($result)) {
$coursename = $row['cname'];
$tcolor = $row['color'];
$data = $row['hole_no'];

echo "<tr>\n";
echo "  <td>\n";
echo "$coursename\n";
echo "  </td>\n";
echo "  <td>\n";
echo "$tcolor\n";
echo "  </td>\n";
echo "<TD>$data</TD>\n";
}
echo "</TR>\n";
echo "</TABLE>\n";

?>

the output is yes looking at each row, but i dont know where to make it look at each hole for each course. this is my output

Monarch Bay White 1
Monarch Bay White 2
Monarch Bay White 3
Monarch Bay White 4
Monarch Bay White 5
Monarch Bay Black 1
Tilden Park Middle 1
Harding Park

Metropolitan Golf

Monarch Bay - Tony Lema Golf Course

I need it to get to the point of

Monarch Bay White 1 2 3 4 5
Monarch Bay Black 1
Tilden Park Middle 1
Harding Park

Metropolitan Golf

Monarch Bay - Tony Lema Golf Course