0

I have the following code:

$query  .= "SELECT COUNT( ". $r ." ) AS Total, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=750) AS Eng, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=450 AND ". $r ." <=749) AS Pas, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." <=449) AS Dis
FROM  tresults;";

This is a multi-query and using PHP I can look through a number of instances and pull data back - which is fine.

However, what I need to be able to do is easily add additional parameters to the SQL query based on a users input from dropdown menus.

So, for example, a user might select a option from a drop down and then this needs to be appended to the SQL query, such as:

$query  .= "SELECT COUNT( ". $r ." ) AS Total, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=750 AND q16 = 'Yes') AS Eng, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=450 AND ". $r ." <=749 AND q16 = 'Yes') AS Pass, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." <=449 AND q16 = 'Yes') AS Dis
FROM  tresults WHERE q16 = 'Yes';";

There might be one append, as above, or several.

I have not attempted anything like this before, so I wondered if anyone has any advice before I delve in?

Homer_J
  • 3,277
  • 12
  • 45
  • 65

1 Answers1

0

Here is my general approach:

$filter = "AND q16 = 'Yes'"; //This value is dynamic depending on dropdown selection
$query  .= "SELECT COUNT( ". $r ." ) AS Total, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=750 " . $filter ." ) AS Eng, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." >=450 AND ". $r ." <=749 " . $filter ." ) AS Pass, 
(SELECT COUNT( ". $r ." ) FROM  tresults WHERE ". $r ." <=449 " . $filter ." ) AS Dis
FROM  tresults WHERE 1=1 " . $filter ." ;";
Aycan Yaşıt
  • 2,106
  • 4
  • 34
  • 40