M
Method 1If the intention is to do so with PHP under the concept you proposed, you need to save in two different arrays both the content of the questions like user_asnswers. For that I recommend you do it with the cycle while (to explain its operation is more because the control structure works just as in any other programming language).So let's assume you have your code like this:<?php
session_start();
include 'connection.php';
include 'URLS.php';
$pregunta_sql = "SELECT questionId FROM questions;";
$pregunta_result = mysqli_query($connection, $pregunta_sql);
$pregunta_row = mysqli_fetch_array($pregunta_result);
$respuesta_sql = "SELECT questionId FROM user_answers WHERE userId = $_SESSION['userId']";
$respuesta_result = mysqli_query($connection, $respuesta_sql);
$respuesta_row = mysqli_fetch_array($respuesta_result);
print_r($pregunta_row);
print_r($respuesta_row);
?>
What we have to do is modify this code by adding the mentioned while and creating the two arrays as follows:<?php
session_start();
include 'connection.php';
include 'URLS.php';
$pregunta_sql = "SELECT questionId FROM questions;";
$pregunta_result = mysqli_query($connection, $pregunta_sql);
//inicializaremos nuestros arrays que ocuparemos y compararemos
$questions = array();
$user_answers = array();
//este ciclo terminará hasta que mysqli_fetch_array esté vacío o haya regresado un false
while ( $pregunta_row = mysqli_fetch_array($pregunta_result) ) {
//y en cada vuelta guardaremos el contenido de cada row de la consulta
$questions[] = $pregunta_row;
}
$respuesta_sql = "SELECT questionId FROM user_answers WHERE userId = $_SESSION['userId']";
$respuesta_result = mysqli_query($connection, $respuesta_sql);
//lo mismo hacemos para las respuestas del usuario
while ( $respuesta_row = mysqli_fetch_array($respuesta_result) ) {
//y en cada vuelta guardamos el contenido de la row de la consulta
$user_answers[] = $respuesta_row;
}
?>
Once we have the arrays $users_answers and $questions full we start with comparisons. We can do it in several ways, but the one I like best and recommend is to go and share them. You couldn't use a array_diff because they are not two arrays that have the same characteristics (in terms of dimension, data types, keys, etc.).So, we go through adding the following to the end of the previous code:<?php
//...
/Recorremos el array $questions debido a que nos interesa saber de cada una de ellas cuál contestó el usuario y para eso nos apoyamos de la estructura de control foreach/
//además creamos un array nuevo que llamaremos respuestas obtenidas donde guardaremos las respuestas que el usuario dio
$respuestas_usuarios = array();
foreach ( $questions as $mQuestion ) {
//recorremos el array de $users_answers en cada item del array $question para saber si el id se encuentra dentro del array $users_answers y que coincida con el elemento actual iterado llamado $mQuestion
foreach ( $user_answers as $mUserAnswer ) {
if ( $mQuestion["id_question"] == $mUserAnswer["id_question"]) {
//si el id_question de la respuesta actual iterada del usuario loggeado y obtenido previamente coincide con una de las preguntas de la tabla preguntas entonces guardamos esa pregunta en el array $respuestas_usuarios
$respuestas_usuarios[] = $mQuestion;
}
}
}
//...
?>
And now yes, once the previous code has been added, we will already know what questions the user has answered and that we have available in the array $respuestas_usuarios. Now what we have to do with this is depending on what we want to show. If for example we want to show which answers the user has answered, we will print (in addition to saving in the $response_user array) with an indicator that question answered or not.<?php
//...
/Recorremos el array $questions debido a que nos interesa saber de cada una de ellas cuál contestó el usuario y para eso nos apoyamos de la estructura de control foreach/
//además creamos un array nuevo que llamaremos respuestas obtenidas donde guardaremos las respuestas que el usuario dio
$respuestas_usuarios = array();
foreach ( $questions as $mQuestion ) {
echo "Pregunta #{$mQuestion['id_question']} ";
//recorremos el array de $users_answers en cada item del array $question para saber si el id se encuentra dentro del array $users_answers y que coincida con el elemento actual iterado llamado $mQuestion
foreach ( $user_answers as $mUserAnswer ) {
if ( $mQuestion["id_question"] == $mUserAnswer["id_question"]) {
//si el id_question de la respuesta actual iterada del usuario loggeado y obtenido previamente coincide con una de las preguntas de la tabla preguntas entonces guardamos esa pregunta en el array $respuestas_usuarios
$respuestas_usuarios[] = $mQuestion;
//Además de guardar la pregunta contestada en el array imprimos el indicador de "Contestada"
echo "Contestada";
}
else {
//Si la pregunta aún no fue respondida entre las respuestas obtenidas del usuario vamos a imprimir un "No contestada"
echo "No contestada";
}
}
echo "<br>";
}
//...
?>
At the end the previous code's reluctance would result, for example, as follows:Pregunta #1 Contestada
Pregunta #2 Contestada
Pregunta #3 No Contestada
Method 2How do I know what questions a user has answered?With SQL we can know. Suppose we have this following structure that you comment on is like this:Table questions
Table user_answers
If we would like to know the questions that the user with id 1 has answered, we would ask a query as the following:SELECT questions., user_answers.id_user FROM user_answers INNER JOIN questions ON (user_answers.id_question = questions.id_question) WHERE user_answers.id_user = 1
With the previous consultation what we are doing is making a relationship with the field id_question, that is, an intersection that where in both tables coincide this field. The rest of the rows that do not coincide in such condition or do not intersecte, will be ignored.The result of the previous consultation would be as follows:
In this case the intersection only becomes effective with the id_queston 1 and 2, since the 3 does not exist in the user_answers table, then the query should not show this last row.How do I know which questions a user has answered and also bring the list of all questions?This can be done very similar to the previous consultation, adding an indicator field that will help us know whether or not you answered that question. The consultation would be:SELECT questions., user_answers.id_user, (IFNULL(user_answers.id_user, 0)) pregunta_respondida FROM user_answers LEFT JOIN questions ON (user_answers.id_question = questions.id_question) WHERE user_answers.id_user = 1
The previous consultation keeps in a field called pregunta_respondida the id of the user found in the table user_answers. In case it was NULO (because the LEFT JOIN turned out to be a null or a non-intersection) you will be placed a number 0. This can then be read with PHP and print an indicator like "Contested" or "not answered" with a conditioner if, for example:<?php
session_start();
include 'connection.php';
include 'URLS.php';
$consulta_final = "SELECT questions.*, user_answers.id_user, (IFNULL(user_answers.id_user, 0)) pregunta_respondida FROM user_answers LEFT JOIN questions ON (user_answers.id_question = questions.id_question) WHERE user_answers.id_user = " . $_SESSION["id_usuario"];
$consulta_final_result = mysqli_query($connection, $consulta_final);
while ( $consulta_row = mysqli_fetch_array($consulta_final_result) ) {
echo "Pregunta #{$consulta_final_result['id_question']} ";
if ( $consulta_row["pregunta_respondida"] > 0 ) {
echo "Pregunta respondida";
}
else {
echo "No respondida";
}
echo "<br>";
}
?>
The result would have to be the same explained as:Pregunta #1 Contestada
Pregunta #2 Contestada
Pregunta #3 No Contestada
