Personally I like having all my trivia questions, answers and correct answer on one level →
create table trivia_questions
(
id int auto_increment
primary key,
user_id int default 1 not null,
hidden varchar(15) default 'no' not null,
question text not null,
answer1 char(100) not null,
answer2 char(100) not null,
answer3 char(100) not null,
answer4 char(100) not null,
correct int(1) not null,
category varchar(60) not null,
play_date datetime default CURRENT_TIMESTAMP null,
day_of_week int(3) default 0 not null,
day_of_year int(3) null
)
collate = latin1_german2_ci;
It’s eaiser to save the questions that way. but the trade off (for me) is that I have had these trivia questions/answers a long time, so in order to read them using JavaScript I had to do the following:
<?php
require_once 'assets/config/config.php';
require_once "vendor/autoload.php";
use Miniature\Trivia;
/*
* Get Category from the FETCH statement from javascript
*/
$category = htmlspecialchars($_GET['category']);
if (isset($category)) { // Get rid of $api_key if not using:
$data = Trivia::fetch_data($category); // Fetch the data from the Database Table:
$mData = []; // Temporary Array Placeholder:
$answers = []; // Answer Columns from Table Array:
$finished = []; // Finished Results:
$index = 0; // Index for answers array:
$indexArray = 0; // Index for database table array:
/*
* Put database table in proper array format in order that
* JSON will work properly.
*/
foreach ($data as $qdata) {
foreach ($qdata as $key => $value) {
switch ($key) {
case 'answer1':
$answers['answers'][$index] = $value;
break;
case 'answer2':
$answers['answers'][$index + 1] = $value;
break;
case 'answer3':
$answers['answers'][$index + 2] = $value;
break;
case 'answer4':
$answers['answers'][$index + 3] = $value;
break;
}
} // foreach inner
/*
* No Longer needed, but it wouldn't hurt if not unset
*/
unset($qdata['answer1'], $qdata['answer2'], $qdata['answer3'], $qdata['answer4']);
$finished = array_merge($qdata, $answers);
$mData[$indexArray] = $finished;
$indexArray++;
}
output($mData); // Send properly formatted array back to javascript:
}
/*
* After converting data array to JSON send back to javascript using
* this function.
*/
function output($output)
{
http_response_code(200);
try {
echo json_encode($output, JSON_THROW_ON_ERROR);
} catch (JsonException) {
}
}
and the actual setting up of the trivia Questions and Answers in Javascript:
/* Populate Question, Create Answer Buttons */
const createQuiz = (gameData) => {
startTimer(dSec);
question.textContent = gameData.question;
/*
* Create Buttons then insert answers into buttons that were
* create.
*/
gameData.answers.forEach((value, index) => {
/*
* Don't Show Answers that have a Blank Field
*/
let gameButton = buttonContainer.appendChild(d.createElement('button'));
gameButton.id = 'answer' + (index + 1);
gameButton.className = 'answerButton';
gameButton.setAttribute('data-correct', (index + 1));
gameButton.addEventListener('click', clickHandler, false);
if (value !== "") {
gameButton.appendChild(d.createTextNode(value));
} else {
gameButton.appendChild(d.createTextNode(" "));
gameButton.style.pointerEvents = "none";
}
});
};
/* Success function utilizing FETCH */
const quizUISuccess = (parsedData) => {
mainGame.style.display = 'grid';
console.log(parsedData);
gameData = parsedData;
//gameData = parsedData.sort(() => Math.random() - .5); // randomize questions:
totalQuestions = parseInt(gameData.length);
createQuiz(gameData[gameIndex]);
};
/* If Database Table fails to load then answer a few hard coded Q&A */
const quizUIError = (error) => {
console.log("Database Table did not load", error);
};
/* create FETCH request */
const createRequest = (url, succeed, fail) => {
fetch(url)
.then((response) => handleErrors(response))
.then((data) => succeed(data))
.catch((error) => fail(error));
};
/*
* Start Game by Category
*/
const selectCat = function (category) {
const requestUrl = `${quizUrl}category=${category}`;
createRequest(requestUrl, quizUISuccess, quizUIError);
};
Though if I had to do all over again I would setup my database table a little differently, so I wouldn’t have to mess around with arrays to much. Though it’s good practice to.
My point is I like keeping the questions, answers and correct answers in one easy to flow table. I don’t read the correct answers in as I have an id for the correct answers.
Just something to think about? HTH