--- a/displayCategory.php +++ b/displayCategory.php @@ -3,14 +3,27 @@ if ($_REQUEST['category']) { include_header("Category"); echo "<center><h1>".$_REQUEST['category']."</h1></center>"; - $query = "SELECT CNID, description, value, agencyName, category, contractStart, supplierName - FROM `contractnotice` - WHERE childCN = 0 - AND category = '" . $_REQUEST['category'] . "' - ORDER BY value DESC"; - $result = mysql_query($query); - echo "<table>"; - while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { + $query = 'SELECT "CNID", description, value, "agencyName", category, "contractStart", "supplierName" + FROM contractnotice + WHERE "childCN" is null + AND category = :category + ORDER BY value DESC'; +$query = $conn->prepare($query); +$query->bindParam(":category", $_REQUEST['category']); + $query->execute(); + databaseError($conn->errorInfo()); + + echo "<table> <thead> + <tr> + <th>Contract Notice Number</th> + <th>Contract Description</th> + <th>Total Contract Value</th> + <th>Agency</th> + <th>Contract Start Date</th> + <th>Supplier</th> + </tr> + </thead>"; + foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row['value']) , 2); echo ("<tr> @@ -22,25 +35,30 @@ </tr>"); } echo "</table>"; - mysql_free_result($result); } else { /* split by main categories */ include_header("Categories"); -$query = "SELECT sum(value), category -FROM `contractnotice` -WHERE childCN = 0 -GROUP BY category ORDER BY sum(value) DESC "; -$result = mysql_query($query); -echo "<table>"; -while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { +$query = 'SELECT sum(value), category +FROM contractnotice +WHERE "childCN" is null +GROUP BY category ORDER BY sum(value) DESC '; +$query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + echo "<table> <thead> + <tr> + <th>Category</th> + <th>Total Contracts Value</th> + </tr> + </thead>"; + foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row[0]) , 2); echo ("<tr><td><A href=\"displayCategory.php?category={$row[1]}\"><b>{$row[1]}</b></a></td><td>\$$value</td></tr>"); } echo "</table>"; -mysql_free_result($result); } include_footer(); ?>