sql query builder

[sachu]

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..

[Jerry Stuckle]

Re: sql query builder

sachu wrote:

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..
>

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


--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[ronverdonk]

When you just stick to the columns themselves it will be fine. You just have to display all the columns onto your screen with a checkbox or a text input and the user can click or fill-in the selection data. Each field can then be inserted in the SELECT .. WHERE statement.

However, if you want to condition and select in ranges (like a date-range) you cannot just do that with a simple select box or text field. Then you need to setup 2 boxes for (e.g.) each date. And that is a lot more difficult.

So, it depends on your requirement how complicated it has to be.

Ronald :cool:

[strawberry]

Re: sql query builder


Jerry Stuckle wrote:

sachu wrote:

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..

>
Impossible to do without knowing the structure of your tables, etc.
>
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

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
started:

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($qu ery1) 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'>Fi rst name</a></th>\n";
echo "<th><a href='$PHP_SELF ?key=l_name'>La st name</a></th>\n";
echo "<th><a href='$PHP_SELF ?key=company'>C ompany</a></th>\n";
echo "</tr>\n";
echo "</table>\n";

?>

So not exactly 'impossible' after all.

[Kenneth Downs]

Re: sql query builder

sachu wrote:

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.

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(',',$co ls);
$SQL_OB = implode(',',$co ls_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.
(Ken)nneth@(Sec )ure(Dat)a(.com )

[sachu]

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.