MySQL Help Please

[EKsparky]

I've been trying to learn php and mysql so I can make a quiz for my web site

So far its going quit well

My quiz has 10 different categories each category  issue's a random questions from db

But I also need sub categories i.e.  you can chose quiz No10 called example easy or you may choose quiz No9 called say harder  now a sub category No9a would include random question from quiz 10 and 9 this would go on all the way to quiz no1 example ultra hard with sub category No1a including random question from all 10 quiz's

 

Where I am

I made 10 different tables  columns questions and answers and I used

SELECT * from ten  ORDER BY RAND() LIMIT 1 ;

which worked fine for individual category's

I assumed I would then just use

SELECT * from ten seven  ORDER BY RAND() LIMIT 1 ;

or

SELECT * from ten , seven  ORDER BY RAND() LIMIT 1 ;

But this does not work how I want

I then thought maybe I should have just one table and give each quiz and id

 

CREATE TABLE test (
  
  cat char(20) NOT NULL,
  question char(30) NOT NULL,
  answer char(30) NOT NULL,
 
  );
  
  
INSERT INTO test ( cat, question, answer) VALUES
       ('ten','foo1', ' bar1'),
('ten','foo2', ' bar2'),
('ten','foo3 ', ' bar3'),
('seven','foo1',' bar1'),
('seven','foo2',' bar2'),
('seven','foo3',' bar3'),
('one','foo1','bar1'),
('one','foo2','bar2'),
('one','foo3','bar3');

 

Hoping I could use a query like

SELECT * from test  WHERE cat = ten ORDER BY RAND() LIMIT 1 ;

 

I guess I need to use different create table command and query

A I said noobie who is so happy to have what I have working

Thanks for any help

I no I could just made loads more tables adding extra data from each cat orgy but that would not be the correct way to go about it

 

[AndyB]

SELECT * from test  WHERE cat = 'ten' ORDER BY RAND() LIMIT 1 ;

 

Note the single quotes around the field value

[bubblegum.anarchy]

You have questions.. and each question is unique so start with a table of questions something like:

 

CREATE TABLE question (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
    content VARCHAR(255) NOT NULL DEFAULT 'This question is undefined' COMMENT 'unique question',
    PRIMARY KEY (id),
    UNIQUE KEY (content)
) TYPE=MyISAM;

INSERT IGNORE INTO question (content) 
    VALUES ('What is your favourite color?')
    , ('What is your favourite number?')
    , ('What is your favourite movie?')
    , ('What is todays day')
    , ('What is the current temperature');

 

now you also want categories that should also be unique, so create a categories table:

 

CREATE TABLE category (
    id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'category unique identifier',
    content VARCHAR(255) NOT NULL DEFAULT 'This category is undefined' COMMENT 'unique category',
    PRIMARY KEY (id),
    UNIQUE KEY (content)
) TYPE=MyISAM;

INSERT IGNORE INTO category (content)
    VALUES ('Favourite Things')
    , ('About Today');

 

Now you need to create a table to link a question with a category:

 

CREATE TABLE question_in_category (
category_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'category.id foreign key reference',
question_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'question.id foreign key reference',
primary key (category_id, question_id)
) TYPE=MyISAM;

INSERT IGNORE INTO question_in_category (category_id, question_id)
   VALUE ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite color?'))
   , ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite number?'))
   , ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite movie?'))
   , ((SELECT id FROM category WHERE content = 'About Today'), (SELECT id FROM question WHERE content = 'What is todays day?'))
   , ((SELECT id FROM category WHERE content = 'About Today'), (SELECT id FROM question WHERE content = 'What is the current temperature?'));

 

Now to select a random question from a particular category:

 

SELECT question.* FROM question 
INNER JOIN question_in_category ON question.id = question_id
INNER JOIN category ON category_id = category.id AND category.content = 'Favourite Things'
ORDER BY RAND() LIMIT 1;

 

This is a complex table structure.

[EKsparky]

Thanks that worked i did try  ' ' and " " must have done something else wrong 

 

[EKsparky]

Sorry not worked 

Can i add second cat on where clause if so how i tried AND OR but no luck

 

SELECT * from table  WHERE cat = 'ten' need second cat ORDER BY RAND() LIMIT 1 ;

[EKsparky]

LOL done it added OR with cat again  ;D

 

SELECT * from table  WHERE cat = 'ten' OR cat = 'nine' ORDER BY RAND() LIMIT 1 ;

I can see where your going with your answer  anarchy but that far to complecated for me at the moment