Generate non-repetition datas from database

puyunk · May 12, 2013

Hi all,

So basically, I tried to generate random questions from database using mySQL and PHP.

I success in generating those questions, BUT the questions can sometimes appear again.

I have this PHP code of mine..


.......
	$sqlText="SELECT * FROM soal WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ."'";
	$result=mysql_query($sqlText);

	$ctr=0;
	$arr=null;
	while($row = mysql_fetch_array($result))
	{
	   $arr[$ctr]=$row["SoalID"]."#".$row["Question"]."#".$row["Answer1"]."#".$row["Answer2"]."#".$row["Answer3"]."#".$row["Answer4"]."#".$row["CorrectAnswer"]."#".$row["Picture"];
	   $ctr++;
	}

    if ($ctr>0)
	{
		$totalRand=rand(10,50);
		for ($i=0;$i<$totalRand;$i++)
		{
		   $idx1=rand(0,$ctr-1);
		   $idx2=rand(0,$ctr-1);
		   
		   $res=$arr[$idx1];
		   $arr[$idx1]=$arr[$idx2];
		   $arr[$idx2]=$res;
		}
		$idx=rand(0,$ctr-1);
		echo $arr[$idx];
	}
  }

Any idea how to fix this problem?

Appreciate any help..

Thanks before

jazzman1 · May 12, 2013

Try,

$sqlText="SELECT * FROM soal WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ." GROUP BY soal.Question ORDER BY soal.SoalID DESC'";

Edited May 12, 2013 by jazzman1

mac_gyver · May 12, 2013

the best way of doing things randomly without repeats is to remove each choice from the 'pool' of choices as it gets used so that it no longer can be picked.

Edited May 12, 2013 by mac_gyver

puyunk · May 14, 2013

Try,

$sqlText="SELECT * FROM soal WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ." GROUP BY soal.Question ORDER BY soal.SoalID DESC'";

Its not working. The loaded data still repeatable. I'm thinking I should change this code rather than the sql, but I'm kinda confused what should I change here.

if ($ctr>0)
	{
		$totalRand=rand(10,50);
		for ($i=0;$i<$totalRand;$i++)
		{
		   $idx1=rand(0,$ctr-1);
		   $idx2=rand(0,$ctr-1);
		   
		   $res=$arr[$idx1];
		   $arr[$idx1]=$arr[$idx2];
		   $arr[$idx2]=$res;
		}
		$idx=rand(0,$ctr-1);
		echo $arr[$idx];
	}

Edited May 14, 2013 by puyunk

jazzman1 · May 14, 2013

Before starting to build your app you have to better consider your database structure and normalize data.

You will find thousands of articles about that for sure.

I highly recommend you to start watching these 9

About my previous sql query, try to change it with this:

$sqlText = "
SELECT SoalID, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, Picture 
FROM (
     SELECT *, 
     @num := if(@Question = Question, @num + 1, 1) AS q_number,
     @Question := Question AS q_dummy
FROM soal
WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ."') AS X
WHERE X.q_number = 1
";

Edited May 14, 2013 by jazzman1

mac_gyver · May 14, 2013

since you are storing the retrieved rows in an array, just use shuffle() to randomize the rows in that array, then loop over the shuffled/random array to output the questions.

if ($ctr>0){
    shuffle($arr);
    foreach($arr as $question){
        echo $question;
    }
}

@jazzman, i don't know what you are reading in this thread, but the op has a number of questions for each difficulty/topic and wants to retrieve them and randomly output them. the problem is to output them without repeating any one question in a set.

jazzman1 · May 14, 2013

@jazzman, i don't know what you are reading in this thread, but the op has a number of questions for each difficulty/topic and wants to retrieve them and randomly output them. the problem is to output them without repeating any one question in a set.

Yes, that's correct.

What's wrong about my reply?

mac_gyver · May 14, 2013

your first query grouped by the question. unless there are duplicate questions at any difficulty/topic, there's nothing to group.

you second query is just numbering the questions in the query. the OP wants to randomize the display of the questions, so any numbering of the rows in the query would be out of order when displayed and has nothing to do with the stated problem.

jazzman1 · May 14, 2013

your first query grouped by the question. unless there are duplicate questions at any difficulty/topic, there's nothing to group.

you second query is just numbering the questions in the query. the OP wants to randomize the display of the questions, so any numbering of the rows in the query would be out of order when displayed and has nothing to do with the stated problem.

Hm.....you are right, she will get the same result as GROUP BY Question, in case into one topic she has multiple different questions:

So, that should work,

$sqlText = "
SELECT SoalID, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, Picture
FROM (
SELECT *,
@num := if(@Question = Question, @num + 1, 1) AS q_number,
@Question := Question AS q_dummy
FROM soal
WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ."') AS X
WHERE X.q_number = 1
GROUP BY X.Question
";

Edited May 14, 2013 by jazzman1

jazzman1 · May 14, 2013

So, I've made a test with a real data similar to @OP I guess

The table soap:

mysql> SHOW COLUMNS FROM soal;
+---------------+----------------------+------+-----+---------+----------------+
| Field         | Type                 | Null | Key | Default | Extra          |
+---------------+----------------------+------+-----+---------+----------------+
| id            | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| Question      | varchar(45)          | NO   |     | NULL    |                |
| Answer1       | varchar(45)          | NO   |     | NULL    |                |
| Answer2       | varchar(45)          | NO   |     | NULL    |                |
| Answer3       | varchar(45)          | NO   |     | NULL    |                |
| Answer4       | varchar(45)          | NO   |     | NULL    |                |
| CorrectAnswer | varchar(45)          | NO   |     | NULL    |                |
| Picture       | varchar(45)          | YES  |     | NULL    |                |
| Difficulty    | smallint(5) unsigned | NO   |     | NULL    |                |
| Topic         | varchar(45)          | NO   |     | NULL    |                |
+---------------+----------------------+------+-----+---------+----------------+

mysql> select * from soal;
+----+---------------------+----------+----------+----------+----------+---------------+----------+------------+--------------+
| id | Question            | Answer1  | Answer2  | Answer3  | Answer4  | CorrectAnswer | Picture  | Difficulty | Topic        |
+----+---------------------+----------+----------+----------+----------+---------------+----------+------------+--------------+
|  1 | what is the apple?  | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
|  2 | what is the apple?  | Answer 1 | Answer   | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
|  3 | what is the apple?  | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
|  4 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 2 |
|  5 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 2 |
|  6 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
|  7 | what is the orange? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 3 |
|  8 | what is the orange? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 3 |
|  9 | what is the pear?   | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 4 |
| 10 | what is the pear?   | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 4 |
| 11 | what is the pear?   | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
| 12 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
| 13 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |          1 | some topic 1 |
+----+---------------------+----------+----------+----------+----------+---------------+----------+------------+--------------+

All my proposals should be work if her data structure is similar to mine.

mysql> SELECT id, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, Picture
    -> FROM (
    -> SELECT *,
    -> @num := if(@Question = Question, @num + 1, 1) AS q_number,
    -> @Question := Question AS q_dummy
    -> FROM soal
    -> WHERE Difficulty= 1 AND Topic='some topic 1') AS X
    -> WHERE X.q_number = 1
    -> GROUP BY X.Question;
+----+---------------------+----------+----------+----------+----------+---------------+----------+
| id | Question            | Answer1  | Answer2  | Answer3  | Answer4  | CorrectAnswer | Picture  |
+----+---------------------+----------+----------+----------+----------+---------------+----------+
|  1 | what is the apple?  | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
|  6 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
| 11 | what is the pear?   | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
+----+---------------------+----------+----------+----------+----------+---------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT id, Question, Answer1, Answer2, Answer3, Answer4, CorrectAnswer, Picture
    -> FROM soal
    -> WHERE Difficulty= 1 AND Topic='some topic 1'
    -> GROUP BY Question;
+----+---------------------+----------+----------+----------+----------+---------------+----------+
| id | Question            | Answer1  | Answer2  | Answer3  | Answer4  | CorrectAnswer | Picture  |
+----+---------------------+----------+----------+----------+----------+---------------+----------+
|  1 | what is the apple?  | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
|  6 | what is the cherry? | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
| 11 | what is the pear?   | Answer 1 | Answer 2 | Answer 3 | Answer 4 | fruit         | some pic |
+----+---------------------+----------+----------+----------+----------+---------------+----------+
3 rows in set (0.00 sec)

I think, she has to give us a little more information about database and some sample of data too.

mac_gyver · May 15, 2013

@jazzman, i think your language translator is broken.

the problem isn't the query. the OP can query for and retrieve the correct rows. the OP is making a QUIZ where the questions are displayed in a random order, without repeating a question. :facewall:

jazzman1 · May 15, 2013

@jazzman, i think your language translator is broken.

the problem isn't the query. the OP can query for and retrieve the correct rows. the OP is making a QUIZ where the questions are displayed in a random order, without repeating a question.

He-he, in fact that my English language translator engine is completely broken, that's why I am here

I like you.

Edited May 15, 2013 by jazzman1

puyunk · May 23, 2013

since you are storing the retrieved rows in an array, just use shuffle() to randomize the rows in that array, then loop over the shuffled/random array to output the questions.
if ($ctr>0){
    shuffle($arr);
    foreach($arr as $question){
        echo $question;
    }
}
@jazzman, i don't know what you are reading in this thread, but the op has a number of questions for each difficulty/topic and wants to retrieve them and randomly output them. the problem is to output them without repeating any one question in a set.

Hey, thanks for the replies.

I did some research about shuffle(), it seems if i use shuffle() to randomize the questions, it seems only reversing the array data, for example array(1,2,3) becomes (3,2,1) or am I taking the wrong conclusion?

And about the code you posted, why is it not working for me? I mean, the questions are still repeatable and its not loaded clearly, i mean the answer and question data are all mixed up.

Can you give me another suggestion? Or should i post the screenshot?

Thanks before..

mac_gyver · May 23, 2013

shuffle should not produce the same result each time. with a small set of data, you may see the same result multiple times, but it should not be the same result every time. did you try it more than once and what's your current code?

puyunk · May 23, 2013

Thanks for the quick reply..

Yes, I did try it more than once..

I changed my current code to the code you posted before, with nothing else change, i mean the codes before them..

Edited May 23, 2013 by puyunk

mac_gyver · May 23, 2013

cannot possibly help you with your current problem without seeing the code that produces the problem. changing one character or one line in code can completely change the result.

puyunk · May 23, 2013

Then what should I do? I reached a stuck point..

What kind of code should I put here so you can help me solved this?

mac_gyver · May 23, 2013

you would post the same section of code that is in the first post in this thread, but what it currently is.

puyunk · May 23, 2013

<?php
  include "databaseconnection.php";
  if ($_GET)
  {
    $topic=$_GET["topic"];
	$level=$_GET["level"];
	$levelStr="";
	if ($level==0)
	{
	  $levelStr="Easy";
	}
	else if ($level==1)
	{
	  $levelStr="Medium";
	}
	else if ($level==2)
	{
	  $levelStr="Hard";
	}
	
	$sqlText="SELECT * FROM soal WHERE Difficulty='". $levelStr ."' AND Topic='". $topic ."'";
	$result=mysql_query($sqlText);

	$ctr=0;
	$arr=null;
	while($row = mysql_fetch_array($result))
	{
	   $arr[$ctr]=$row["SoalID"]."#".$row["Question"]."#".$row["Answer1"]."#".$row["Answer2"]."#".
	              $row["Answer3"]."#".$row["Answer4"]."#".$row["CorrectAnswer"]."#".$row["Picture"];
	   $ctr++;
	}
//    if ($ctr>0)
//	{
//		$totalRand=rand(10,50);
//		for ($i=0;$i<$totalRand;$i++)
//		{
//		   $idx1=rand(0,$ctr-1);
//		   $idx2=rand(0,$ctr-1);
//		   
//		   $res=$arr[$idx1];
//		   $arr[$idx1]=$arr[$idx2];
//		   $arr[$idx2]=$res;
//		}
//		$idx=rand(0,$ctr-1);
//		echo $arr[$idx];
//	}
      if ($ctr>0){
	  shuffle($arr);
	  foreach($arr as $question){
	      echo $question;
	  }
      }
  }
?>

Here's the code to get the question from the database..

I commented my old codes, and changed them to yours..

mac_gyver · May 23, 2013

the questions are still repeatable and its not loaded clearly, i mean the answer and question data are all mixed up.

that code cannot produce repeated questions, unless you have rows in your database table that are repeats.

since we only see the information that you post, perhaps you should copy/paste what you see in the browser.

puyunk · May 23, 2013

There are two screenshot I attached in this post..

the first one is when I used my old codes..

and second one is when I used your codes...

puyunk · May 23, 2013

Oh wow.. I think i found the reason why the question is not loaded perfectly if i used shuffle()..

The quiz are loaded inside a flash file, to load the question, I connect the array result to it..

The code inside the flash file (actionScript) is as follows:

....
var questionID:String = arrContent[0];
var questionL:String = arrContent[1];
var answerS1:String = arrContent[2];
var answerS2:String = arrContent[3];
var answerS3:String = arrContent[4];
var answerS4:String = arrContent[5];
var correctAnswer:String = arrContent[6];
.....

so, if i shuffle() the array, then the string will all be different.. That is why the code is not working as i wanted..

Seeing this, it raise another question..

How to randomize the array without changing the array numbers..

:confused:

mac_gyver · May 23, 2013

the code i posted isn't a copy/paste 100% complete tested solution, especially since no one here knew you were even outputting this to flash. it is an example showing how to use shuffle() to sort the rows in your $arr and to loop over the resulting random rows. if your code is doing more than that, it is up to you to take the method shown in the example code and modify it to suite your needs.

puyunk · May 23, 2013

Do you have any suggestion in how to make it just like what i need?

Would it be work if i do the shuffling inside the sql query rather than in the array?

It raised another problem, what kind of query then..

:confused:

Edited May 23, 2013 by puyunk

mac_gyver · May 28, 2013

since you have provided the bigger picture of how you are using the information (outputting one question at a time), the method to prevent duplicates must be different. you must 'remember' which questions have been output and remove them from the 'pool' of questions. which is what i suggested in post #3 in this thread.

i would store the SoalID id values in an array in a session variable, then don't select those ids in the query. if $_SESSION['used'] is an array of the id's of the questions that have been output (initialize to an empty array if it doesn't exist), adding the following to the query would only return questions that have not been used -

$ids = implode(',',$_SESSION['used']);
$not_in = empty($ids) ? '' : " AND SoalID NOT IN($ids)";
$sqlText="SELECT * FROM soal WHERE Difficulty='$levelStr' AND Topic='$topic' $not_in";

the previous code i posted would also need to simply output one (the first) element from the shuffled array (i.e. no foreach() loop.)

Edited May 28, 2013 by mac_gyver

Sign In

Generate non-repetition datas from database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information