Hey forum :laugh:
I have made a program in PHP that is a Postgres Database Viewer. All the pages are at the bottom of this page.
Question time 😄
1) How can i make the Tabe Headder that i have setup in view_data a hiperlink to sort it by the corrosponding field?
2) How can i make the script faster/stronger? It crashes with Large tables. And this is sort of what it is designed to view.
3) How can i make the database im viewing dynamic? So i only have to enter the Connection details to the Postgres server. Is there a function to list all the data bases?
4) Is it possable to make a function to Export this data to a Excell document? On a click of a button?
Keeping in mind that this is suppost to work on ANY database, so the idea is to hard code Nothing 🙂
Thanks 🙂
index.php
<?PHP
// index.php
// Creates a list of all fields and allows user to select them
//------------------INCLUDES--------------------------
include("config.inc"); // Include Config
include("db_connect.inc"); // Connect to the DB
include("functions.inc"); // Include Functions
//-----------------END INCLUDES-----------------------
starthtml ($view_table, $view_table);
$sql_query = pg_exec($db_conn,"SELECT * FROM $view_table LIMIT 1");
$field_count = pg_numfields($sql_query);
$rows = pg_numrows($sql_query);
if ($rows > 0){
echo "<html>";
echo "<form name='view_columns' action='view_data.php' method='post'>";
echo "<table>" ;
for ($i=0; $i < 1; $i++) {
// For each row, print the name!
for ($j=0; $j < $field_count; $j++) {
echo "<tr>" ;
$field = pg_fieldname($sql_query,$j);
echo "<td><input type='checkbox' name=$field value=$field checked='checked'></td>";
echo "<td>View--$field </td>";
echo "</tr>";
}
}
} else {
echo "<font size='5'>Incorrect Config.inc file, please Check it.</font>" ;
}
echo "</table>" ;
echo "<hr>" ;
if ($rows > 0){
echo "<font size='5'>Sort By:</font>" ;
echo "<select name='sort_by'>";
echo "<br>";
for ($i=0; $i < 1; $i++) {
// For each row, print the name!
for ($j=0; $j < $field_count; $j++) {
$sort_by_field = pg_fieldname($sql_query,$j);
echo "<option value=$sort_by_field>$sort_by_field";
}
echo "</select>" ;
}
} else {
echo "<font size='5'>Incorrect Config.inc file, please Check it.</font>" ;
}
echo "<BR>" ;
echo "<input type='submit' value='Get Data!'>" ;
echo "</form>" ;
echo "</html>";
pg_Close($db_conn);
?>
view_data.php
<?php
// vied_data.php
// Views the Selected columns from before
//------------------INCLUDES--------------------------
include("config.inc"); // Include Config
include("db_connect.inc"); // Connect to the DB
include("functions.inc"); // Include Functions
//-----------------END INCLUDES-----------------------
//------------------VARIABLES-------------------------
$query = "SELECT * FROM $view_table";
$sql_query= pg_query($db_conn, $query);
$field_count = pg_numfields($sql_query);
$rows_count = pg_num_rows($sql_query);
//----------------END VARIABLES-----------------------
starthtml ($view_table, $view_table);
for ($i=0; $i < 1; $i++) {
for ($j=0; $j < $field_count; $j++) { // For each field in the database
$field = pg_fieldname($sql_query,$j); // Get field name
$field=$_POST[$field]; // Get the Variable from index.php
if ($field != ""){ // If box is selected
$sql_select .="\"$field\", " ; // Add it to the Query IN "" quotes...
}
}
}
// Trim the select statment to get the Last ", " off so the statment is correct
$sql_select1 = rtrim($sql_select, 'a..z');
$sql_select2 = rtrim($sql_select1, ', ');
// This is the ORDER function. Checks if the Sort By box is selected, and adds it to the Query if it is...
$sort_by= $_POST[sort_by]; // Get the Variable from index.php
echo "<br>";
if ($sort_by != ""){ // If box is selected
echo "<br>" ;
$sort_by="ORDER BY \"$sort_by\"" ; // Add it to the Query with "ORDER BY IN "" quotes...
} else {
$sort_by=""; // Else Dont Sort it!
}
//We have to redifign the vars to use NEW select, not the * one.
$query1 = "SELECT $sql_select2 FROM $view_table $sort_by";
$sql_query1= pg_query($db_conn, $query1);
$field_count1 = pg_numfields($sql_query1);
$rows_count1 = pg_num_rows($sql_query1);
if ($rows_count1 > 0){ // If there are any rows
//----Print the headdings----
echo "<table border cellpadding=0 cellspacing=0>" ;// Start Table
echo "<tr>" ; // Start Row in Table
for ($j=0; $j < $field_count1; $j++) { // For each Row in the Selected Fields
$field = pg_field_name($sql_query1,$j); // Get the Field Name
echo "<td><b><font color='red'>$field</font></b></td>"; // Print it into the First row
}
echo "</tr>"; // End the Row
//----Done Headdings----
//----Print Data----
for ($i=0; $i<$rows_count1; $i++) {
echo "<tr>" ; // Start the Row
$row = pg_fetch_object($sql_query1, $i); // Get the Row data
for ($j=0; $j<$field_count1; $j++) { // For each field
$column = pg_field_name($sql_query1,$j); // Get the field name
?>
<td><font size="-2"><?PHP echo $row->$column ; ?></font></td>
<?PHP
}
echo "</tr>";
}
echo "</table>";
}else { // We dont have data, display the error message
echo "<font size='5'>No data available (Or nothing is selected to be Displayed) Please check the Query...</font>" ;
}
//----Done Data----
pg_close($db_conn); // Close the Database
?>
functions.inc
<?PHP
// Functions.inc
// All the Functions that are used are defigned in here...
// Start HTML headers and Print View Table/Set Title Bar.
function starthtml($title, $view_table) {
echo "<html>";
echo "<title>$title</title>";
echo "<hr>" ;
echo "<h1><b><center>You are viewing $view_table</h1></center>" ;
echo "<hr>" ;
}
?>
db_connect.inc
<?php
// db_connect.php
// Used to connect to the Database
$db_conn = pg_connect("host=$db_host port=$db_port dbname=$db_name user=$db_user password=$db_pass");
if( !$db_conn ) {
echo "Could not connect. Please check the Config.inc file.";
exit;
}
?>
config.inc
<?php
// Config.inc
// file for all the static Variables
//-----Connection Details for PostgreSQL------------
$db_host="*******" ; // Database Host
$db_name="*******" ; // Database Name
$db_user="*******" ; // Database User
$db_pass="*******" ; // Database Password
$db_port="5432" ; // Database Port
//-----------END CONNECTION DETAILS-----------------
$view_table="*******" ; // Table to view data for
?>
