current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite
1

This project is based on trivia(Question/Answer Quiz) basically scenario its so simple but i have to improve/ more optimize my query using these tables

I have 5 different table.... (working Query) enter image description here

$query=$this->db->query("SELECT
q.`id`,
q.`topic_id`,
t.`topic`,
q.`question`,
GROUP_CONCAT(a.`answer`) AS `answer`,
GROUP_CONCAT(a.`is_correct`) AS `is_correct`,
GROUP_CONCAT(a.`id`) AS `ans_id`,
q.`answer_type`,
q.`image_url`,
q.`created_date`,
(SELECT ua.`topic_id` FROM usersAnswer ua 
WHERE t.`id`= ua.topic_id AND ua.`user_id` = '".$user_id."' LIMIT 1) 
AS `userdone_topic`,
(SELECT ua.`answer_id` 
FROM usersAnswer ua 
WHERE ua.`question_id` = q.`id` AND ua.`user_id`='".$user_id."' 
AND ua.`answer_id` 
IN
(SELECT a.`id` FROM answers a
WHERE a.`is_correct` != '0' AND a.question_id = q.`id`
)) AS `user_correct_id`
FROM
questions q INNER JOIN topics t ON q.`topic_id`= t.`id`
INNER JOIN answers a ON q.`id`=a.`question_id`  
WHERE 
(SELECT s.`status` 
FROM `status` s 
WHERE 
s.`month_date`= q.created_date AND s.`status`= 2
$valid)
GROUP BY q.`id`
");
return $query->result();
share|improve this question
    
I agree. I'd vote to move it but there isn't an option for codereview in the migrate voting list. –  GordonM Feb 29 '12 at 10:41
    
Thanx for Sharing me Quasdunk... –  Samad Feb 29 '12 at 11:20
    
See dev.mysql.com/doc/refman/5.6/en/using-explain.html –  Janus Troelsen Feb 29 '12 at 12:50
add comment

migrated from stackoverflow.com Feb 29 '12 at 18:28

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

well I used the MS sql for reference but that should be a bit batter. I would also reccomend you to structure your sql cos that way you get a better overview of things. Carefull the below seelect is not grouped you can group it as a subquery again

SELECT q.id, q.topic_id, q.question, a.answer, a.is_correct, a.id as ans_id,
    q.answer_type, q.image_url, q.created_date, uaAll.topic_id as userdone_topic,
    uaCorrect.answer_id as user_correct_id
FROM questions q
    inner join topics t on q.topic_id = t.id
    inner join answers a on q.id = a.question_id
    inner join status s on q.created_date = s.month_date
    left outer join 
    (
        SELECT top 1 ua.topic_id
        FROM UserAnswers ua
        where ua.userid = ''
    ) as uaAll on t.id = uaAll.topic_id
    left outer join (
        select ua.answer_id,ua.question_id
        FROM UserAnswers ua
            inner join answers a on a.id = ua.answer_id
        where ua.userid = ''  
            and a.is_correct <> 0
    ) as uaCorrect on q.id = uaCorrect.question_id AND a.id = uaCorrect.answer_id
WHERE s.status = 2
share|improve this answer
    
Thanks for sharing... –  Samad Feb 29 '12 at 19:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.