homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

linking tables trouble

5+ Year Member

Msg#: 4409396 posted 10:33 am on Jan 21, 2012 (gmt 0)

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'>
<td align='center' width='34%'>Topics</td>
<td align='center' width='33%'>Created</td>
<td align='center' width='33%'>Last Post</td>

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>

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.



WebmasterWorld Senior Member eelixduppy us a WebmasterWorld Top Contributor of All Time 5+ Year Member

Msg#: 4409396 posted 4:49 pm on Jan 23, 2012 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved