Save object with nested arrays into mysql db

Hi,

I got following data coming from my application :slight_smile:

data: "{
"questions":
["question1?","question2?","question3?"],
"answers":
["answer1","answer2","answer3"]
}"

I can’t seem to find out how to :

  1. take the data in to be usable for my loop (can’t count my data)
  $key = $_POST['key'];
  $data = $_POST['data'];
  $total_questions = count ($data[0]);

This results in an error : Warning: count(): Parameter must be an array or an object that implements Countable

  1. save this in my mysql-db using php with following loop :
  for($i = 0; $i < $total_questions; $i++)
  {
    $question = $data[0][$i];
    $answer = $data[1][$i];
    echo "\n".$question. " > ".$answer;

    $query = mysqli_query($connect,"INSERT INTO table (key, question, answer) VALUES ($key, $question, $answer)");
    if(mysqli_errno($connect)){
      mysqli_rollback($connect);
      $result[] =  "unable to save!";
    }
    else {
      mysqli_commit($connect);
      while($row = mysqli_fetch_assoc($query)) {
        $result[] = "data saved!";
      }
    }
  }

Probably making one stupid beginners mistake, but can’t seem to put my finger on it.

owkee… got it working !
Just needed to adapt the way I grab my data :slight_smile:

  $data = $_POST['data'];
  $questions = $data->{'questions'};
  $answers = $data->{'answers'};

Then I did a count on $questions to create my loop
After that I changed this part inside my loop :

    $question = $questions[$i];
    $answer = $answers[$i];
    $result .= "\n".$question." : ".$answer."\n";

This did the trick !

Good figuring out!

You don’t need the {'...'} here. $data->questions and $data->answers will work just fine.

1 Like

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. :rofl: 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

The problem is that is not scalable. Anytime you start consecutively numbering columns that is a red flag that your DB structure needs work. What if you had an exam that had more than four answers? Are you just going to keep numbering columns to Infinity? Put the answers in a separate table with answers keyed to the question ID. Once you do that it won’t matter if your question has two or a thousand answers. You won’t have to touch the database schema at all.

2 Likes

Indeed… this is the reason why I made the choice to have a row/ insert for each question / answer … this way you are not limited to columns… and when you want to retreive the data, you can just use the overal key that is the same for each question in that range…
fyi: for example… I have key “123” assigned for each of those 3 question/answer …
so it results in this :slight_smile:

123 - question1 - answer1
123 - question2 - answer2
123 - question3 - answer3

Thanks for this… applied it to my code :wink:
I assume that the rest is the best / correct way to handle the data?
Asking this question because I was wondering if I could concat the insert queries and run them all at the same time… not that familiar with it, but something with msqli commit?

Your db code could be cleaner and safer. First up, your commit / rollback code won’t do anything since you’re not working in a transaction. You’re also not sanitising your db input, which leaves you vulnerable to database injection attacks. PHP’s mysqli extension supports prepared queries which will protect against these. I’d rewrite your for loop something like as follows:

$query = mysqli_prepare($connect, "INSERT INTO table (key, question, answer) VALUES (?, ?, ?)");
for ($i=0; $i<$total_questions; $i++) {
    $question = $data[0][$i];
    $answer = $data[1][$i];

    mysqli_bind_param($query, 'sss', $key, $question, $answer);

    $succeeded = mysqli_stmnt_execute($query);
    
    if ($succeeded) {
        $result[] = "Saved question: " . $question . " and answer: " . $answer;
    } else {
        $result[] =  "Failed to save question: " . $question . " and answer: " . $answer;
    }
}

You should also look at using PDO instead of mysqli; it would make this code simpler and give you better error handling options.

1 Like

Still not right. You are duplicating the question data.

Db should be like this:

questions table
exam_id | question_id | question

answers table
answer_id | question_id | answer | is_correct

I will certainly look into that PDO thingie ;)…
About the rewritten code you provided :

  • When I said I figured it out to fetch my data I gave this bit of code which was used in my loop :
 $question = $questions[$i];
 $answer = $answers[$i];

Now in your code snippet I see this :

$question = $data[0][$i];
$answer = $data[1][$i];

am I correct assuming this won’t work when fetching my data this way? :

$data = $_POST['data'];
$questions = $data->{'questions'};
$answers = $data->{'answers'};

Do I need to change the way I fetch my data or can I just use my way ?

highly appriciated the help !

could you explain certain parts in your snippet?

  • the VALUES part : (?,?,?) is this actualy how it should be used? or is it just to be replaced?
  • the mysql_bind_param part: the ‘sss’ thingie… what does this do?

(Sorry if I ask stupid questions :smiley: )

My code just changes your db usage; if you have a different way to actually get the data from your structure, then use that.

The VALUES (?, ?, ?) is the placeholder syntax for using mysqli. The bind_params part replaces these for you, whilst also correctly escaping them to prevent db injection attacks.

mysqli_stmnt_bind_param($query, 'sss', $key, $question, $answer);

My original code did include a typo: mysqli_stmt_bind_param is the correct function. The first parameter should be your your prepared query; the second parameter (sss here) describes the types of the parameters to be injected: three strings (hence three ses) in this case. Any further parameters you give to this function are the parameters that will be bound to the query for running, in the order they appear.

I would recommend having a good read around database use in PHP before going too much further with this: PHP Delusions has information on both PDO and mysqli, as does PHP The Right Way.

1 Like

Hi, got it working this way :

    $stmt = mysqli_prepare($connect, "INSERT INTO table (key, question, answer) VALUES (?, ?, ?)");
    for ($i=0; $i<$total_questions; $i++) {
        $question = $questions[$i];
        $answer = $answers[$i];
        mysqli_stmt_bind_param($stmt,'sss', $key, $question, $answer);
        $succeeded = mysqli_stmt_execute($stmt);
        if ($succeeded) {
            $result .= "\n Saved question: " . $question . " with answer: " . $answer;
        } else {
            $result .=  "Failed to save question: " . $question . " with answer: " . $answer;
            $result .= mysqli_error($connect);
        }
    }
    mysqli_stmt_close($stmt);

I asked the question to fast and decided to have some googling to find it out myself :slight_smile: that’s also when I discovered the typo :smiley:
Now my code works as it should be and tnx to your input it’s safer :wink:

Sponsor our Newsletter | Privacy Policy | Terms of Service