linking tables trouble

[Mr_Cat]

Hi folks,

I'm trying to echo some basic enough information from 2 db tables but I've just confused myself by trying to add stuff from a third table into the mix.

I have 4 tables, 'topics' which links everything and contains just ID numbers and creation and last post dates, 'person_a' which contains p_a_ID|p_a_name|author|date, 'person_b' (very similar)

This was fine until I added 'author_ID' and 'lpauthor_ID' fields to the topics table. Those ID's will link to the new 'members' table but of course they both link to the same field there 'member_name'.

Here's the code

$query = "SELECT topics.t_ID, creation_date, lastpost_date, person_a.p_a_name, person_b.p_b_name, author_id, lpauthor_id"; 

$query.= " FROM topics";

$query.= " LEFT JOIN person_a ON topics.persona_ID = person_a.p_a_ID";
$query.= " LEFT JOIN person_b ON topics.personb_ID = person_b.p_b_ID";

// the confusing bit now, how to distinguish these as different names?

$query.= " LEFT JOIN members ON topics.author_ID = members.member_ID";
$query.= " LEFT JOIN members ON topics.lpauthor_ID = members.member_ID";


$result = mysql_query($query) or die(mysql_error()); //of course the handy error message

// if there is any data in the $result array, let's organise it into something
if ($row = mysql_fetch_array($result)) {

echo "<table width='70%' border='0' align='center'>
<tr>
 <td align='center' width='34%'>Topics</td>
 <td align='center' width='33%'>Created</td>
 <td align='center' width='33%'>Last Post</td>
</tr>";

do {
$topic_id = $row['t_ID'];
$persona_name = $row['p_a_name'];
$personb_name = $row['p_b_name'];
$creation_date = $row['creation_date'];
$lastpost_date = $row['lastpost_date'];

// how to do this bit...?

$author = $row['author_name'];
$lpauthor = $row['lpauthor_name'];

echo "<tr>
<td align='center' width='34%'><a href=\"showtopic.php?id=$topic_id\">" . $persona_name . " and " . $personb_name . "</a></td>
<td align='center' width='33%'>" . $creation_date . " by " . $author . "</td>
<td align='center' width='33%'>" . $lastpost_date . " by " . $lpauthor . "</td>
</tr>";

I hope that makes sense...do I need to just make a seperate query for author and last post authors. I did think I could just stash the names as text in the topics table which would solve the problem but goes against the general "try not to store the same info twice" database rule.

I've that annoying feeling it's really simple and probably just needs an AS in the right place or something but I've tried a few things to no avail so far.

Any help much appreciated, cheers.

[eelixduppy]

If I understand your issue, you can create an alias for joined relations, e.g.:

LEFT JOIN members m1 ON topics.author_ID = m1.member_ID 
LEFT JOIN members m2 ON topics.lpauthor_ID = m2.member_ID