MySql - Simply Too many queries

[agriz]

Hi,

There are three tables involved here.
A kind of quiz localhost site, i did for me.

Table 1 contains questions.
==========================
question_id (autoincrement)
questions (varchar 250)
category_id

Table 2 contains choices
========================
Choice_id (autoincrement)
Choice_value (varchar)
question_id

Table 3 contains answers
========================
question_id
choice_id

I am displaying 10 questions from every category

select * from questions where cat_id = 3 ORDER BY RAND() LIMIT 10

now i need to write 10 queries to display the choices.

select * from choice where question_id = xx

What is the better solution to reduce the query?

[smantscheff]

You cannot reduce the number of queries.
You need a subset of questions, but for each question a complete set of choices.
You cannot do the following because mysql doesn't allow limits in subsets:

select * from questions q, choices c
where q.question_id = c.question_id
and q.question_id in 
(select question_id from questions x where cat_id=3 order by rand() limit 10);

You could first isolate the subset of questions in a separate table:

drop table if exists tmp_questions;
create temporary table tmp_questions like questions:
insert into tmp_questions select * from questions where cat_id=3 order by rand() limit 10;
select * from tmp_questions q, choices c where q.question_id = c.question_id;

[agriz]

I am just thinking i am wrong with my table structure. Am i?

One more question.

I display questions in the random order.
After submitting the form,

I display question along with answers. But there is a problem in the order.

Mysql orders the result by question_id which is primary key.
So Answers are ordered which is confusing.

In question, it is like this ..

question 4
Question 7
Question 1

In Answers

Question 1
Question 4
Question 7

I write something like this.

$query = "SELECT * FROM questions WHERE question_id IN (4,7,1);"

Still i am getting ordered results.

How to display answers in the same order as questions displayed?

[smantscheff]

Your table structure is fine.
MySQL does not sort records without a SORT BY clause. If you want the result ordered by some criteria, you have to name them explicitly in your query.

[agriz]

Your table structure is fine.
MySQL does not sort records without a SORT BY clause. If you want the result ordered by some criteria, you have to name them explicitly in your query.

No it is sorted.

What should be the output for the query?

SELECT * FROM questions INNER JOIN answers ON question_id = FK_question_id WHERE question_id IN (13,17,16,11,12,14,18,20,19,15)

I am getting ordered results.

[smantscheff]

You mean that the output is ordered by question_ids. This is from a database point of view a random order - the order of the input. So the result is not sorted. To sort it by specific criteria, include an ORDER BY clause in your query.

[agriz]

I didnt give any order by.

Just in where condition i am giving question ids (like in my last post)
The result is coming the ascending order of question id

How to get results in the same order as i am passing the question ids in my last post?

[smantscheff]

How to get results in the same order as i am passing the question ids in my last post?

You don't, at least not in an easy fashion.
Either there is a data property which you can use for sorting (like an alphabetical sequence, a timestamp etc., or you have to code an inline or explicit function to alter the sorting arbitrarily.
For this you could for example use the locate() or the if() function, like in:

select id from mytable order by (if(id=13,1,if(id=17,2,if(id=16,4)));

or

select id from mytable order by locate(concat(' ', id, ' '), ' 13 17 16 11 12 ');

[ditty]

I think you can get the result using this query

SELECT * FROM `user` WHERE uid IN ( 22, 14, 18 ) ORDER BY FIELD( uid, 22, 14, 18 ) LIMIT 0 , 30;

[agriz]

Thanks. I tried this one. It is working for me.

SELECT * FROM `user` WHERE uid IN ( 22, 14, 18 ) ORDER BY FIELD( uid, 22, 14, 18 ) LIMIT 0 , 30;

[smantscheff]

Please mark this thread as solved.