sql query builder

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi , What i exactly needed is, I need user to build his own query [only
the SELECT statement] on the client side. Something like user selecting
column name from the listbox,etc and selecting the condition.
for example: condition like between two dates or something like based
on employee name, or product name.
what i mean to say is, it should work for all possible condition. and
retrieved data should be displayed in the table.

Am using PHP 5.0, MySQL and IIS

Can anyone provide mean the code snippets similar to the above idea. I
have very much shortage of time to really do my own.

Please help....
Thanks in advance..

Re: sql query builder

sachu wrote:
Quoted text here. Click to load it

Impossible to do without knowing the structure of your tables, etc.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: sql query builder

Jerry Stuckle wrote:
Quoted text here. Click to load it

If you haven't got a lot of time then you're in trouble but anyway,
here's a very basic (and abridged), single-page example to get you

It uses a value passed to the end of the url to reorder the result list
but you can adapt this technique (usually with a 'submit' button
attached to a form) to suit your purposes...


//Allow user to sort list by '$key'

$iskey = @$_GET['key'];
if (is_null($iskey)){
   $key = 'f_name';}
else{$key = $iskey;}

$query = "SELECT * FROM table1 ORDER BY $key ASC;";

$result = mysql_query($query1) or die ("Could not execute query1.");

/* Display (abridged) results in a table */

echo "<table width='100%'>\n";
echo "<tr valign='top'>\n";
echo "<th><a href='$PHP_SELF?key=f_name'>First name</a></th>\n";
echo "<th><a href='$PHP_SELF?key=l_name'>Last name</a></th>\n";
echo "<th><a href='$PHP_SELF?key=company'>Company</a></th>\n";
echo "</tr>\n";
echo "</table>\n";


So not exactly 'impossible' after all.

Re: sql query builder

sachu wrote:

Quoted text here. Click to load it

This cannot be done in a short amount of time, but I can tell you what you
have to have.  

First you need a data dictionary, the list of columns and tables.  Foreign
keys are very important if you want to allow multi-table queries.

For the very basics, you need then 4 selections for the user:

1)  User chooses 1 table (more complex: user picks multiple tables)
2)  User chooses columns
3)  User chooses columns to order by out of avail columns
4)  User manually inputs filter expressions for some columns

Building SQL out of something like this is child's play, for example:

// Assume $cols contains array of chosen columns
$SQL_Cols = implode(',',$cols);
$SQL_OB   = implode(',',$cols_orderby);
$sql="SELECT ".$SQL_Cols." FROM ".$table." ORDER BY ".$SQL_OB;

This is of course missing all kind of details, but it should give you some
flavor of how easy it can be, and yet also how long it can take to really
nail down all of the details.

Our own framework has a prototype of this kind of thing.  Email me offlist
and I can provide you with a login.  The code is all GPL, so you are free
to download it from our site and examine it.  But if you use any more than
a few snippets, you have to abide by the terms of the GPL.

Kenneth Downs
Secure Data Software, Inc.

Re: sql query builder

Hi All,
Thanks for your valuable information.
Below is the table structure for which i need the sql builder.
There are 10 fields all are varchar2 and with one primary key. No
foriegn key and is not related to any other table.

Site Timeline