duangai1368
2019-04-19 20:00 阅读 88
已采纳

如何在PHP中控制绑定param变量

I'm selecting a list of exam IDs from my database, and then using a random one to select from a list of questions. If there are no matching questions, I want to pick another random ID and try again. Here is the code I have now; it works but will not return any questions for some of the exams.

$query = $connect->prepare("SELECT * FROM exam WHERE level=? AND flag=?");
$query->bind_param("si",$level,$flag);
$query->execute();
$result = $query->get_result();
$resultCount= $result->num_rows;
if($resultCount>0)
{
    while($row=$result->fetch_assoc())
    {   
        $list[]=$row['id'];
   }
    $indexRand=array_rand($list, 1);
    $query->close();
}
$query2 = $connect->prepare("SELECT * FROM questions WHERE exam_id=?");
$query2->bind_param('i', $list[$indexRand]);
$query2->execute();
$question_result = $query2->get_result();
$resultCount2= $question_result->num_rows;
if($resultCount2>0)
{  
    while($questions_rows=$question_result->fetch_assoc())
    {   
        $list2[]=$questions_rows;
    }
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanfu1942 duanfu1942 2019-04-20 04:55

    If I understand correctly, you can do this in a single database query.

    SELECT q.*
      FROM questions q
      LEFT JOIN (
        SELECT e.id
        FROM exams e
        LEFT JOIN questions q ON (e.id = q.exam_id)
        WHERE level = ? AND flag = ? AND q.id IS NOT NULL
        ORDER BY RAND()
        LIMIT 1
      ) i ON (q.exam_id = i.id)
      WHERE i.id = q.exam_id
    

    You join in a subquery as a table in your query. The subquery selects a single random item from the exams table, and itself joins in the questions table, to make sure that some questions exist. Demo is here: http://sqlfiddle.com/#!9/b394af/1

    点赞 评论 复制链接分享

相关推荐