Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 11 down vote favorite
6

So, I need to construct a front-facing query builder for a database, but I'm having trouble finding existing code for what I can't imagine is a rare requirement.

Basically, I have non-SQL fluent people needing to build queries on the fly and view the results.

I found this: http://plugins.jquery.com/project/SQL_QUERY_BUILDER (Demo: http://ksistem.com/jquery/sqlbuilderdemo.htm)

But it requires that the database schema be hardcoded, rather than be dynamically generated.

Is there a better solution that dyamically pulls the database schema into a jQuery-like UI for building and executing SELECT queries against a MySQL database?

share|improve this question
You could also dynamically generate the parts of the Javascript which define the table structure. A script like this must only be accesible for people you really trust, it looks like this sends the query to the server by POST and just executes it, which would allow strangers to do anything with your db. – svens Jun 8 '10 at 18:59
1  
Thanks, I'm aware of the security issues; This would be connecting with a mysql user that is only able to do SELECT queries, and it is behind 2 separate authentication schemes; the people accessing this query builder will be trusted users. – emailq Jun 8 '10 at 19:00
can you give us an example of a non-sql statement that you need to parse? I assume you only want to turn them into standard SQL – Purefan Jun 10 '10 at 10:43
An app I work with regularly has a basic query function, but it's not nearly that fancy but works for non-SQL-fluent people. It has a table at the bottom where you check boxes of the tables you are interested, then it pulls in the available field names. Above there are two text boxes separated by a select dropdown; the select has options for "EQUALS", "DOES NOT EQUAL", "IS LIKE", etc. ...... wait, why am I describing this? Look at phpMyAdmin's "Search" function. Same thing almost. (The app I'm referring to isn't phpMyAdmin, just similar UI's for this.) – Nathan Loding Jun 11 '10 at 12:49
Unfortunately the jquery.com link is now broken. Anyone know where this plugin might have gone? – andronikus Nov 22 '11 at 20:06

4 Answers

up vote 1 down vote

Active Query Builder ASP.NET Edition's MS Access-like user interface is based on jQuery. It's a commercial component for ASP.NET 2.0 and higher. It can build SELECT statements for MySQL server.

Product page: http://www.activequerybuilder.com/product_asp.html Demo: http://aspquerybuilder.net

share|improve this answer
I wish if it was an open source! :(. +1 though – iSid Oct 19 '10 at 9:02
OP is using PHP, not ASP. – f1r3br4nd Oct 9 '12 at 18:08
up vote 1 down vote

You may be interested in MyQuery Builder (PHP/Javascript) http://sourceforge.net/projects/myquerybuilder/

I have not used it yet but it's feature list looks promising.

share|improve this answer
Removed from SF 14 months ago due to dormancy. – f1r3br4nd Oct 9 '12 at 18:10
It now lives at myquerybuilder.com – Rimu Atkinson Dec 11 '12 at 22:22
up vote 0 down vote

Dynamically creating the database schema might be a problem, because of the relations between tables. You could let the users carry the burden of selecting the relations but if i look at the "non-SQL fluent people" around me.. i'd probably not do that..

Other than that, if you want to create/check the structure on the fly, you practically have to create/check it every time the script is called. Generating a maybe huge, not necessary, overhead. I`d probably rather have a script/db-admin generate/update the structure every time someone twiddles with the database.

I doubt that you'll find something that does exactly what your looking for. Maybe pulling the structure out of a YAML file that's kept up to date by the db-admin might be better a better solution.

share|improve this answer
up vote 0 down vote

jQuery plugin link you have given is amazing. I think you can now create some functionality to generate JSON of schema for passing it to the jQuery plugin.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.