You want to produce a tabulated report from PHP using data from PostgreSQL.
Use PHP's pg_xxx functions, remembering to validate any incoming data and check return codes from system calls.
Below is the short version of this example. You can access it from your browser with index.php?orddate=2001-01-01 or similar.
<HTML>
<HEAD>
<TITLE>Example PHP Querying</TITLE>
<BODY>
<?php
$conn = pg_connect("dbname=pgtest user=richardh password=mypass");
$sql = "SELECT c.co_name as company, p.pr_desc as product, o.ord_qty as quantity ".
"FROM companies c ".
"JOIN orders o ON c.co_id = o.ord_company ".
"JOIN products p ON o.ord_product = p.pr_code ".
"WHERE o.ord_placed = '$orddate' ".
"ORDER BY company, product";
$res = pg_exec($conn, $sql);
$numrows = pg_numrows($res);
echo '<table border="1">';
echo '<tr><th>#</th><th>Company</th><th>Product</th><th>Quantity</th></tr>';
for ($i=1; $i<=$numrows; $i++) {
$row = pg_fetch_array($res, $i-1, PGSQL_ASSOC);
echo "<tr><td>$i</td><td>$row[company]</td><td>$row[product]</td><td>$row[quantity]</td></tr>\n";
}
echo "</table>\n";
echo "<hr/>Total of $numrows results.<hr/>";
?>
</BODY>
</HTML>
|
But - the previous example does no error checking or data validation. The example below shows the bare minimum of checking you should be using in the real world.
<HTML>
<HEAD>
<TITLE>Example PHP Querying</TITLE>
<BODY>
<?php
function show_table($orddate) {
$conn = pg_connect("dbname=pgtest user=richardh password=mypass");
if (!$conn) {
echo "Unable to connect to database<br>";
return;
}
$sql = "SELECT c.co_name as company, p.pr_desc as product, o.ord_qty as quantity ".
"FROM companies c ".
"JOIN orders o ON c.co_id = o.ord_company ".
"JOIN products p ON o.ord_product = p.pr_code ".
"WHERE o.ord_placed = '$orddate' ".
"ORDER BY company, product";
$res = pg_exec($conn, $sql);
if (!$res) {
echo "Query failed<br>";
return;
}
$numrows = pg_numrows($res);
if ($numrows > 0) {
echo '<table border="1">';
echo '<tr><th>#</th><th>Company</th><th>Product</th><th>Quantity</th></tr>';
for ($i=1; $i<=$numrows; $i++) {
$row = pg_fetch_array($res, $i-1, PGSQL_ASSOC);
echo "<tr><td>$i</td><td>$row[company]</td><td>$row[product]</td><td>$row[quantity]</td></tr>\n";
}
echo "</table>\n";
echo "<hr/>Total of $numrows results.<hr/>";
}
else {
echo "<hr>No results<hr>";
}
}
// Start of main code
//
// Don't allow index.php?orddate=2001-01-01
//
$orddate = $HTTP_POST_VARS[orddate];
// Make sure the date is valid
//
if ( ereg("([0-9]{4})-([0-9]{2})-([0-9]{2})",$orddate,$regs) ) {
if ( checkdate($regs[2],$regs[3],$regs[1]) ) {
show_table($orddate);
}
else {
echo "<p>Invalid date: $orddate</p>";
$orddate = '2001-01-01';
}
}
else {
$orddate = '2001-01-01';
}
?>
<FORM METHOD="POST" ACTION="index.php">
Date order was placed:
<INPUT NAME="orddate" TYPE="TEXT" SIZE="12" VALUE="<?php echo $orddate ?>"/><BR/>
<INPUT NAME="run" TYPE="SUBMIT"/>
</FORM>
</BODY>
</HTML>
|
In practice, you'd want to log database errors and provide a standard error message to the user. You'll also want better comments and some degree of output abstraction.