PHP / MySQL Query Builder UI with jQuery? [closed]

Question

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?

Achromat

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

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

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 – Flotage

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.) – Presentable

Unfortunately the jquery.com link is now broken. Anyone know where this plugin might have gone? – Charlacharlady

For future reference, here's a very promising (and updated) jquery querybuilder: github.com/mistic100/jQuery-QueryBuilder – Vesical

Answer 1

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

Saddlebacked

I wish if it was an open source! :(. +1 though – Lunn

Answer 2

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.

Winou