--- a/displayCategory.php +++ b/displayCategory.php @@ -1,46 +1,64 @@ -<?php -include_once ("./lib/common.inc.php"); -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)) { - setlocale(LC_MONETARY, 'en_US'); - $value = number_format(doubleval($row['value']) , 2); - echo ("<tr> - <td><a href=\"displayContract.php?CNID={$row['CNID']}\">{$row['CNID']}</a></td> - <td><b>{$row['description']}</b></a></td> - <td>\$$value</td><td>{$row['agencyName']}</td> - <td>{$row['contractStart']}</td> - <td>{$row['supplierName']}</td> - </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)) { - 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(); +<?php +include_once ("./lib/common.inc.php"); +if ($_REQUEST['category']) { + include_header($_REQUEST['category']." goods and services"); + echo "<center><h1>".$_REQUEST['category']."</h1></center>"; + $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> + <td><a href=\"displayContract.php?CNID={$row['CNID']}\">{$row['CNID']}</a></td> + <td><b>{$row['description']}</b></a></td> + <td>\$$value</td><td>{$row['agencyName']}</td> + <td>{$row['contractStart']}</td> + <td>{$row['supplierName']}</td> + </tr>"); + } + echo "</table>"; +} else { + /* + split by main categories + */ + include_header("Categories"); +$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>"; +} +include_footer(); ?>