Inserting array to database

Question

I use this code where it groups the question together with the choices.

<?php 

$entries = preg_split('/(?=[a-z\d]+\.(?!\d))/', $str, -1, PREG_SPLIT_NO_EMPTY); 

  $questions = array();
  $currentQuestion = null;
  $id = 0;

  foreach($entries as $entry) {
    if(is_numeric(substr($entry, 0, 1)) === true) {
        $currentQuestion = $entry;
        $questions[$entry] = array();
        $id++;
        // echo "INSERT INTO question (id, q_name) VALUES ($id, $currentQuestion)"."<br>";
        // mysqli_query($con, "INSERT INTO question (id, q_name) VALUES (NULL, '$currentQuestion')");
        continue;

    }

    // mysqli_query($con, "INSERT INTO answers (id, choices, question, correct) VALUES (NULL, 'choices', $id , 0);");
    // echo "INSERT INTO answers (id, choices, question, correct) VALUES (NULL, 'choices', $id , 'stuff')"."<br>";
    $questions[$currentQuestion][] = $entry;
  }

This is the result of the array.

Array
(
    [1. What is love?] => Array
        (
            [0] => a. Haddaway

            [1] => b. Haxxaway

            [2] => c. Hassaway

            [3] => d. Hannaway

        )

    [2. What is love? ] => Array
        (
            [0] => a. Haddaway

            [1] => b. Haxxaway

            [2] => c. Hassaway

            [3] => d. Hannaway

        )

    [3. What is love 1.1? ] => Array
        (
            [0] => a. Haddaway

            [1] => b. Haxxaway

            [2] => c. Hassaway

            [3] => d. Hannaway

        )

    [4. What is love? ] => Array
        (
            [0] => a. Haddaway

            [1] => b. Haxxaway

            [2] => c. Hassaway

            [3] => d. Hannaway 
        )

)

And this is my database structure: question column in table answers is the primary key from questions table, that will determine which question the choice belongs...

questions
+-------+--------------------------+
|  id   | q_name                   | 
+-------+--------------------------+
|   1   |   1.) What is foo?       |
|   2   |   2.) What is foo?       |
+-------+--------------------------+

answers
+-------+-------------+-----------------------+
|   id  | choices     |  question | correct   | 
+-------+-------------+-----------------------+
|   1   |   a. foo1   | 1         |   0       | 
|   2   |   b. foo2   | 1         |   0       |
|   3   |   c. foo3   | 1         |   1       |
|   4   |   a. foo3   | 2         |   0       |
|   5   |   b. foo2   | 2         |   1       |
|   6   |   c. foo1   | 2         |   0       |
+-------+-------------+-----------------------+

I managed to insert the questions to db but I'm having trouble to insert the choices because I'm confused on what I should do to $questions in order to get the choices...

Any suggestion(s) would do!

1) Try not to have a query running within a loop, but build it within the loop and execute it after the loop. 2) Please can you paste the structure of your database tables; answers and questions (SHOW CREATE TABLE answers;)? — ʰᵈˑ
– ʰᵈˑ, Commented Feb 11, 2015 at 14:19
Ohh, so sorry about that.. okay I will edit it, thanks a lot! — yowza
– yowza, Commented Feb 11, 2015 at 14:20
@ʰᵈˑ I updated my question, I hope this is thorough enough. — yowza
– yowza, Commented Feb 11, 2015 at 14:40

ʰᵈˑ · Accepted Answer · 2015-02-13 09:58:39Z

1

I would not use json_encode() for this as you're setting up a normalised data structure.

Here's a breakdown of what you need to do;

Insert the question into questions
Grab the last_insert_id and store it in a variable
Insert the answers into answers
Link the answers to the questions by using last_insert_id

Now onto the code.

Collecting the data

$arrAnswers = array();
$arrQuestions = array();
$id = 0; //Assuming your table is empty

foreach($entries as $entry) { //Loop through the grabbed records
  if(is_numeric(substr($entry, 0, 1)) === true) { //Is it a question?
     $id++;     
     $arrAnswers[$id] = array();
     $arrQuestions[$id] = '(\''. $entry .'\')'; 
  } else { //Ok, it's a possible answer to a question
     $arrAnswers[] = '(\''. $entry .'\', '. $id .', 0)';
  }
}

Inserting the questions

Now we have an array holding all the answers to a question. The array key will be the question id within the database. We can now insert the questions by doing;

$strDbQuery = "INSERT INTO `questions` (`q_name`) VALUES ". implode(", ", $arrQuestions);
// Execute the query.

Inserting the answers

Now you've inserted your questions, you can now insert your answers.

$strDbQuery = "INSERT INTO `answers` (`choices`, `question`, `correct`) VALUES ". implode(", ", $arrAnswers);
// Execute the query.

Because your array (in your question) didn't hold a value to indicate whether or not an answer is correct or not, you'll have to do this manually.

edited Feb 13, 2015 at 9:58

answered Feb 11, 2015 at 16:15

ʰᵈˑ

11.4k3 gold badges31 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

yowza Over a year ago

Yes, I'll do something about to determine the answer is correct. I really appreciate your time for helping me! Kudos! I learn new things!

yowza Over a year ago

Hello again, I have an error on inserting answers to db, it says Array to String Conversion, I think this is happening since it's a multidimensional array? this is what it returns... INSERT INTO answers (choices, question, correct) VALUES Array

ʰᵈˑ Over a year ago

Hi @yowza - Just edited my answer so the answers are not in a multidimensional array :) So should be good.

yowza Over a year ago

Uhmm, hi again :D how can I implode the answers in the loop?

ʰᵈˑ Over a year ago

You want to implode within the loop to do what? Execute the query? I wouldn't suggest you do. We're building an array of answers (with the question id they are for) as a string to build the final query. I'm unsure why you'd want to implode in the loop. Please explain.

|

Emmanuel Okeke · Accepted Answer · 2015-02-11 14:27:35Z

1

To store an array, you'll need to write it as a string to the database. There are two (2) functions that come to mind:

serialize() serialize
json_encode() json_encode

Either works great; serialize() will convert the array to a string which you can then save; to retrieve your array at a later time, pass the string to the unserialize() function.
json_encode() also has an accompanying json_decode() function. To read your data as an array [rather than an object in this scenario], you'll have to do this:
$questions = json_decode($string_from_database, true);

I hope this helps.

answered Feb 11, 2015 at 14:27

Emmanuel Okeke

1,48215 silver badges18 bronze badges

4 Comments

yowza Over a year ago

I'll look up on this! I'd like to extend my appreciation!

yowza Over a year ago

In my opinion, I guess json_encode() is appealing for me, I might need a second opinion!

Emmanuel Okeke Over a year ago

@yowza you're right. It'll usually be smaller in size than serialize()

ʰᵈˑ Over a year ago

@yowza I would not use json_encode() for this. Although, Okeke may have answered before you updated your post to show your table design.

Collectives™ on Stack Overflow

Inserting array to database

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related