Clement Rasul: SQL Query Form Design Pattern Using "PostgreSQL PDO" driver

Friday, October 18, 2013

SQL Query Form Design Pattern Using "PostgreSQL PDO" driver

The following is a PHP Script that can serve as a design pattern for HTML form handling with "PostgreSQL PDO" connection driver for the PostgreSQL database.



<html>
<head>
<title>PostgreSQL PDO Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "postgres";
$pw = "wordpass123";
$db = "eis2013v2";

# Query Form
echo "<h1>PostgreSQL PDO Query Interface</h1>";
echo "<form action='postgresql_pdo_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
try {
    $con = new PDO('pgsql:host='.$host.';dbname='.$db.'', $user, $pw );
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

# SQL Query Script
$result = $con->query(''.$sql.'');
$num_rows = $result->rowCount();
$num_cols = $result->columnCount();
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";
echo "<table border='1'>";

# Table Heading
for ($i = 0; $i < $result->columnCount(); $i++) {
    $col = $result->getColumnMeta($i);
    $columns[] = $col['name'];
}

$f=0;
while ( $f < $num_cols ) {
	$fn = $columns[$f];
	echo "<th>".$fn."</th>";
	$f=$f+1;
    	}

# Table Content
# PDO::FETCH_ASSOC
# PDO::FETCH_BOTH
while ( $row = $result->fetch(PDO::FETCH_NUM) ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";

?>

</body>
</html>

Friday, October 18, 2013

SQL Query Form Design Pattern Using "PostgreSQL PDO" driver

No comments: