php - 使用php和mysql从另一个表中使用多个ID从表中获取数据

Question

我有评估 id 。评估从多个题库类别中读取问题(从每个类别中随机抽取特定数量的问题)

我需要使用 php 和 mysql 进行查询以收集不同类别的问题。我已经尝试过了:

$query = "SELECT * FROM `questions`  WHERE `category_id` IN (SELECT   `category_id` FROM `assessment_category` WHERE `assessment_id`= $id   ORDER BY RAND() LIMIT 0,$number)";

但它给出了我的错误服务器版本不支持限制。

我还使用了内连接

$query ="SELECT *   FROM questions AS v INNER JOIN (SELECT category_id FROM assessment_category WHERE assessment_id = $id ) as v2  ON v.category_id = v2.category_id  ORDER BY RAND() LIMIT 0,$number";

但也不起作用。任何帮助将不胜感激。

谢谢。

Answer 1

这对我有用:

$cats // data for categories
$query2 = "";
$numItems = count($cats);
$i = 0;
foreach($cats as $cat){
    $cat_id = $cat['category_id'];
    $number = $cat['number_of_questions'];
      if(++$i === $numItems) {
            $query2 .= " (SELECT * FROM `questions` WHERE category_id = $cat_id ORDER BY RAND() LIMIT $number)";
      }else{
            $query2 .= "(SELECT * FROM `questions` WHERE category_id = $cat_id ORDER BY RAND() LIMIT $number)
                UNION ALL ";
      }                          
    $questions // execute your query;
}

谢谢大家