--- a/displayConfidentialities.php +++ b/displayConfidentialities.php @@ -10,20 +10,26 @@ */ include_header("Confidentialities"); -$query = "SELECT value, procurementMethod -FROM `contractnotice` -GROUP BY procurementMethod "; +$query = ' +SELECT SUM(value) as value,count(*), reason +FROM +(select value, unnest(string_to_array(replace("confidentialityContractReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityContractReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.' +union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.' +union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" = "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.') a +GROUP BY reason ORDER BY value desc +'; -$result = mysql_query($query); + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); echo "
{$row[1]} | \$$value | |
{$row[2]} | {$row[1]} contract".($row[1] >1 ? 's':'')." | \$$value |