Table with variable columns

[eluser]Jacob Graf[/eluser]
I am creating a reporting tool with about 17 different attributes tied to a database. For each attribute, you can specify what you'd like that attribute to be as well as if you'd like that attribute to show up on the report (table). How can I query a database and select data using the criteria specified and then display that data based on which attributes the use would like to see on the report?

Let me know if you need clarification. It's a little tricky to describe, but basically, it's a dynamic table and I can't figure out how to code a table that is not the same every time.

Thanks in advance for your help!

[eluser]sholnay[/eluser]
You might want to think about table normalization for this issue. The more normalized your database is - the easier it will be to associate data between multiple tables.

http://en.wikipedia.org/wiki/Database_normalization

I might not be understanding the question fully - but one approach might be to have say - an attribute column in your reports table, and a corresponding attribute table. In any given report row for attributes you would store an array of attributes the user selected - which you could create subqueries to the attribute table - or something on those lines.

Not sure if that was helpful - maybe showing a snippet of your schema or creating a 'dumbed' down version so we can get a better idea of the issue (not sure if we can be helpful if were looking at 17+ columns of info ).

Good luck!

[eluser]Jacob Graf[/eluser]
Maybe this will help. Here is the app I started writing using traditional PHP. The dropdowns indicate criteria for the search and the checkboxes determine if that attribute should appear on the report.

http://www.graftechhosting.com/

Does that help at all?

[eluser]sholnay[/eluser]
I'm pretty sure this would not be the optimal solution but:

It appears that you will let people choose features of a phone - and then return the phones that match those criteria. An easy solution would be to have a Phones table and in the table you would have a column for every criteria category you have on your page. In other words, Category, Manufacturer, touch screen, etc. Your query is going to be built dynamically every time you submit the page.

From there - its pretty easy to use the submitted data to weed out the phones.

SELECT phones
WHERE category IN ( list,the,categories,the,user,chose )
AND manufacturer IN ( another,list,here )
AND qwerty = 'true'
etc..

not sure if you're using the active record etc - but you can probably get the gist of the query you would build.

Am I missing the target of what you want to do here? (I apologize if I am hehe)

[eluser]Jacob Graf[/eluser]
That's what I have now. I have a table called devices and a column for each feature. The problem is not in querying the database for phones that match that criteria, the problem is in the checkboxes that determine if the columns show up on the report.

So consider this, with the way I'd like it to work, you could query the database for phones with a qwerty keyboard, but you may not necessarily want that to show up on the report (for printing/clutter purposes)

So now that I have all my data, how do I build a table based on which columns the user wants to see on the report?

Thanks for your help. I look forward to your response!

[eluser]sholnay[/eluser]
Well, I think that is as easy as setting up your query dynamically based on what the user has clicked.

In your select statement - you would build the data that you want to show BASED on those checkboxes.

So if the user checked 'category' and 'style' for instance, you would build the query like this:

SELECT category,style FROM devices
WHERE the data matches the criteria.

You could also SELECT * and then when you're processing the data for display - use some logic to filter for only what the user chose (using the posted data from the form).

I think filtering at the query level would be more efficient though.

Am I still off target here?

[eluser]bradym[/eluser]
Building from sholnay's example, something like this should work:

Code:

$sql = "SELECT ";

$fields_selected = array('category' => 'selected category', 'style' => 'selected style');

$sql .= implode(',',array_keys($fields_selected));
$sql .= " FROM devices WHERE ";

foreach($fields_selected as $field => $value)
{
   $sql .= $field .' = '. $this->db->escape($value);
}

$this->db->query($sql);

Of course $fields_selected would be the checkbox values the user selected.