NYCPHP Meetup

[Roland Cozzolino]

I am not sure how flexible you are making this (can users do inner/outer 
joins, group by, etc.) but the way you set up the system seems a bit 
over complicated.  Based on what you said, there seems to be 2 rather 
simple options:
1) create a view with the parameters the user specifies.  I am not sure 
what db you are using, but if this option is available it is rather 
helpful and simple.
2) construct the query in php and save the query in the db with a name 
or something valid the user remembers.  If you allow users to name the 
query they construct, then you can save it to a table like this:
table: user_query
    query_id: 1
    name: 'Customer Report'
    query: 'select customer_id, customer_name from customer'
This will also allow for some nasty queries, joins, etc., where the 
hierarchical thing might get a bit on the insane side with a large 
complex query.


Allen Shaw wrote:

>Yeah, this is going to be saved in the database to have on hand for 
>later, part of a saved query setup.  And although the "buttload of 
>fields" idea occurred to me, the idea here is that I have to design this 
>to work even for tables that don't even exist yet.  Once a table or 
>relational set of tables is offered for querying, the admin has to mark 
>which columns are available, and then the user should be able to pick 
>any of those columns to display in the query *and* define filters based 
>on any combination of those available columns.
>
>The more I think about this the more it seems like I'm on the right 
>track with the recursive parent/child structure, but one thing that's 
>giving me pause is that a parent record has no criteria of its own, and 
>a child record has no boolean-type of its own; meaning I wind up with 
>basically two types of records in this one table.  Still, normalizing 
>beyond this point and trying to put those two types of records in two 
>different tables seems just insanely and unnecessarily complicated. 
>Um... right?
>
>Roland Cozzolino wrote:
>  
>
>>Just curious, it this something that needs to be saved to the database 
>>(sort of like saved queries a user can bring back)?  If not, you can do 
>>this with sessions.  I am actually implementing something very similar 
>>right now with open ended queries where we simply give the user a 
>>buttload of fields to choose from including boolean options.  Since I 
>>don't care about the query once it returns a result, I assemble it on 
>>the fly and use DOM/DHTML and a bit of ajax to deal with the 
>>addition/subtraction of query fields.  Not sure if this helps.
>>
>>Allen Shaw wrote:
>>
>>
>>    
>>
>>>All this heirarchical structure talk happens to come up as I'm trying to 
>>>implement a data filtering scheme for an ad-hoc querying interface.  
>>>Basically I want to allow queries as complex as the user wants -- not 
>>>just on one or two fields at a time -- so this has to be open-ended, and 
>>>it seems to be pointing me to a heirarchical structure.  I think I've 
>>>      
>>>
> >>...
>
>  
>