[Keylogger]

Hi,

Which kind of tables should I have to show/notShow fields that user set?

Name/on
Birth/on
Location/of

How my table should be like?

[Atli]

You don't really need a special kind of table for that. Just make the fields nullable, so that if no value is provided for them, they won't have a value.

For example, assuming a table like this:

CREATE TABLE example (
    user_id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR,
    birth DATE,
    location TEXT
);

INSERT INTO example (user_id, name, birth, location)
VALUES
    (1, 'John Doe', null, null),
    (2, null, null, 'New York, U.S.A.'),
    (3, null, '1953-02-31', null),
    (4, null, null, null),
    (5, 'Anna Smith', '1987-12-25', 'London, England');

You could use that in a PHP program like this. This code will show only the contents of the name, birth and location fields if they have values. Otherwise it'll be left empty.

<?php
/* I'll leave the connection parameters to your
 * imagination... */
$dblink = new PDO($dsn, $usr, $pwd);

/* Sometimes PDO does odd conversions on nulls, like
 * in PostgreSQL it'll turn them to empty strings
 * by default. This tells it to leave nulls as nulls. */
$dblink->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);

$sql = "SELECT user_id, name, birth, location
        FROM example";
$result = $pdo->query($sql);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    /* Show the user ID first. */
    $user_id = (int) array_shift($row);
    echo "User #{$user_id}:\n";
    
    /* Then print only the values that have... values. */
    foreach ($row as $key => $value) {
        if ($value !== null) {
            echo " - $key => $value\n";
        }
    }
    
    echo "\n";
}

Which would give you:

User #1:
 - name => John Doe
 
User #2:
 - location => New York, U.S.A.
 
User #3:
 - birth => 1953-02-31
 
User #4:

User #5:
 - name => Anna Smith
 - birth => 1987-12-25
 - location => London, England

Get what I mean?

[Keylogger]

First of all, thanks.
However, the information cannot be null...that's my problem.

[Atli]

Why can't it be null?

[Keylogger]

It's part of the profile, and I will need those fields later.

Like birth (for example), user could like not to show is birth to people, but I must know for further stuff.

This post has been edited by Keylogger: 18 August 2012 - 09:16 AM

[JackOfAllTrades]

Well then, you're looking for a user_preferences table, in a one-to-one relationship with your table, that dictates what fields can be shown. Don't think that can be dealt with at the DB level; that's an implementation-level detail.

/obvious?

[e_i_pi]

Unfortunately that sort of thing can be done at a DB level, at least in MSSQL. For example, if we have the table:
Cars

ID
Make
Model

...and we run this query...

DENY SELECT ON Cars(ID) TO MyUser

...then run this query as MyUser...

SELECT * FROM Cars

...then an error will be generated stating that the query cannot execute as the user does not have permissions on the column Cars.ID.

Clearly, this sort of micro-management of database permissions is fraught with danger. As previous posters have noted, doing it an an application/implementation level is a better way to go.

Alternately, if you need to protect the tables from direct SELECTion from certain connection credentials, but expose some data, then you could DENY SELECT over your tables, create a bunch of views that expose the non-sensitive data, and GRANT SELECT on the views instead. Still, there is going to be some amount of application-level management involved, at least in exposing only those pages/parts that allow INSERT/UPDATE/DELETE/DROP/CREATE to users that would ordinarily have that permission.

[Keylogger]

There's also another possibility: Create another table (visibility) with the following fields:

ID --- what --- hide --- idUser
1 Name 0 1
2 Birth 1 1
3 Name 0 2

Is this wrong?