March 14th, 2013, 11:06 PM
Array Loop Problem
I am looking to see if the values of two arrays are equal to each other. I have a mysql table where I have an answers row and want to check and see if what the users inputted are equal to the answer row. Because the table starts with a unique auto incremental id, and then a users id i can not use every value of the array. So I want $answer['game1'] to equal $row['game1'], and loop thru to $answer['game12']. I was thinking it would be simple but cant figure it out. Would appreciate any help
March 14th, 2013, 11:38 PM
this is very bad database design. You're not supposed to store sequential data in a loong row with column names like "game1", "game2", ..., "game5162".
This has several big disadvantages:
- Whenever you wanna add or remove a "game", you have to fumble with the table structure.
- You cannot reference a specific game in another table, you cannot store meta-information -- well, unless you make yet another 12 columns in the other tables and change them each time the "games" change. But that's obviously not a good solution.
- Most queries will be very, very ugly, if not impossible. For example, you cannot even tell (with a sane query) how many "games" you have total.
So repair your database before you do anything else. Store the data in rows, not in columns. The structure might look like this:
As a rule of thumb: Whenever you find yourself numbering dozens of identifiers, there's clearly something wrong. This applies to pretty much all (programming) languages, not just SQL.
game | answer
1 | yes
2 | no
3 | maybe
March 14th, 2013, 11:46 PM
Thanks for the response, I am not sure I understand your point. I eventually want to have users submit their answers, and after the game happens I can put in which team won each game and I can echo out how many each user got right.
The way I have it right now, the user selects a radio button inputting a 1 for the away team, a 2 for the home team. This creates a new row. Can you explain what is wrong with this thinking?
March 15th, 2013, 09:37 AM
Just a thought...
I think I would build such a database with 4 tables.
Table1: games (Game Design)
Table3: submits (Answers from Players)
Table4: users/team w/e defines the players
Table1: Each row would be 1 game. It would define the build/structure of such. This contains the IDs of its questions from Table2. This way, you won't have to delete anything, and just design a new game/question set. So, perhaps something like 2 columns. FIrst holds id number of game, and second holds id number of questions, seperated by commas.
Table2: Just a table with 3 columns. id, question, answer
Table3: This may be structured differently based on your intent for questions. Are you limiting number of questions to like 10, or may it always be random counts, and never know the MOST one game may run? If limited, to say 10, then this would be 12 columns. First column would hold something to relate these responses to the user/team in which submitted such, and one to relate which game these are responses to.
March 15th, 2013, 10:48 AM
I am not sure how many questions will be in each "tournament", how would this change it? Based on your suggestion, i would match the rows from the questions and answer table? And make a new table 2 and 3 for each "tournament"?
Originally Posted by Triple_Nothing
March 15th, 2013, 11:04 AM
Well, Table1 will provide the references to questions per tournamt. You would seperate the id number that are stored i.e. 2,4,13,14,15. Then you would do a simple if/then comparing their answer from Table3, to answer in Table2, column 3.
If a challenge could have 50+ questions, I would build Table3 with 3-4 columns.
3 columns: userid/teamid, questionid, response
4 columns: userid/teamid, quizid, questionnumber, response
'questionnumber' in the 4-col option IS NOT the id of the question. If you have a quiz asking questions 3,7,12,14,15 then questionnumber 3 will equal 12, because of an array index.
Select the 3,7,12,14,15 from the Table1. explode() via the commas. Than question id's will be as displayed:
$array[arrayindex] = id from Table2
$array = 3 Table2, id 3, will be question #1.
$array = 7 Table2, id 7, will be question #2.
$array = 12
$array = 14
$array = 15
March 15th, 2013, 11:26 AM