user selects checkbox wich columns to retrieve

[Klein_Kipje]

I do not have code yet, i just want a general idea how to get started.

 

I have a database with +/- 15 columns. 6 people are using this database. Not all columns are relevant for each user.

 

So my idea was that each user will be able to make his preference by checking checkboxes for each column -> store this in the database so that when he logs in , his preference will be retrieved and used to make the right query.

 

second question, how do i make the headers so that they are the same als the preferences from the user ?

 

Just put me on the right track (or if someone has it on the shelf, post the code)

 

thanks in advance

[ginerjm]

Post the code? Uh, what code are you going to try and learn and do this complex thing with? PHP? JS? JQ?

 

It's all very doable but if you haven't programmed AT ALL in your life, you have a long road ahead of you. You might want to start with the simple stuff. There's a lot to learn about writing good, working, secure and safe code.

[mac_gyver]

I have a database with +/- 15 columns. 6 people are using this database. Not all columns are relevant for each user.

 

 

this sounds like a bad database table design.

 

correctly designed database tables have one row for each distinct data item, making it easy to insert, retrieve, update, delete, or associate each data item to related data in other tables.

 

post an example of your data is to get the best help.

[Klein_Kipje]

I have programmed in php / mysql but i teached myself. So to begin this i just wanted to asked your opion how to approach it

[ginerjm]

Create a 'preferences' screen where you show a checkbox for each column that people may choose to view.

 

Behind that screen your php script will grab those checks and store them under their id with multiple rows to contain each checkbox value. Ex.:

[id][col_id]
user1  1
user1  2
user1  5
user1  8
user1  9
user1  10
user1  13

Here 'user1' wants to see columns 1,2,5,8,9,10 & 13.

 

In your display script you will look up the preferences for 'user1' and then build a query string using the above choices as well as another table that links col_id to column names of your table. Ex.:

[col_id][col_name][col_label]
1       colname1   Col 1
2       colname2   Col 2
3       colname3   My Column
4       colname4   This Column
...
...

You have the query result of the user's preferences and you can include the above column info with that query (join) and then you loop through the results and build a query string. Ex.

$qry = "";
while($row = $results->fetch())
{
if ($qry=='')
$qry .= "select ".$row['col_name']";
else
$qry .= ",".$row['col_name']";
}
$qry .= "from (tblname) order by xyz where xyz";

 

Hope this makes sense - it is very OTT. (off the top).

Edited January 14, 2015 by ginerjm

[Psycho]

Regarding the process of using the saved preferences of the user:

 

I'm not knocking ginerjm's approach, but I would do it a little differently. I would not put logic into the query to determine which columns to fetch. If you make any mistake in properly validating the data you may create a potential hole in security. Instead I would have a single process to fetch the data that gets all the potential columns that users may want to see. Parameters I would put in such a process, if needed, would be things such as a sort order and a limit (if the user can define the page size). I would then modify the output when processing those record to determine which fields to include in the output.

[ginerjm]

I agree with Psycho - it does remove the security concern because you can then use a prepare query. My logic would work just as well to select the output fields instead of the query fields.