Loop doesnt work good...

[Jotade29]

V: 2.0.19
When I run a query and use a while loop to display the results, it doesn't give me the correct results. It shows me everything it's supposed to, yes, but the loops are incorrect, and I don't know how to do it.

I share the code snippet

Code Select Expand

$test = $smcFunc['db_query']('', '
    SELECT
        q.title, q.description, qq.answer_text, a.answer_text AS at
    FROM quiz as q
    LEFT JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
    LEFT JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
    WHERE creator_id = '1''
    );
   
    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        $data['quiz-usuarios'][] = array(
        'titulo' => $row['title'],
        'descripcion' => $row['description'],
        'question' => array(
            'pregunta' => $row['answer_text'],
            'answer' => array(
                'respuestas' => $row['at'],
            ),
        ),
        );   
    }
As you can see, I want it to show me all the quizzes of the user with id 1, that is, their name and description, then I want it to show me the questions in that quiz, as well as their answers. What's the problem?

The code works well for me, meaning it prints the data I send it, but not optimally. Let's imagine the first quiz has the following data, and have user_id 1:

Code Select Expand

- Name: Animals
- Description: The best animals
- Questions:
Does the tiger have 4 legs?
Answers:
- Yes
- No
Does the whale have 4 legs?
- Yes
- No
With the shared code, it prints (in xml) it like this:

Code Select Expand

<quizzes-usuario>
        <0>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the tiger have 4 legs?</pregunta>
                <answer>
                 <respuestas>No</respuestas>
                </answer>
            </question>
        </0>
        <1>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the tiger have 4 legs?</pregunta>
                <answer>
                 <respuestas>Yes</respuestas>
                </answer>
            </question>
        </1>
        <2>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the whale have 4 legs?</pregunta>
                <answer>
                 <respuestas>No</respuestas>
                </answer>
            </question>
        </2>
        <3>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the whale have 4 legs?</pregunta>
                <answer>
                 <respuestas>Yes</respuestas>
                </answer>
            </question>
        </3>
When what I expect him to do is the following:

Code Select Expand

<quizzes-usuario>
            <0>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the tiger have 4 legs?</pregunta>
                <answer>
                    <respuestas>No</respuestas>
                    <respuestas>Yes</respuestas>
                </answer>
             <pregunta>Does the whale have 4 legs?</pregunta>
                <answer>
<respuestas>Yes</respuestas>
                    <respuestas>No</respuestas>
</question>
</0>
</quizzes-usuario>
I want it to only show me the results of each quiz with its questions and answers, not the quiz with the questions and only 1 of the answers for each loop.

Thnx!!

[Diego Andrés]

Because $row['at'] is not an array.
What is the value of this column in the database? If it's a comma separated list, you will need to use explode()

Otherwise, you probably need to join another table to get the answers (I don't remember much about that MOD)

[Jotade29]

Because $row['at'] is not an array.
What is the value of this column in the database? If it's a comma separated list, you will need to use explode()

Otherwise, you probably need to join another table to get the answers (I don't remember much about that MOD)

Hii Diego Andrés,

a.answer_text AS at

My sql query work, but for any answer create me other title, descripcition... Only i want that create me one title and description only for quiz, and inside the questions and answers in loop

[Chen Zhen]

Try using INNER JOIN for the answer table, use the quiz ID as the initial key and put the answers in the same 3rd indice.

Perhaps this will work for you:

Code Select Expand

global $smcFunc;
    $data['quiz-usuarios'] = [];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text, a.answer_text AS at
        FROM {db_prefix}quiz as q
        LEFT JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_quiz']])) {
            $data['quiz-usuarios'][$row['id_quiz']] = [
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'question' => [
                    'pregunta' => $row['answer_text'],
                    'answer' => [
                        'respuestas' => [],
                    ],
                ],
            ];
        }
        $data['quiz-usuarios'][$row['id_quiz']]['question']['answer']['respuestas'][] = $row['at'];
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val){
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }


[Jotade29]

Try using INNER JOIN for the answer table, use the quiz ID as the initial key and put the answers in the same 3rd indice.

Perhaps this will work for you:

Code Select Expand

global $smcFunc;
    $data['quiz-usuarios'] = [];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text, a.answer_text AS at
        FROM {db_prefix}quiz as q
        LEFT JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_quiz']])) {
            $data['quiz-usuarios'][$row['id_quiz']] = [
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'question' => [
                    'pregunta' => $row['answer_text'],
                    'answer' => [
                        'respuestas' => [],
                    ],
                ],
            ];
        }
        $data['quiz-usuarios'][$row['id_quiz']]['question']['answer']['respuestas'][] = $row['at'];
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val){
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }


Thanks a lot, @Chen Zhen       The code works almost perfectly. It only shows one question from each quiz, instead of all the ones it has. Could you tell me how to add all the questions from a quiz to the code?

With your code, it show me:

Code Select Expand

<quizzes-usuario>
            <0>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the tiger have 4 legs?</pregunta>
                <answer>
                    <respuestas>No</respuestas>
                    <respuestas>Yes</respuestas>
                </answer>
</question>
</0>
</quizzes-usuario>
I need that show me all quizz question with answer

Code Select Expand

<quizzes-usuario>
            <0>
            <titulo>Animals</titulo>
            <descripcion>The best animals</descripcion>
            <question>
                <pregunta>Does the tiger have 4 legs?</pregunta>
                <answer>
                    <respuestas>No</respuestas>
                    <respuestas>Yes</respuestas>
                </answer>
             <pregunta>Does the whale have 4 legs?</pregunta>
                <answer>
<respuestas>Yes</respuestas>
                    <respuestas>No</respuestas>
<answer>
</question>
</0>
</quizzes-usuario>
Thx a lot of!!!

[Chen Zhen]

Try this query:

Code Select Expand

global $smcFunc;
    $data['quiz-usuarios'] = [];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text, a.answer_text AS at,
            qq.id_question, qq.question_text, a.is_correct
        FROM {db_prefix}quiz as q
        INNER JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}
        ORDER BY qq.id_question, q.id_quiz',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_question']])) {
            $data['quiz-usuarios'][$row['id_question']] = [
                'id_quiz' => $row['id_quiz'],
                'id_question' => $row['id_question'],
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'question' => $row['question_text'],
                'additional_dialogue' => $row['answer_text'],
                'correct_answer' => '',
                'answers' => [],
            ];           
        }

        $data['quiz-usuarios'][$row['id_question']]['answers'][] = $row['at'];
        $data['quiz-usuarios'][$row['id_question']]['correct_answer'] = !empty($row['is_correct']) ? $row['at'] : '';
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val){
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }

You should be able to build your XML output from that.

[Jotade29]

Try this query:

Code Select Expand

global $smcFunc;
    $data['quiz-usuarios'] = [];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text, a.answer_text AS at,
            qq.id_question, qq.question_text, a.is_correct
        FROM {db_prefix}quiz as q
        INNER JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}
        ORDER BY qq.id_question, q.id_quiz',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_question']])) {
            $data['quiz-usuarios'][$row['id_question']] = [
                'id_quiz' => $row['id_quiz'],
                'id_question' => $row['id_question'],
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'question' => $row['question_text'],
                'additional_dialogue' => $row['answer_text'],
                'correct_answer' => '',
                'answers' => [],
            ];           
        }

        $data['quiz-usuarios'][$row['id_question']]['answers'][] = $row['at'];
        $data['quiz-usuarios'][$row['id_question']]['correct_answer'] = !empty($row['is_correct']) ? $row['at'] : '';
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val){
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }

You should be able to build your XML output from that.

Thnxx uuuu, Cheeen 

[Chen Zhen]

No problem, I'm glad that works for you!

You can also change it up if you prefer the array to be something closer to your original format:

Code Select Expand

    global $smcFunc;
    list($data['quiz-usuarios'], $cuestionario_datos) = [[], []];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text AS ta, a.answer_text AS at,
            qq.id_question, qq.question_text, a.is_correct
        FROM {db_prefix}quiz as q
        INNER JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}
        ORDER BY qq.id_question, q.id_quiz',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_question']])) {
            $data['quiz-usuarios'][$row['id_question']] = [
                'id_prueba' => $row['id_quiz'],
                'id_pregunta' => $row['id_question'],
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'pregunta' => [
                    'pregunta' => $row['question_text'],
                    'texto_adicional' => $row['ta'],
                    'respuesta_correcta' => '',
                    'respuesta' => [
                        'respuestas' => [],
                    ],
                ],
            ];
        }

        $data['quiz-usuarios'][$row['id_question']]['pregunta']['respuesta']['respuestas'][] = $row['at'];
        $data['quiz-usuarios'][$row['id_question']]['pregunta']['respuesta_correcta'] = !empty($row['is_correct']) ? $row['at'] : '';
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val) {
        if (empty($cuestionario_datos[$val['id_prueba']])) {
            $cuestionario_datos[$val['id_prueba']] = [];
        }
        $cuestionario_datos[$val['id_prueba']][] = $val;
    }

    foreach ($cuestionario_datos as $key => $val) {
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }


[Jotade29]

No problem, I'm glad that works for you!

You can also change it up if you prefer the array to be something closer to your original format:

Code Select Expand

    global $smcFunc;
    list($data['quiz-usuarios'], $cuestionario_datos) = [[], []];

    $test = $smcFunc['db_query']('', '
        SELECT
            q.id_quiz, q.title, q.description, qq.answer_text AS ta, a.answer_text AS at,
            qq.id_question, qq.question_text, a.is_correct
        FROM {db_prefix}quiz as q
        INNER JOIN {db_prefix}quiz_question AS qq ON (q.id_quiz = qq.id_quiz)
        INNER JOIN {db_prefix}quiz_answer AS a ON (qq.id_question = a.id_question)
        WHERE q.creator_id = {int:id}
        ORDER BY qq.id_question, q.id_quiz',
        ['id' => 1]
    );

    while ($row = $smcFunc['db_fetch_assoc']($test))
    {
        if (empty($data['quiz-usuarios'][$row['id_question']])) {
            $data['quiz-usuarios'][$row['id_question']] = [
                'id_prueba' => $row['id_quiz'],
                'id_pregunta' => $row['id_question'],
                'titulo' => $row['title'],
                'descripcion' => $row['description'],
                'pregunta' => [
                    'pregunta' => $row['question_text'],
                    'texto_adicional' => $row['ta'],
                    'respuesta_correcta' => '',
                    'respuesta' => [
                        'respuestas' => [],
                    ],
                ],
            ];
        }

        $data['quiz-usuarios'][$row['id_question']]['pregunta']['respuesta']['respuestas'][] = $row['at'];
        $data['quiz-usuarios'][$row['id_question']]['pregunta']['respuesta_correcta'] = !empty($row['is_correct']) ? $row['at'] : '';
    }

    $smcFunc['db_free_result']($test);

    foreach ($data['quiz-usuarios'] as $key => $val) {
        if (empty($cuestionario_datos[$val['id_prueba']])) {
            $cuestionario_datos[$val['id_prueba']] = [];
        }
        $cuestionario_datos[$val['id_prueba']][] = $val;
    }

    foreach ($cuestionario_datos as $key => $val) {
        echo $key . ' => ' . json_encode($val) . '<br><br>';
    }


Yeaahhh, thnxxx uuu