2

I am new here and should make it clear that I am not a computer programmer, just doing this for a hobby. As a result, I understand that my codes are not perfect but just wondering how to add the following array into a database.

At the moment, only one array is being inserted. I am trying to follow a php tutorial on how to do a php quiz but the person did not use implode() or serialize(). How can I insert separate rows? I hope that I have formatted it correctly.

<?php

include_once 'includes/dbh.php';
include_once 'header.php';

$sql = "SELECT * FROM users WHERE user_uid = ?;";

$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
  echo 'SQL statement failed';
} else {
//Bind parameters to the placeholder
  mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);
//Run parameters inside database
  mysqli_stmt_execute($stmt);
  $result = mysqli_stmt_get_result($stmt);
  $row = mysqli_fetch_assoc($result);


  if ($row['admin'] == 0) {
    header("Location: header.php?add=notadmin");
    exit;
  } else {

    if (isset($_POST['submit'])) {


      include_once 'includes/dbh.php';
      $question_number = $_POST['question_number'];
      $question_text = $_POST['question_text'];
      $correct_choice = $_POST['correct_choice'];
      //Choices array
      $choices = array();
      $choices[1] = $_POST['choice1'];
      $choices[2] = $_POST['choice2'];
      $choices[3] = $_POST['choice3'];
      $choices[4] = $_POST['choice4'];



      $correct_choice = $_POST['correct_choice'];

      $sql2 = "INSERT INTO questions (question_number, text) VALUES (?,?);";

      $stmt = mysqli_stmt_init($conn);
      if (!mysqli_stmt_prepare($stmt, $sql2)) {
        echo 'SQL statement failed';
      } else {
        //Bind parameters to the placeholder
        mysqli_stmt_bind_param($stmt, "is", $question_number, $question_text);
        //Run parameters inside database
        mysqli_stmt_execute($stmt);



        foreach($choices as $choice => $value) {

          if ($value != '') {
            if ($correct_choice == $choice) {
              $is_correct = 1;
            } else {
              $is_correct = 0;
            }




            // Choice query

            $sql3 = "INSERT INTO choices (question_number, is_correct, text) VALUES (?,?,?);";



            $stmt = mysqli_stmt_init($conn);
            if (!mysqli_stmt_prepare($stmt, $sql3)) {
              echo 'SQL statement failed';
            } else {
              //Bind parameters to the placeholder
              mysqli_stmt_bind_param($stmt, "iis", $question_number, $is_correct, $value);
              mysqli_stmt_execute($stmt);
            }

            header("Location: quiz.php?add=success");
            exit();
          }
        }

      }
    }
  }
}

Here is the output of print_r($choices):

Array (
    [question_number] => 2 
    [question_text] => how many beat is a crotchet? 
    [choice1] => one 
    [choice2] => two 
    [choice3] => three 
    [choice4] => four 
    [correct_choice] => 1 
    [submit] => submit 
)

I am wondering why all the $value are being shown when I echo $value;; only the first variable is outputting anything.

I am also wondering... what is the equivalent in writing $result = mysqli->query(); in procedural mysqli? Is this the same as writing $result = mysqli_stmt_execute($stmt);?

Improve this question
0

2 Answers 2

Reset to default
2

First of all here is your code with a major cleanup:

include_once 'includes/dbh.php';
include_once 'header.php';

$stmt = $conn->prepare('SELECT * FROM users WHERE user_uid = ?');
$stmt->bind_param('i', $_SESSION['u_uid']);
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

if (!$row['admin'])
{
    header("Location: header.php?add=notadmin");
    exit;
}

if ($_POST && !array_diff_key(array_flip(['question_number', 'question_text', 'correct_choice', 'choice1', 'choice2', 'choice3', 'choice4']), array_filter($_POST, 'strlen')))
{
    for ($i = 1; $i <= 4; ++$i)
        if ($_POST['correct_choice'] == $_POST["choice$i"])
            break;

    if ($i > 4)
        die('There is no correct choice.');

    $stmt = $conn->prepare('INSERT INTO questions (question_number, text, correct_choice) VALUES (?,?,?)');
    $stmt->bind_param('is', $_POST['question_number'], $_POST['question_text'], $i);
    $stmt->execute();

    $stmt = $conn->prepare('INSERT INTO choices (question_number, choice_number, text) VALUES (?,?,?)');
    for ($i = 1; $i <= 4; ++$i)
    {
        $stmt->bind_param('iis', $_POST['question_number'], $i, $_POST["choice$i"]);
        $stmt->execute();
    }

}
header("Location: quiz.php?add=success");

What was changed:

  1. Making sure all required data is sent in $_POST;
  2. Table questions now hold the ID of the correct answer;
  3. Table choices now has choices identified from 1 to 4;
  4. Using MySQLi the way it is meant to be used, with object oriented programming;
  5. Note I created $stmt just once outside the loop. The prepared statement can be reused, you don't need to declare it every time.

This way things are structured way better.

Right, so back to the question. Storing arrays.

Typically you store arrays in a database in what is known as a one-to-many relationship, in this case each question has 4 choices for answers, each choice has it's own record in the choices table, but they are essentially part of a group (part of an array if you must).

You could serialize, but that implies the MySQL server is not in charge of indexing and searching this data anymore, only your application -- that knows how it is serialized -- can do that. Most of the time it doesn't really matter.

I suppose you want to know how to retrieve this 'array' back. You do it like this:

$stmt = $conn->prepare('SELECT choice_number, text FROM choices WHERE question_number = ? ORDER BY choice_number ASC');
$stmt->bind_param('i', $question_number);
$stmt->execute();
$res = $stmt->get_result();
$choices = [];
while ($row = $res->fetch_assoc())
    $choices[$row['choice_number']] = $row['text'];

There, $choices is the array, back to you.

I hope this cleared some doubts, since your question was rather unclear I'm not sure if this is helpful or not.

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

20 Comments

Your answer's better than mine. Feel free to steal anything you want from it. I'm just going to delete it.
I couldn't bother to set up those tables here to make sure this code works at all so if there are any problems let me know, I'm sure there's a comma missing somewhere and whatnot, unless I have fully transcended into a machine and wrote 50 lines of code without making a mistake.
Also worth mentioning, instead of using the ugly $_POST["choice$i"], you could instead use arrays in your HTML and just loop over $_POST['choices']. Some questions could have 2 choices and others could have 50 choices. But hard-coding 4 answers could lead to pain in the future.
Thanks... I was thinking that the problem was also partly due to the fact that I did not loop through it and I guess at the moment, I will replace-> with stmt until I get a chance to get around learning php oop? Also, what is the purpose of array_differ_key and array_filter?
The chance is right now, why delay it? OOP exists to make your life easier, there is absolutely no reason not to use it.
|
0

I would personally prefer json_encode() method to store an array into a database table. Here is an example depicting this scenario:

// An example PHP array
$yourArray = array(
                'Hello', 
                'World', 
                array(
                    1,
                    2,
                    3,
                    "What's up?"
                ) 
            );

// Encode $yourArray array into a JSON string
$yourArrayEncoded = json_encode($yourArray);

// Let's escape single / double quotes before saving it into database
$escapedStr = mysqli_real_escape_string($GLOBALS['connection'], $yourArrayEncoded);

// Insert the string into a table column
$sql = "INSERT INTO foo (bar) VALUES ('$escapedStr')";

// Stripping slashes and converting json string into the original array
print_r( json_decode ( stripslashes ($escapedStr) ) );

I hope this would help you. That's all!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.