[Resolved] query question can you solve it?

k994519

Hi guys

i got this query:

select question_id, question from table questions q where module_id ='$module' order by rand();

which i need to modify to also do the following

select question_id, question from table questions q WHERE module_id ='$module' and the field "required" = "YES" and WHERE the field (number_of_questions) from another table called tests where test_id='$test' then do an order by rand()

how can i change this into proper sql syntax?

here is the table structure if any one needs it.....

table questions

question_id
module_id
required
mark value
question type
question
answer
alt_answer1
alt_answer2
alt_answer3
notes

table tests

test_id
module_id
description
Notes
number_of_questions
start_date
end_date
group_id

any help with this would be great

YappyDog

Is your heirarchy

tests, then modules within tests, then questions within modules?

k994519

yes

that is it exactly.......

YappyDog

select number_of_questions, question_id, question
from tests, questions
where tests.test_id = '$test'
and tests.module_id = '$module'
and tests.module_id = questions.module_id
and questions.required = 'YES'
order by rand();

I think this will select the required questions for a particular test and module in random order. Not sure if/why you need the number of questions, but I put it in anyway.

k994519

thanks for ur help yappy

i will try that,

btw the reason ive got a required field is to minize the number of questions shown for each test... as the db will be holding a v large number of questions for each module.....

is there a way of doing a compare of the current date with the values of start_test end_test ( the data type for this are: datetime)

thanks for all ur help

YappyDog

I think you can just add in something like

and CURDATE() between start_date and end_date

or maybe you'll need to do

and CURDATE() >= start_date
and CURDATE() <= end_date

k994519

is curdate a php or an mysql function?

thanks for ur help yappy ur'e a star !!!

YappyDog

curdate() is a mySql function

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

k994519

thanks yappy

i have had a quick look at my fields: start_test and end_test

they are like this yyyy:mm:dd hh:mm:ss which check would be the best to use when comparing the curent time and date with these variables....

thanks

k994519

thanks yappy

ive found out that current_timestamp is perfect for what i need to do

thanks a million for all ur help today.....

its been great