Simple End-User SQL Builder

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Simple End-User SQL Builder

Post by Swede78 »

I'm trying to build a form for non-technical users to build a targeted audience based on certain criteria/user attributes (Role, UserID, Birthdate, etc.). Because the users are non-technical, it needs to be simple logic. Therefore, the SQL being produced from the form will be very limited, using AND and OR logical operators, and =, !=, >, < comparison operators. This portion of the form is pretty straight-forward design. My problem is that I need to allow grouping of statements, and I'm struggling to come up with good usability. I tried searching for something similar out there, but with no luck.

Another thing I should add, is that I will be saving these queries in a database.

Here's an example of a basic WHERE CLAUSE that I'd like to produce from the form:
WHERE Role = 'abc' AND (UserID != 'xyz' OR Dept != 'defg')

I have a couple ideas of how to create this form.

1) Build a form where the end-user has the freedom to build the logic one piece at a time They would be fully responsible for putting together the correct structure/logic using any of the components available, including grouping.

PROs: This would probably be the easiest, most straight-forward form to build (in terms of development). The queries could be stored in a single database field.
CONs: It wouldn't be the easiest to use. The end user would have to understand what they're doing. I'd have to have a considerable amount of error checking/validation for the entire statement as a whole when they submit it.

2) Take a more structured approach, where the end user's available components for the statement may be limited based on what they already have used. As far as grouping, I was thinking of having a checkbox under each piece. The end user then selects a starting piece and an ending piece. Everything between is grouped together.

PROs: This would I think be a little easier to use, forcing the statement to be logical while they build it.
CONs: May need javascript/ajax because each piece would require validation and updating of the possible choices. Reloading the screen several times may interfere with usability. Not sure about that. Also, the db storage structure will most likely be a little more complicated. I'd probably have to save each piece of the statement separately.

I would appreciate any feedback on this. I would especially appreciate any links to other working solutions or concepts.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Simple End-User SQL Builder

Post by Benjamin »

I guess what I would do is make it as easy to use as possible while maintaining flexibility. I would ensure that the learning curve is minimal and everything they need to reference in order to build the queries is easily available.

My idea would be to have a textbox where users can type in a pseudo query. It would look something like:
get field_one field_two where Role is tech and (userid is not abc or dept is not def)
I could parse that out into an actually database query. Quotes would really only be needed if there are spaces involved.

Either way you look at it though there is work involved. You'll either need to write code to parse the pseudo query, or build a query editor. The query editor would be safer as far as security is concerned however. So if security is an issue I would most likely go with a query builder.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Simple End-User SQL Builder

Post by Ollie Saunders »

If a rules based system is powerful enough you should use that. Save this as an HTML file and have a look

Code: Select all

 
<style>span,a{font-family:sans-serif;font-size:small;}span,select,input{margin:0 0.2em}</style>
<p>
<select>
<option>Show only</option>
<option>Don't show</option>
</select>
<span>people</span>
<select><option selected="selected">earning</option><option>aged</option><option>occupied as</option></select>
<select><option>more than</option><option>less than</option><option selected="selected">between</option></select>
<input size="6" value="30000"><span>and</span><input size="6" value="50000"><span>dollars a year</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="http://">(Remove this rule)</a><br>
</p>
<p>
<select>
<option>Show only</option>
<option selected="selected">Don't show</option>
</select>
<span>people</span>
<select><option>earning</option><option selected="selected">aged</option><option>occupied as</option></select>
<select><option>older than</option><option selected="selected">younger than</option><option>between</option></select>
<input size="2" value="35"><span>years</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="http://">(Remove this rule)</a><br>
</p>
<p>
<select>
<option>Show only</option>
<option selected="selected">Don't show</option>
</select>
<span>people</span>
<select><option>earning</option><option>aged</option><option selected="selected">occupied as</option></select>
<select><option>farmer</option><option>retail manager</option><option>web developer</option><option>etc.</option></select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="http://">(Remove this rule)</a><br>
</p>
 
<p>
<a href="http://">(Add another rule)</a>
</p>
Obviously this kind of system would involve a far bit of logic. Otherwise you could consider a domain specific language. DSLs are very nicely discussed on the thoughtworks podcast, episodes 1 and 3: http://www.thoughtworks.com/what-we-say/podcasts.html
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Re: Simple End-User SQL Builder

Post by Swede78 »

astions,
I think I'm leaning towards doing something similar to my concept #2. Which as you stated would be a little more secure. It's not so much that I'm worried about hacking the database, but more that they get the results they expect. What the end-user will be able to control will only affect the WHERE clause. The SELECT statement will be pre-defined and the same for everyone. So, I'm not 100% sure, but I don't think too much harm can be done with the WHERE clause. But, I will still need to build the query based on the form input. And you're right, I think either way I go will result in a bit of work on my part. I just think concept #1 would be a little easier in terms of complexity and logic.

Ollie Saunders,
I appreciate the code snippet. That looks like what I pictured. Two things that will create the biggest challenge are adding additional rules without a page refresh and grouping rules to result in something similar to my sample SQL. Also, I'd have to address the issue of showing only the possible options based on the previous option chosen (again, preferrably w/o a refresh, most likely needed AJAX).

Thanks for the suggestions.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Simple End-User SQL Builder

Post by Ollie Saunders »

Well I was going to say you could use a library but it appears, after some searching, there isn't one. Bums.
Two things that will create the biggest challenge are adding additional rules without a page refresh
Yes you would need quite a bit of JavaScript
and grouping rules to result in something similar to my sample SQL
I don't think that aspect would be too difficult. You could just bundle up the results as JSON and then iterate over it.
Also, I'd have to address the issue of showing only the possible options based on the previous option chosen (again, preferably w/o a refresh, most likely needed AJAX).
It would be pretty rubbish if a page refresh was required so this would have to be JavaScript. But it's all basic logic, not too difficult but would take a fair bit of time.
Post Reply