Author |
Message |
Darren
Team Member
![](images/rank_6.gif)
![](images/avatars/gallery/computers/apple.gif)
Joined: 05 Feb 2002
Posts: 549
Location: London
|
Posted:
Fri Jan 02, 2004 10:53 am (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
I'm creating a glossary of terms and I want to be able to display a list of related terms with a term.
My main table is like this (MySQL):
table.glossary
- glossary_id
- glossary_term
- glossary_definition
I then have a table like this:
table.glossary_related
- id
- glossary_id
- related_id
This works to a degree, in that I do a query for all rows that have the glossary_id of the current term and then use the related_id to look up which terms are related.
$gid = current term ID
Code: | SELECT g.glossary_id, g.glossary_term
FROM glossary as g, glossary_related as gr
WHERE gr.glossary_id = $gid
AND g.glossary_id = gr.related_id |
However I need the relationship to work both ways, with my current system that means adding two records, like so:
glossary_id | related_id
---------------------------
1 | 2
2 | 1
This seems somewhat redundant to me, and what I would ideally like to do is to only define the relationship once. Then I guess my query would need to search both columns for any record containing the current term ID and gather the necessary list of related terms no matter which column they are in. Make sense?
I would appreciate any help in writing this query as it is beyond what I understand of SQL...
Thanks in advance |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Darren Find all posts by Darren](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Daniel
Team Member
![](images/rank_6.gif)
![](http://www.4webhelp.net/forums/images/daniel_avatar.php)
Joined: 06 Jan 2002
Posts: 2564
|
Posted:
Fri Jan 02, 2004 12:02 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
This probably isn't ideal but what about doing two queries, one the same as above, and another like this:
Code: | SELECT g.glossary_id, g.glossary_term
FROM glossary as g, glossary_related as gr
WHERE gr.related_id = $gid
AND g.glossary_id = gr.glossary_id |
If this is wrong, crazy or whatever, please let me know. This is a rushed answer, off the top of my head ![Wink](/forums/images/smilies/icon_wink.gif) |
________________________________
![Image Image](http://www.4webhelp.net/images/daniel_sig.gif) |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Daniel Find all posts by Daniel](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) |
![](images/spacer.gif) |
Darren
Team Member
![](images/rank_6.gif)
![](images/avatars/gallery/computers/apple.gif)
Joined: 05 Feb 2002
Posts: 549
Location: London
|
Posted:
Fri Jan 02, 2004 12:47 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
Yes thats probably the answer.
I was looking on mysql.com and if I'm not mistaken I could have joined the results of the 2 SELECTs with UNION had my host been running MySQL 4. their alternative for lower versions was to dump the results of the first query into a temp table, but i didn't have much luck with that either.
if I run the 2 queries seperately, what would be the best way to join them so I can loop through them altogether? |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Darren Find all posts by Darren](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Daniel
Team Member
![](images/rank_6.gif)
![](http://www.4webhelp.net/forums/images/daniel_avatar.php)
Joined: 06 Jan 2002
Posts: 2564
|
Posted:
Fri Jan 02, 2004 12:50 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
Yeah, MySQL4 allows lots of things you can't do in MySQL3, especially in the table join area.
This might work, where $var1 is the result of query1, and $var2 is the result of query2:
Code: | $var1 = $var1 . $var2; |
|
________________________________
![Image Image](http://www.4webhelp.net/images/daniel_sig.gif) |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Daniel Find all posts by Daniel](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) |
![](images/spacer.gif) |
adam
Forum Moderator & Developer
![](images/rank_6.gif)
Joined: 26 Jul 2002
Posts: 704
Location: UK
|
Posted:
Fri Jan 02, 2004 1:15 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
would what I said in this old thread perhaps help here? |
________________________________ It's turtles all the way down... |
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by adam Find all posts by adam](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Darren
Team Member
![](images/rank_6.gif)
![](images/avatars/gallery/computers/apple.gif)
Joined: 05 Feb 2002
Posts: 549
Location: London
|
Posted:
Fri Jan 02, 2004 1:18 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
that didn't seem to work, ended up using array_merge()
This is the code I have now which is so far producing the desired results:
Code: | $query2a = "SELECT g.glossary_id, g.glossary_term
FROM glossary g, glossary_related gr
WHERE gr.glossary_id = $gid
AND g.glossary_id = gr.related_id";
$result2a = mysql_query($query2a, $link_id);
$no_of_related_a = mysql_num_rows($result2a);
$query2b = "SELECT g.glossary_id, g.glossary_term
FROM glossary g, glossary_related gr
WHERE gr.related_id = $gid
AND g.glossary_id = gr.glossary_id";
$result2b = mysql_query($query2b, $link_id);
$no_of_related_b = mysql_num_rows($result2b);
$no_of_related = $no_of_related_a + $no_of_related_b;
if($no_of_related > 0)
{
$related_array_a = db_result_to_array($result2a);
$related_array_b = db_result_to_array($result2b);
$related_array = array_merge($related_array_a,$related_array_b);
echo "<div class='ruledotted'></div>";
echo "<p class='tiny'><strong>Related Terms</strong></p>";
if(is_array($related_array))
{
foreach ($related_array as $row)
{
$related_id = $row["glossary_id"];
$related_term = $row["glossary_term"];
echo "<p class='tiny'><a href='glossary.php?mode=definition&gid=".$related_id."' target='mainFrame'>".$related_term."</a></p>\n";
}
}
} |
|
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Darren Find all posts by Darren](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
Darren
Team Member
![](images/rank_6.gif)
![](images/avatars/gallery/computers/apple.gif)
Joined: 05 Feb 2002
Posts: 549
Location: London
|
Posted:
Fri Jan 02, 2004 1:21 pm (20 years, 5 months ago) |
![Reply with Quote Reply with Quote](templates/4WebHelp2/images/lang_english/icon_quote.gif) ![Back to Top Back to Top](templates/4WebHelp2/images/icon_up.gif) |
adam wrote: | would what I said in this old thread perhaps help here? |
if I understood what it was doing it might ![Embarassed](/forums/images/smilies/icon_redface.gif) |
|
|
![Offline](templates/4WebHelp2/images/offline.gif) ![View User's Profile View User's Profile](templates/4WebHelp2/images/lang_english/icon_profile.gif) ![Find all posts by Darren Find all posts by Darren](templates/4WebHelp2/images/lang_english/icon_search.gif) ![Send Personal Message Send Personal Message](templates/4WebHelp2/images/lang_english/icon_pm.gif) ![Visit Poster's Website Visit Poster's Website](templates/4WebHelp2/images/lang_english/icon_www.gif) |
![](images/spacer.gif) |
|
Page generation time: 0.128074 seconds :: 18 queries executed :: All Times are GMT
Powered by
phpBB 2.0
© 2001, 2002 phpBB Group :: Based on an FI Theme