MySQL query to obtain responses to questionnaire from two tables

Question

Overwhelmed noob here. I would appreciate any help. I am have been working on this for several days and am cracking.

I have two tables, questions and results:

Table - questions:

ID   |questions            |ans1     |ans2     |ans3     |ans4 
-----|---------------------|---------|---------|---------|---------
1    | Favorite color:     |red      |blue     |green    |purple
2    | Favorite animal:    |cat      |dog      |snake    |bird
3    | Favorite food:      |pizza    |hotdog   |chicken  |salad

Table - results:

ID   |user_id    |ques1  |ques2  |ques3
-----|-----------|-------|-------|-----
122  |abc123     |   1   |   3   |  4
123  |xyz987     |   3   |   3   |  1  
124  |ghj567     |   4   |   1   |  1

Desired output table for user_id abc123:

ques|question         |ques_value|response
1   |Favorite color:  |     1    |  red 
2   |Favorite animal: |     3    |  snake
3   |Favorite food:   |     4    |  salad

Changing the structures of the tables is not an option.

Please help!!

is using PHP to solve this an option, or would you prefere to have such an result by MySQL only? — S.Gartmeier, Sep 23 '17 at 21:08
Yeah, with PHP it would be an easy thing. But if you want to do this SQL only it gets a bit harder. I tried along and created it with the SQL only method: http://sqlfiddle.com/#!9/d4ac2f/1/0 — S.Gartmeier, Sep 23 '17 at 22:23

score 0 · Answer 1 · answered Sep 23 '17 at 21:10

Since you said changing the table structure is not an option I will refrain from talking about relational databases.

$conn = new mysqli($servername, $username, $password, $database);

$sql= "SELECT * FROM TABLE results WHERE user_id='abc123'";

$result = $conn->query($sql);

if($result->num_rows >0){
        $output=array();
        $row = $result->fetch_assoc();

        while($row=$result->fetch_assoc()){
            array_push( $output, $row['ques1'], $row['ques2'], $row['ques3'] );
        }

/*All of this will get the answers from the user. You now have two options, either hardcode
the answer and the word it references, or make another request to the database*/

Option 1

Add this code to your program

switch($output[0]){//Use a switch statement on the first question
    case "ans1":
        $ans1='red';
        break;
    //Continue this for all answers and questions
}

Option 2 (Better if the database may change)

Add this code to your program

$conn = new mysqli($servername, $username, $password, $database);

$sql= "SELECT * FROM TABLE questions";

$result = $conn->query($sql);

if($result->num_rows >0){
        $row = $result->fetch_assoc();

        while($row=$result->fetch_assoc()){

            $outputText=array();

            foreach($output as $value){//Loop through all questions
                switch($value){//Use switch statement to match answer number and answer text
                    case 1:
                        array_push($outputText,$row['ans1']);
                        break;
                    case 2:
                        array_push($outputText,$row['ans2']);
                        break;
                    case 3:
                        array_push($outputText,$row['ans3']);
                        break;
                    case 4:
                        array_push($outputText,$row['ans4']);
                        break;
                }
            }
        }

This should give you everything you need in the $outputText[] array.

Lastly, good luck, I hope this helped.

MySQL query to obtain responses to questionnaire from two tables

1 Answers1

Option 1

Option 2 (Better if the database may change)