4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 help needed with a MySQL query
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
Darren
Team Member



Joined: 05 Feb 2002
Posts: 549
Location: London

PostPosted: Fri Jan 02, 2004 10:53 am (13 years, 11 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by DarrenSend Personal MessageVisit Poster's Website
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Fri Jan 02, 2004 12:02 pm (13 years, 11 months ago) Reply with QuoteBack to Top

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

________________________________
Image
OfflineView User's ProfileFind all posts by DanielSend Personal Message
Darren
Team Member



Joined: 05 Feb 2002
Posts: 549
Location: London

PostPosted: Fri Jan 02, 2004 12:47 pm (13 years, 11 months ago) Reply with QuoteBack to Top

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?
OfflineView User's ProfileFind all posts by DarrenSend Personal MessageVisit Poster's Website
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Fri Jan 02, 2004 12:50 pm (13 years, 11 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by DanielSend Personal Message
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Fri Jan 02, 2004 1:15 pm (13 years, 11 months ago) Reply with QuoteBack to Top

would what I said in this old thread perhaps help here?

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
Darren
Team Member



Joined: 05 Feb 2002
Posts: 549
Location: London

PostPosted: Fri Jan 02, 2004 1:18 pm (13 years, 11 months ago) Reply with QuoteBack to Top

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";
                  }   
               }         
            }
OfflineView User's ProfileFind all posts by DarrenSend Personal MessageVisit Poster's Website
Darren
Team Member



Joined: 05 Feb 2002
Posts: 549
Location: London

PostPosted: Fri Jan 02, 2004 1:21 pm (13 years, 11 months ago) Reply with QuoteBack to Top

adam wrote:
would what I said in this old thread perhaps help here?


if I understood what it was doing it might Embarassed
OfflineView User's ProfileFind all posts by DarrenSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.054487 seconds :: 18 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme