Custom Query Interface - HELP!

[James Perry]

Hi,

I have been trying to build an custom report interface for a charity
manangement system; which is part of my dissertation. This interface
will allow the chairty to input a SQL query and submit the query. When
the submit button is executed, it will display the selected column names
and the query data. I am 80% finished but I have encountered a problem
though. I do not know how to display the dynamic column names?! Could
some please enlighten me as I feel like giving up! I can't find the
funtion/code I need!!!! HELP!!!!

[Jerry Sievers]

Re: Custom Query Interface - HELP!

James Perry <2perrj76@solen t.ac.uk> writes:
[color=blue]
> I have been trying to build an custom report interface for a charity
> manangement system; which is part of my dissertation. This interface
> will allow the chairty to input a SQL query and submit the
> query. When the submit button is executed, it will display the
> selected column names and the query data. I am 80% finished but I
> have encountered a problem though. I do not know how to display the
> dynamic column names?! Could some please enlighten me as I feel like
> giving up! I can't find the funtion/code I need!!!! HELP!!!![/color]


May depend on what DB you are using...

I use Postgres and in the docs you will notice functions to get the
number of fields returned by a query and also functions to return the
field name for a numbered field.

Iterate over the field names once to build your table header and then
go on to output the rows/fields as needed.

Assuming the other DB APIs offer same functionality, but no guarantee.

HTH


--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[Marcin Dobrucki]

Re: Custom Query Interface - HELP!

James Perry wrote:
[color=blue]
> I have been trying to build an custom report interface for a charity
> manangement system; which is part of my dissertation. This interface
> will allow the chairty to input a SQL query and submit the query. When
> the submit button is executed, it will display the selected column names
> and the query data. I am 80% finished but I have encountered a problem
> though. I do not know how to display the dynamic column names?! Could
> some please enlighten me as I feel like giving up! I can't find the
> funtion/code I need!!!! HELP!!!![/color]

This was my attempt when looking at some PEAR classes:

<?php

/**
* Short script for dumping stuff out of a database
*/

require_once 'HTML/Page.php';
require_once 'HTML/QuickForm.php';
require_once 'HTML/Table.php';
require_once 'DB.php';

$p = new HTML_Page();
$f = new HTML_QuickForm( 'irm_dumper');

$f->addElement('he ader',NULL,'Sub mit your query');
$f->addElement('te xt','query_stri ng','SQL Query (SELECT only)',
array('size' => 80));
$f->addElement('su bmit',NULL,'Que ry');
$f->addElement('re set',NULL,'Rese t query');

$p->addBodyContent ($f);

if ($_POST['query_string']) {

$dsn = array('phptype' => 'mysql',
'hostspec' => 'your.host.here ',
'username' => 'usr_name_here' ,
'password' => 'usr_passwd',
'database' => 'your_db_name') ;

$db =& DB::connect($ds n);

if (DB::isError($d b)) { die ($db->getMessage() ); }
else {

if (!preg_match("/^(UPDATE|INSERT )/i",$_POST['query_string'])) {

$query = stripslashes($_ POST['query_string']);

$sth =& $db->query($query );

if (DB::isError($s th)) {
$p->addBodyContent ("<p>ERROR<br>I nvalid SQL query!</p>");
$p->addBodyContent ("<p>" . $sth->getMessage() . "</p>");
}
else {
$t_attributes = array('rules' => 'all',
'frame' => 'border');

$t = new HTML_Table($t_a ttributes);

$db_header = $db->tableInfo($sth );

$headers = array();

foreach ($db_header as $c => $c_name) {
$headers = array_merge($he aders,array($c_ name['name']));
}

$header_attr = array('bgcolor' => 'steelblue');

$t->addRow($header s,$header_attr, 'TH');

$align_attr = array ('valign' => 'top');
for ($row=0; $row < $sth->numRows(); $row++) {
$t->addRow($sth->fetchRow(), $align_attr);
}

$light = array('bgcolor' => '#CCCCCC');
$dark = array('bgcolor' => '#AAAAAA');
$t->altRowAttribut es(1,$light,$da rk,TRUE);
$p->addBodyContent ($t);
}
}
else {
$p->addBodyContent ("<p>Sorry, only SELECT queries allowed!</p>");
}
}
}

$p->display();
?>