Question Where to process data - DB, php or c#

Hi
I am running a Maria mysql db and passing data to my game using JSON via php and c#

I have a table of quiz questions - in the short term this is going to be under 1000 records
I then have a table that records whether a given user has given the correct answer as i don't want to ask the same question if they have already gotten it right

Where should i put the logic to loop through the questions and check if they have been answered correctly for a user
I believe i have three options
- to create a nested SQL query (is this correct? not sure how to do this but sounds right!)
- to put some logic into the php script
- to do this in the c# code - getting the proposed question then looping through all the correctly answered questions for that user to check there is not a match. If there is a match then get the next question and repeat until i don't get a match

I have been told previously that php is not compiled so imagine i can discount that option but include it for completeness

I appreciate the record count is low and it probably makes little difference but would like to do this the right way. SQL feels like the quickest but it is also my weakest area so if that is the answer it would be great to get pointed in the right direction for a tutorial on how to achieve this

thanks

 

I don't know what you base that opinion on... I don't think there is a harder, more complicated more peril-fraught way you could do it than SQL, assuming you're talking about hosting a SQL database within Unity.

First you have to just get all the binaries working, which is no small task in itself.

Then you have to make the SQL happy with your filesystem across all targets: rights, directories, etc.

Finally you have to keep it all working forever across all future versions of Unity.

Third party software integration is never the easiest approach when native Unity functionality to store and deliver content (especially simple small text content) already exists and works extremely well.

For a quiz game, put the questions in ScriptableObjects, then just serialize POCO objects for your save state data.

If you insist on editing the data outside of Unity, throw your questions in a giant JSON file. If you choose the JSON route, be sure to leverage sites like:

https://jsonlint.com
https://json2csharp.com
https://csharp2json.io

Anything else is going to be harder, and standing up a SQL database in Unity will be by far the hardest way possible.

Are you hosting this SQL database remotely and connecting to it over the net? If so this isn't a Unity question, this is a "how to make a REST endpoint on a web server somewhere that uses SQL."

Otherwise, php is not running under Unity unless you have also integrated such a system, perhaps one from the asset store.

 

thanks for getting back to me - yes the db is hosted remotely as are php files. I pull/push in c# using UnityWebRequest

I appreciate they are both outside unity and only my c# script is within, but this is by far the friendliest most helpful forum and since the question spans internal and external i thought it would be the best place to ask the question

The reason i thought doing this through SQL would be the best place is because it would be mean i am not transferring redundant data - i need the raw data in the DB remotely since I also need this data available outside of my unity app e.g. on website