Geek asks and answers

Retrieving a record in accordance with the IN condition - duplicate values ​​in the IN section

I have questions on tables like

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
2    | prs         | 3
______________________________________
3    | oesl        | 2
______________________________________
4    | ocsl        | 3
______________________________________ 
5    | qoindos     | 1
______________________________________
6    | xyz         | 3
______________________________________
7    | mnlop       | 2
______________________________________
8    | cllse       | 2
______________________________________ 
9    | teuosn      | 4
______________________________________
10   | ulcd        | 2
______________________________________

I want to select 10 entries that will match the level of the question. I have a level order in which I want all the entries to be lower.

1,2,1,2,3,2,4,2,3,3

The exit should be

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
3    | oesl        | 2
______________________________________
5    | qoindos     | 1
______________________________________
7    | mnlop       | 2
______________________________________ 
2    | prs         | 3
______________________________________
8    | cllse       | 2
______________________________________
9    | teuosn      | 4
______________________________________
10   | ulcd        | 2
______________________________________ 
4    | ocsl        | 3
______________________________________
6    | xyz         | 3
______________________________________

I tried different solutions, but could not get the correct output. I tried with the field, find_in_set, but did not succeed. Refers to [ Force MySQL to return duplicates from the WHERE IN clause without using JOIN / UNION? but getting only the invoice and record in order

I tried to find solutions

SELECT question_level FROM `tbl_questions` WHERE `question_level` IN (1,2,1,2,3,2,4,2,3,3) ORDER BY FIELD(`question_level`, 1, 2, 1, 2, 3, 2, 4, 2, 3, 3) LIMIT 10

SELECT question_level FROM tbl_questions WHERE FIND_IN_SET(`question_level`,'1,2,1,2,3,2,4,2,3,3');

SELECT question_level,question_object_name,question_object_path,question_answer
        FROM tbl_questions e JOIN (SELECT 1 AS question_level UNION ALL
              SELECT 2 UNION ALL
              SELECT 1 UNION ALL
              SELECT 2 UNION ALL
              SELECT 3 UNION ALL
              SELECT 2 UNION ALL
              SELECT 4 UNION ALL
              SELECT 2 UNION ALL
              SELECT 3 UNION ALL
              SELECT 3
             ) matches
             USING (question_level) LIMIT 10;

I also tried using the foreach loop, but every time I got the same record when the question level matches the value

$array = explode(',', '1,2,1,2,3,2,4,2,3,3')
foreach ($array as $value) {
 SELECT question_level FROM tbl_questions 
 WHERE question_level = $value;
}

If this is not possible in mysql then this can be achieved with php.

+1
2

php

$sequence = "1,2,1,2,3,2,4,2,3,3";
$seq_arr = explode(',', $sequence);

1:

$questions = array(); // to store all questions 
$final_questions = array(); // tos store final questions in the sequence needed
$level_1 = array(); // level 1 questions
$level_2 = array(); // level 2 questions
$level_3 = array(); // level 3 questions
$level_4 = array(); // level 4 questions
$level_5 = array(); // level 5 questions

2:

Db

$this->db->select(*);
$this->db->from(questions);
$query = $this->db->get();
$result = $query->result_array();
for ($i=0; $i<count($result) ; $i++) {
 if(isset($result[$i]) && !empty($result[$i])){
  if($result[$i]['question_level'] == 1)
  {
    $level_1[] = $result[$i];
  }
  if($result[$i]['question_level'] == 2)
  {
    $level_2[] = $result[$i];
  }
  if($result[$i]['question_level'] == 3)
  {
    $level_3[] = $result[$i];
  }
  if($result[$i]['question_level'] == 4)
  {
    $level_4[] = $result[$i];
  }
  if($result[$i]['question_level'] == 5)
  {
    $level_5[] = $result[$i];
  }
 }
}

3:

.

foreach ($seq_arr as $key => $value) {
  if($value == 1)
    {
         for ($i=0; $i < count($difficulty_array) ; $i++) 
          { 
             if(isset($level_1[$i]) && !empty($level_1[$i])){
               if(!in_array($level_1[$i], $final_questions))
                 {
                    $final_questions[] = $level_1[$i];
                    break;
                 } 
             }
          }
     }
   if($value == 2)
     {
         for ($i=0; $i < count($difficulty_array) ; $i++) 
          { 
             if(isset($level_2[$i]) && !empty($level_2[$i])){
               if(!in_array($level_2[$i], $final_questions))
                 {
                    $final_questions[] = $level_2[$i];
                    break;
                 } 
              }
           }
      }
    if($value == 3)
      {
        for ($i=0; $i < count($difficulty_array) ; $i++) 
         { 
            if(isset($level_3[$i]) && !empty($level_3[$i])){
               if(!in_array($level_3[$i], $final_questions))
                 {
                    $final_questions[] = $level_3[$i];
                    break;
                 } 
             }
          }
     }
     if($value == 4)
       {
          for ($i=0; $i < count($difficulty_array) ; $i++) 
            { 
              if(isset($level_4[$i]) && !empty($level_4[$i])){
                if(!in_array($level_4[$i], $final_questions))
                   {
                     $final_questions[] = $level_4[$i];
                     break;
                   } 
              }
            }
       }
     if($value == 5)
       {
          for ($i=0; $i < count($difficulty_array) ; $i++) 
             { 
                if(isset($level_5[$i]) && !empty($level_5[$i])){
                   if(!in_array($level_5[$i], $final_questions))
                      {
                         $final_questions[] = $level_5[$i];
                         break;
                       } 
                 }
             }
          }
      }
print_r($final_questions);
0

, , ,

 ORDER BY FIELD(`question_level`, 1, 2, 1, 2, 3, 2, 4, 2, 3, 3)

, 1, 2, 1 question_level. . question_level # 1, , , .

id   | question    | question_level
______________________________________
1    | abc         | 1
______________________________________
5    | qoindos     | 1
______________________________________

, question_level=1 , , 1 , . , . , , , . , , , . , , , . , .

, , , , , , .

, .

x n, . ,

  • 2x lv 1
  • 4 lv 2
  • 3 lv 3
  • 1x lv 4.

, id . , .

, . RAND() , , , , . .

, .

SELECT
    q1.*
FROM 
    tbl_questions AS q1
JOIN
(
    SELECT
        id
    FROM
        tbl_questions
    WHERE
        question_level = 1
    ORDER BY RAND() LIMIT 2
) AS q2 USING( id )

, , .

0

More articles:


All Articles