Page 1 of 1

Simple idea, difficult implementation for rookie...

Posted: Thu Jul 16, 2009 1:43 pm
by asevie
Working on my first project in PHP/MySQL. I'm pretty comfortable with searches, updates etc but now I've moved onto a slightly more complicated results listing and I'm having difficulty finding examples that would help.

In a nutshell, I have a very simple database structure; cardset, question and category. A cardset is made up of questions, questions are assigned a category. I can find a user's cardsets and I can edit the information contained about the cardset, ie name, description etc. Now I'd like to find all of the categories that a cardset is made up of. To do that, I need to search the question table with the cardset id, but I would like to return a list of unique occurences of a category id within the questions contained in a cardset. To do that, I need something like this;

SELECT DISTINCT `id_category` FROM `Questions` WHERE `id_cardset` = 8

Now the question, how do I list the results with the category name from each unique occurence of a category id within the question table? I've got to concurrently search the category table to grab the name and list it, no idea how. Thanks!

Re: Simple idea, difficult implementation for rookie...

Posted: Thu Jul 16, 2009 2:07 pm
by Jammerious
Hm...

Code: Select all

SELECT DISTINCT category_name FROM questions, categories WHERE questions.cardset_id = 8 AND questions.category_id = categories.category_id;
?

Re: Simple idea, difficult implementation for rookie...

Posted: Thu Jul 16, 2009 2:28 pm
by asevie
Jammerious wrote:Hm...

Code: Select all

SELECT DISTINCT category_name FROM questions, categories WHERE questions.cardset_id = 8 AND questions.category_id = categories.category_id;
?

It originally didn't seem to work but after a few small changes, here's what worked. Thanks!

Code: Select all

 
<?php
 
$dbc = connect stuff...
 
 
$result = mysql_query("SELECT DISTINCT id_category, name FROM Questions, Categories WHERE Questions.id_cardset = 8 AND Questions.id_category = Categories.id", $dbc);
        
 
 echo '<p>Categories found in this set:</p>';
 echo '<table>';    
 
  while ($row = mysql_fetch_array($result)) {
 
   echo '<tr><td>Category Name - </td>';
 
   echo '<td><a href="cqs.php?id=' . $row['id_category'] . '">' . $row['name'] . '</a></td></tr>';
            
    }
 
  echo '</table>';
mysql_close($dbc);
?>