help needed with a MySQL query

Posted: Fri Jan 02, 2004 10:53 am (20 years, 11 months ago)

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

Team Member Joined: 06 Jan 2002 Posts: 2564

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

Posted: Fri Jan 02, 2004 12:47 pm (20 years, 11 months ago)

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?

Team Member Joined: 06 Jan 2002 Posts: 2564

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;

Posted: Fri Jan 02, 2004 1:15 pm (20 years, 11 months ago)

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

Posted: Fri Jan 02, 2004 1:18 pm (20 years, 11 months ago)

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";
               }
            }
         }

Posted: Fri Jan 02, 2004 1:21 pm (20 years, 11 months ago)

adam wrote:

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

if I understood what it was doing it might Embarassed