First off - I've read as many PHP/MySQL how-to's as anyone could lay claim to. They all get real close to answering my problem, except for one major hitch...
I have a web site, in which I want to display (IN AN HTML TABLE) my club members and their respective cars. In the back end, I have a MySQL database (club) with two tables: (clubmembers) and (clubcars). This is a one-to-many relationship, where one (clubmembers.memberid) may have many (clubcars.car). The tables are linked via (clubmembers.memberid) = (clubcars.memberid).
The HTML table on the web page is simple:
[Membername ][car ]
[Joe Blow ][1965 Mustang ]
[John doe ][1969 Mustang ]
etc. etc.
The problem lies in the one-to-many relationship. I don't want the member names repeated for every 'additional' car that they own like this:
[Joe Blow ][1965 Mustang]
[Joe Blow ][1955 Chevy ]
I know how to surpress the name cell while still filling in the car cell:
[Joe Blow ][1965 Mustang ]
[ ][1955 Chevy ]
But what I want, is to get the WHOLE list of cars in one HTML cell:
[CODE]
| | |
| | 1965 Mustang |
| Joe Blow | 1955 Chevy |
| | |
| | |
[/CODE]
This is the php that I've written to get me thus far:
SELECT * FROM clubmembers LEFT JOIN clubcars ON clubmembers.memberid = clubcars.memberid
ORDER BY clubmembers.memberid;
$result = mysql_query($query);
$num_results = mysql_num_rows($result)
or die("mysql_query() failed: " . htmlentities(mysql_error()));
$lastref= 0;
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
if ($row['memberid'] != $lastref)
{
$lastref = $row['memberid'];
echo "<tr><td width=\"20%\" class=\"gto8\">".($row['officerposition'])."</td><td width=\"33%\" class=\"gto8\">".($row['firstname'])." ".($row['lastname'])."</td>";
}
else
{
echo "<tr><td width=\"20%\" class=\"gto8\"> </td><td width=\"33%\" class=\"gto8\"> </td>";
}
echo "<td width=\"38%\" class=\"gto8\">".($row['caryear'])." ".($row['carmodel'])."</td><td width=\"9%\" class=\"gto8\">\n";
if($row['providedpics'] > 0)
{
echo "<a href=\"http://www.mywebsite.com/photopost/showgallery.php?cat=500&ppuser=".($row['userid'])."\"><img border=\"0\" src=\"images/symbols/camera.gif\"></a>";
}
echo "</td></tr>\n";
}
echo "</tr>\n";
echo "</table>\n";
echo "</td>\n";
echo "</tr>\n";
echo "</table><br>\n";
As I stated before, this will output an HTML table on my web page, and list the members & their respective cars, while surpressing duplicate member names in cases where a member has multiple cars. It's not ideal; I'm trying to put the multiple cars per member inside ONE HTML TABLE CELL. I need help. Do I need two separate queries/result sets to work with? I'm trying to avoid launching a query for each record in the (clubmembers) table as that would have significant performance issues.
Thanks all,
