Simple idea, difficult implementation for rookie...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
asevie
Forum Newbie
Posts: 3
Joined: Thu Jul 16, 2009 1:27 pm

Simple idea, difficult implementation for rookie...

Post 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!
User avatar
Jammerious
Forum Commoner
Posts: 59
Joined: Sat Jun 27, 2009 11:30 am
Location: Slovenia (EU)

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

Post 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;
?
asevie
Forum Newbie
Posts: 3
Joined: Thu Jul 16, 2009 1:27 pm

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

Post 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);
?>
Last edited by Benjamin on Thu Jul 16, 2009 7:46 pm, edited 1 time in total.
Reason: Added [code=php] tags.
Post Reply