Help with display of results

clarkm

I have 2 tables. One contains Poets, and the other contains Poems linked by poet_id.

I want to Show ALL poets with all poems they wrote displayed under their names like:

Poet #1
poem #1
poem #2
poem #3

Poet #2
poem #4

What I have now is:

Poet #1
poem #1

Poet #1
poem #2

Poet #1
poem #3

Poet #2
poem #4

I understand why I am getting this result. What I am unable to see through is how to display it with all poems showing under each poet's name like the first example. Any help on this would be greatly appreciated.

Code I have so far:

include("/usr/home/iamusic/read_connect.inc");

$sql = "select * from poems, poets WHERE poems.poet_id = poets.poet_id";
$sql_result = mysql_query($sql,$connection) or die ("Couldn't get list");

$numresults = mysql_num_rows($sql_result);

<head>
	<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
	<title>All Poets</title>
</head>

<body>
<center>
<table>

while ($row = mysql_fetch_array($sql_result)) {
$title = $row["title"];
$poem_id = $row["poem_id"];
$poet_id = $row["poet_id"];
$first_name = $row["first_name"];
$last_name = $row["last_name"];

echo "

<tr>
<td>$first_name $last_name</td>
</tr>

<tr>
<td><a href=show_poem.php3?poem=$poem_id>$title</td>
</tr>";

}

echo "</table></center>";

mysql_free_result($sql_result);
mysql_close($connection);

</body>

Thank you for the help.

Mark

rrijnders

<?php

include("/usr/home/iamusic/read_connect.inc");

$sql = "select * from poems, poets WHERE poems.poet_id = poets.poet_id";
$sql_result = mysql_query($sql,$connection) or die ("Couldn't get list");

$numresults = mysql_num_rows($sql_result);

<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<title>All Poets</title>
</head>

<?php

$last_poet_id = '';

while ($row = mysql_fetch_array($sql_result)) {
$title = $row["title"];
$poem_id = $row["poem_id"];
$poet_id = $row["poet_id"];
$first_name = $row["first_name"];
$last_name = $row["last_name"];

// just check if this is a new poet or not...
if ($poet_id != $last_poet_id) {
echo "<tr>\n<td>$first_name $last_name</td>\n</tr>";
$last_poet_id = $poet_id;
}

echo "
<tr>
<td><a href=show_poem.php3?poem=$poem_id>$title</td>
</tr>";

}

echo "</table></center>";

mysql_free_result($sql_result);
mysql_close($connection);

</body>

clarkm

Thank you, it does exactly what I wanted, and thanks for your time and help.

rrijnders

Glad to help.
-- Rich