Limit specific WHERE clauses in a SQL statement (MySQL)?

Question

I checked similar questions and tried answers relying on IN or JOIN but I could not figure it out.

I am trying to accomplish the following:

• allow a person to select a specific question_area
• allow a person to select the number of questions he or she wants for two or more difficulty levels within that question_area
• finally, fetch associative the results

I have the following query (I am using MySQL 5.6.17):

(SELECT question FROM question_active WHERE question_area = "B" AND 
   active_difficulty = 1 ORDER BY RAND() LIMIT 4) 
UNION 
(SELECT question FROM question_active WHERE question_area = "B" AND 
   active_difficulty = 2 ORDER BY RAND() LIMIT 3) 
UNION 
(SELECT question FROM question_active WHERE question_area = "B" AND 
   active_difficulty = 3 ORDER BY RAND() LIMIT 5)

I do not know how efficient it is, but it works. However, I run into two situations:

1. I cannot build a clean prepared statement using the PHP PDO class because I need three unique parameter markers for question_area even though it holds the same value.

As mentioned in the manual:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute().

2. I will be repeating large parts of the query. In fact, I need to add several other WHERE clauses that will be identical for all the levels of difficulty required.

My question is, how can I build the query without repeating myself so I can use the PDO class. I only need help on the SQL part, not the PHP.

I am naively hoping for a way to build a single query and apply LIMIT only to certain WHERE clauses.

I would really appreciate your help. Thank you!

Answer 1

$questions = [];
$sql = "SELECT question FROM question_active WHERE question_area = ? 
AND active_difficulty = ? ORDER BY RAND() LIMIT ?";
$stmt = $pdo->prepare($sql);
foreach ([1,2,3] as $level)
{
    $stmt->execute([$area, $level, $limit]);
    $questions[$level] = $stmt->fetchAll();
}