I've tried everything. Need to make Question/Answer game for School

Unity Engine
,
1

I am currently making a Question / Answer game in unity. I want to put questions in a Mysql database and I want them to show up in my text field.

You should be able to put like 100 questions in a database and get a random question in your textfield. I hope you have an idea of what I mean.

I already followed a tutorial that allows me to add questions via the inspector like this:

But I don’t want to add them via the inspector, I want to add them via a database. I already connected a database but I have no idea how to code this.

public class QuizManager : MonoBehaviour
{
    public List<QuestionAndAnswers> QnA;
    public GameObject[] options;
    public int currentQuestion;

    public Text QuestionTxt;

    private void Start()
    {
        generateQuestion();
    }


    public void correct()
    {
        //when you are right
       
        QnA.RemoveAt(currentQuestion);
        StartCoroutine(waitForNext());
    }

    public void wrong()
    {
        //when you answer wrong
        QnA.RemoveAt(currentQuestion);
        StartCoroutine(waitForNext());
    }

    IEnumerator waitForNext()
    {
        yield return new WaitForSeconds(1);
        generateQuestion();
    }

    void SetAnswers()
    {
        for (int i = 0; i < options.Length; i++)
        {
            options[i].GetComponent<AnswerScript>().isCorrect = false;
            options[i].transform.GetChild(0).GetComponent<Text>().text = QnA[currentQuestion].Answers[i];
           
            if(QnA[currentQuestion].CorrectAnswer == i+1)
            {
                options[i].GetComponent<AnswerScript>().isCorrect = true;
            }
        }
    }

    void generateQuestion()
    {
        if(QnA.Count > 0)
        {
            currentQuestion = Random.Range(0, QnA.Count);

            QuestionTxt.text = QnA[currentQuestion].Question;
            SetAnswers();
        }
        else
        {
            Debug.Log("Out of Questions");
         
        }


    }
}

[System.Serializable]
public class QuestionAndAnswers
{

    public string Question;
    public string[] Answers;
    public int CorrectAnswer;

}

public class AnswerScript : MonoBehaviour
{
    public PlayerMovement PlayerMovement;
    public bool isCorrect = false;
    public QuizManager quizManager;


    private void Start()
    {

    }

    public void Answer()
    {
        if(isCorrect)
        {
            PlayerMovement.MovePlayer();
            Debug.Log("Correct Answer");
            quizManager.correct();
        }
        else
        {
            Debug.Log("Wrong Answer");
            quizManager.wrong();
        }
    }

}
2

Did you? What kind of database? Is it online? Built in to the app? When you say “connected” do you mean Unity has connected to it? If so, what tools did you use to connect to it? Or, have you just created a database?

With the amount of information we currently have we can’t really do anything besides link you to the top result for “unity database connect tutorial”.

3

Hey thank you so much for the reply.

I’ve used a PHPmyadmin mysql database via xampp on a localhost server.
I will drop some screens + code below

public class BackendController : MonoBehaviour
{
    public string id;

    public string answer_one;
    public string answer_two;
    public string answer_three;

    public string question;

    public void Start()
    {
        StartCoroutine(getData());
    }

    IEnumerator getData()
    {
        UnityWebRequest www = UnityWebRequest.Get("http://localhost/camelrace/GetQuestions.php");
        yield return www.SendWebRequest();

        if (www.isHttpError || www.isNetworkError)
        {
            Debug.Log("Connection Error");
        }
        else
        {
            string s = www.downloadHandler.text;
            id = s.Split('-')[0];
            question = s.Split('-')[1];
            answer_one = s.Split('-')[2];
            answer_two = s.Split('-')[3];
            answer_three = s.Split('-')[4];

            id = id.Replace("id: ", "");
            question = question.Replace("Question: ", "");
            answer_one = answer_one.Replace("answer_one: ", "");
            answer_two = answer_two.Replace("answer_two: ", "");
            answer_three = answer_three.Replace("answer_three: ", "");
            answer_three = answer_three.Replace("
", "");

            Debug.Log("Id = " + id);
            Debug.Log("Question =" + question);
            Debug.Log("Answer_One =" + answer_one);
            Debug.Log("Answer_Two =" + answer_two);
            Debug.Log("Answer_Three =" + answer_three);
        }
    }
}

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "camelrace";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, question, answer_one, answer_two, answer_three  FROM questions";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"] . " - Question: " . $row["question"]. " - answer_one: " . $row["answer_one"]. " - answer_two: " . $row["answer_two"]. " - answer_three: " . $row["answer_three"] . "
";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

7898596--1006123--upload_2022-2-15_16-11-34.png
7898596--1006123--upload_2022-2-15_16-11-34.png1902×853 105 KB

4

I need a way to store the database items into my textfields

5

Please stop spamming new posts for the same question. I have no idea why you have chosen SQL, it is probably the single hardest most fraught way you could approach this problem.

Since this is for school, are you required to use SQL? If so then they should provide a version that works well with Unity. That’s the first problem. Games generally don’t use databases. They can, but there’s almost always a far better solution.

If you’re making a quiz game, keep it simple and use ScriptableObjects, or else just put all the questions in a JSON file and load it. Don’t make your life hell by trying to integrate a third party SQL library. That’s gonna be a disaster unless you really know what you’re doing with third party integrations.

There are also THOUSANDS (millions?) of Youtube videos out there for quiz games. It’s VERY well understood territory, and rarely would you use a database.

Tutorials and example code are great, but keep this in mind to maximize your success and minimize your frustration:

How to do tutorials properly, two (2) simple steps to success:

Tutorials are a GREAT idea. Tutorials should be used this way:

Step 1. Follow the tutorial and do every single step of the tutorial 100% precisely the way it is shown. Even the slightest deviation (even a single character!) generally ends in disaster. That’s how software engineering works. Every step must be taken, every single letter must be spelled, capitalized, punctuated and spaced (or not spaced) properly, literally NOTHING can be omitted or skipped.

Fortunately this is the easiest part to get right: Be a robot. Don’t make any mistakes.
BE PERFECT IN EVERYTHING YOU DO HERE!!

If you get any errors, learn how to read the error code and fix your error. Google is your friend here. Do NOT continue until you fix your error. Your error will probably be somewhere near the parenthesis numbers (line and character position) in the file. It is almost CERTAINLY your typo causing the error, so look again and fix it.

Step 2. Go back and work through every part of the tutorial again, and this time explain it to your doggie. See how I am doing that in my avatar picture? If you have no dog, explain it to your house plant. If you are unable to explain any part of it, STOP. DO NOT PROCEED. Now go learn how that part works. Read the documentation on the functions involved. Go back to the tutorial and try to figure out WHY they did that. This is the part that takes a LOT of time when you are new. It might take days or weeks to work through a single 5-minute tutorial. Stick with it. You will learn.

Step 2 is the part everybody seems to miss. Without Step 2 you are simply a code-typing monkey and outside of the specific tutorial you did, you will be completely lost. If you want to learn, you MUST do Step 2.

Of course, all this presupposes no errors in the tutorial. For certain tutorial makers (like Unity, Brackeys, Imphenzia, Sebastian Lague) this is usually the case. For some other less-well-known content creators, this is less true. Read the comments on the video: did anyone have issues like you did? If there’s an error, you will NEVER be the first guy to find it.

Beyond that, Step 3, 4, 5 and 6 become easy because you already understand!

6

I know, a database is required to pass. So I sadly have no choice. Haven’t found a tutorial for making a question game via a database, so I just made it without the database and i’m trying to add the database now.

I’m not spamming posts. The previous one was wrong and couldn’t figure out how to delete it. I understand all the code i’ve made so that’s not the problem.

7

If that is the case, the most-reliable way to integrate a database into Unity will be with an existing Asset Store plugin.

WARNING: it still will not be straightforward and you can expect to have to resolve other dependencies. Look to the forum support for the given SQL database you choose because nobody here keeps up with such details.

Fortunately you have many choices. I have not used any of them personally.

7898659--1006144--Screen Shot 2022-02-15 at 7.28.04 AM.png
7898659--1006144--Screen Shot 2022-02-15 at 7.28.04 AM.png1292×1336 153 KB

8

I think it’s pretty doable to be fair. It’s not going to be used anyway it’s a small part of the assignement. I can already show data from the database into the inspector of Unity.

The only thing I will have to do left is to show the items from the database into a textfield instead of the inspector.

7898716--1006150--upload_2022-2-15_16-44-48.png

But if you think no one is going to help me I guess thanks for letting me know. I will figure it out on my own I guess, im not going to use an asset.

anyway. I’m not going to give up :wink: So wish me luck

9

Well your php script returns more or less html text. this is great if you want to view it in a browser. It’s not really helpful if you want Unity to handle the data since html is just “hyper text” and parsing the information out of your text makes no sense. Your php file should return your data as a json string so it can be read in properly.

Your database design restricts you to just 3 answers per question so it’s less flexible than your old design. Also the example question in your database has a “correct answer” value of “4” which doesn’t make much sense no matter if you have a 0-based index or a 1-based index ^^.

PHP has the “json_encode” method which allows you to convert an object / associative array construct into json. Something like that

$qList = array();
while($row = $result->fetch_assoc()) {
    $item = array(
        'Question'=>$row["question"],
        'Answers'=>array($row["answer_one"], $row["answer_two"], $row["answer_three"]),
        'CorrectAnswer;' => $row["correct_answer"]
    );
    $qList[] = $item;
}
echo json_encode(array('questions' => $qList));

Currently you don’t even read your correct_answer in your database query. So you have to add it to the request:

$sql = "SELECT id, question, answer_one, answer_two, answer_three, correct_answer  FROM questions";

In order to read / parse the result you can use Unity’s JsonUtility. However you need another class that wraps the actual return object:

[System.Serializable]
public class Questions
{
    public List<QuestionAndAnswers> questions;
}

With all that in place you can do this in Unity

IEnumerator getData()
    {
        UnityWebRequest www = UnityWebRequest.Get("http://localhost/camelrace/GetQuestions.php");
        yield return www.SendWebRequest();
        if (www.isHttpError || www.isNetworkError)
        {
            Debug.Log("Connection Error");
        }
        else
        {
            string s = www.downloadHandler.text;
            Questions data = JsonUtility.FromJson<Questions>(s);
            // read the "data.questions" list and do what you've done with your old list.
            // You probably want to copy "data.questions" into your "QnA" list.
        }
    }

For a proper database design you usually would use a seperate table for the answers and have a 1-to-N relationship between a question and the answers. However I guess this may be a bit too advanced for you :slight_smile:

1 Like
10

Thank you so much!
This is going to help a lot, going to work further on it tommorow.

I’m not used working with this, I’m in my last year and this project is going to be for my exam so I have to make it work. Will let you know how it went :wink:

(Will ofcourse make more tables, I was testing before doing it seriously. Because had no idea how to do this)

Have a great day!

11

hey,

Hey I’ve done everything and got it working so for. The only thing I don’t know how to do is:

// read the "data.questions" list and do what you've done with your old list.
            // You probably want to copy "data.questions" into your "QnA" list.
// read the "data.questions" list and do what you've done with your old list.
            // You probably want to copy "data.questions" into your "QnA" list.

Btw, it doesn’t matter if you can only fill in 3 answers, it will always be 3 answers.
Can you maybe help me?

QuestionAndAnswers.CS

[System.Serializable]
public class QuestionAndAnswers
{

    public string Question;
    public string[] Answers;
    public string answer_one;
    public string answer_two;
    public string answer_three;
    public int correct_answer;

}

Quizmanagers.cs

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using UnityEngine.SceneManagement;

public class QuizManager : MonoBehaviour
{
    public List<QuestionAndAnswers> QnA;

    public GameObject[] options;
    public int currentQuestion;

    public Text QuestionTxt;

    private void Start()
    {
        generateQuestion();
    }


    public void correct()
    {
        //when you are right
    
        QnA.RemoveAt(currentQuestion);
        StartCoroutine(waitForNext());
    }

    public void wrong()
    {
        //when you answer wrong
        QnA.RemoveAt(currentQuestion);
        StartCoroutine(waitForNext());
    }

    IEnumerator waitForNext()
    {
        yield return new WaitForSeconds(1);
        generateQuestion();
    }

    void SetAnswers()
    {
        for (int i = 0; i < options.Length; i++)
        {
            options[i].GetComponent<AnswerScript>().isCorrect = false;
            options[i].transform.GetChild(0).GetComponent<Text>().text = QnA[currentQuestion].Answers[i];
        
            if(QnA[currentQuestion].correct_answer == i+1)
            {
                options[i].GetComponent<AnswerScript>().isCorrect = true;
            }
        }
    }

    void generateQuestion()
    {
        if(QnA.Count > 0)
        {
            currentQuestion = Random.Range(0, QnA.Count);

            QuestionTxt.text = QnA[currentQuestion].Question;
            SetAnswers();
        }
        else
        {
            Debug.Log("Out of Questions");
       
        }


    }
}

Backendcontroller.cs

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;

[System.Serializable]
public class BackendController : MonoBehaviour
{

    public void Start()
    {
        StartCoroutine(getData());
    }

    IEnumerator getData()
    {
        UnityWebRequest www = UnityWebRequest.Get("http://localhost/camelrace/GetQuestions.php");
        yield return www.SendWebRequest();

        if (www.isHttpError || www.isNetworkError)
        {
            Debug.Log("Connection Error");
        }
        else
        {
            string s = www.downloadHandler.text;
            Questions data = JsonUtility.FromJson<Questions>(s);
            // read the "data.questions" list and do what you've done with your old list.
        
            // You probably want to copy "data.questions" into your "QnA" list.
        }
    }

    [System.Serializable]
    public class Questions
    {
        public List<QuestionAndAnswers> questions;
    }

}

Getquestions.php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "camelrace";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, question, answer_one, answer_two, answer_three, correct_answer  FROM questions";
$result = $conn->query($sql);

//Convert object / array into JSON
$qList = array();
while($row = $result->fetch_assoc()) {
    $item = array(
        'Question'=>$row["question"],
        'Answers'=>array($row["answer_one"], $row["answer_two"], $row["answer_three"]),
        'CorrectAnswer;' => $row["correct_answer"]
    );
    $qList[] = $item;
}
echo json_encode(array('questions' => $qList));


?>

Answerscript.cs

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;

public class AnswerScript : MonoBehaviour
{
    public PlayerMovement PlayerMovement;
    public bool isCorrect = false;
    public QuizManager quizManager;


    private void Start()
    {

    }

    public void Answer()
    {
        if(isCorrect)
        {
            PlayerMovement.MovePlayer();
            Debug.Log("Correct Answer");
            quizManager.correct();
        }
        else
        {
            Debug.Log("Wrong Answer");
            quizManager.wrong();
        }
    }

}

7901152--1006630--upload_2022-2-16_13-24-12.png
7901152--1006630--upload_2022-2-16_13-24-12.png1187×128 16.7 KB

12

@ This is for homework and an exam? We probably shouldn’t be helping you then, right? Can you share the class syllabus for this assignment, how do you plan to “use this on the exam”. You know this game will only work on your local system, right? This is because you are connecting to a localhost database. Other people won’t be able to run it. Your last comment was “Btw, it doesn’t matter if you can only fill in 3 answers, it will always be 3 answers. Can you maybe help me?”, what do you need help with?

1 Like