Random Select function and Mysql - attempt to build a chinese vocabulary trainer

[xxtobirichter]

Hello, I am a student studying Ethics in IT and at the moment I am working on a project to build a small chinese vocabulary trainer.

The idea: To build a chinese vocabulary trainer. The idea is that one part of the page containing the image of a chinese character. Then on the bottom 4 other imagefiles are displayed. They represent the answers. The student puts in the answer into a "submit" box. And the scribt evaluates if correct or not.

Hey

My idea was to select the files via a random function with php. So far so good. Now the problematic part. One of the four answers has to contain the right one/has to be equal to the one presented. Now the question has anyone an idea to arrange a scribt to do that? The right answer must also be at different positions. So that when the net question is asked the right answer is not in the same position. (e.g box A,B,C,D). Then a scribt should evaluate the names of the documents and if equal echo one html file if wrong echo another.

Can anyone help me with this?

 

Another problem that came up was the storage and managing of files. I wondered if it is possible to create a mysql database with 3 columns to organize the files. Column A contains the filename, column B contains the path of the image file with the character and column C contains the path to an audio file, column D contains the path to a html page explaining why the choice was wrong. First of all is that possible or do the 4 items have to be stored in seperated databases? I am new to the whole mysql database and just getting started with the server, therefore i am really happy and appreciating any comments.

 

Thank you very much

Tobi

[Guest]

Hello Tobi! I'm going to tackle one problem at a time, beginning with selecting and randomizing the selected answers.

 

There are many ways to do this, to explore them, let's first assume this is (or is part of) your db table containing all the chinese characters.

 

chineseCharacters

  - id

  - answerid

  - img_url

  - [others irrelevant, if any]

Answers

  - id

  - [others irrelevant, if any]

 

The simplest and basest way might be just to randomly select the main character (to be shown above the answers). Select 3 wrong answers, and select 1 right answer.

 

you would first select the main character image

 

SELECT id, answerid, img_url FROM chineseCharacters ORDER BY rand() LIMIT 1

 

then, using that id, you do this:

 

SELECT * FROM answers WHERE id <> $answerid ORDER BY rand() LIMIT 3

SELECT * FROM answers WHERE id = $answerid

 

Now you have

 

- the main character

- three wrong answers

- one right answer

 

In PHP, you can put the 4 answers into an array, use a user defined sort that uses rand() to put them in random order.

 

I can go into further detail, but first tell me if I'm on the right track or if I completely missed the point.

[xxtobirichter]

That's exactly it. What do you refer to by answer ID. Can't i use answerid=id and then in the next step of evaluation have the function evaluate the students choice to the id of the chinese character presented instead of id=answerid, or would that lead to doublelabbeling ?

 

Otherwise very helpfull!!! 

[Guest]

What do you refer to by answer ID. Can't i use answerid=id and then in the next step of evaluation have the function evaluate the students choice to the id of the chinese character presented instead of id=answerid, or would that lead to doublelabbeling?

 

When you preform the first select statement:

 

SELECT id, answerid, img_url FROM chineseCharacters ORDER BY rand() LIMIT 1

 

you are given an answerid. And perhaps that contains the id for the correct answer in the Answers table.

 

Then, assuming you store answerid in a PHP variable called $answerid, you'd use the next two statements:

 

SELECT * FROM answers WHERE id <> $answerid ORDER BY rand() LIMIT 3

SELECT * FROM answers WHERE id = $answerid

 

 

You could do it the opposite way, assuming this table structure:

 

chineseCharacters

  - id

  - img_url

  - [others irrelevant, if any]

Answers

  - id

  - characterid (instead of answerid)

  - [others irrelevant, if any]

 

 

SELECT id, characterid FROM answers ORDER BY rand() LIMIT 4

 

Take the four characterids, and pick one randomly in PHP, store it in $characterid and:

 

SELECT id, img_url FROM chineseCharacters WHERE id=$characterid

 

 

Is that what you meant?

[xxtobirichter]

Yeah makes sense. Another thing, at the moment it appears that you differentiate between "chineseCharacters" and "Answers". Is there a way to keep them in the same mysql db? Such that db

 

-id

-img_url

-answerid

- (etc)

 

SELECT * FROM db  WHERE id <> $answerid ORDER BY rand() LIMIT 3

SELECT * FROM db  WHERE id = $answerid ORDER BY rand() LIMIT 1

 

Regards

Tobi

 

[xxtobirichter]

Hey, I ve finished the database and am now attempting the php part. At the moment I am stuck with the attempt to create the the variable $answerid. I think i cant simply, but i cant put an arry into a mysql querry. How do i solve that?

 

$ansid = array('answerid')

 

and how can i align arreys randomly?

 

Thank you veyr much

 

Tobi