7 Replies - 887 Views - Last Post: 23 August 2012 - 08:08 AM Rate Topic: -----

#1 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 14-February 11

Visibility of fields

Posted 17 August 2012 - 06:20 PM

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?
Is This A Good Question/Topic? 0
  • +

Replies To: Visibility of fields

#2 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: Visibility of fields

Posted 17 August 2012 - 06:59 PM

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?
Was This Post Helpful? 1
  • +
  • -

#3 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 14-February 11

Re: Visibility of fields

Posted 17 August 2012 - 07:15 PM

First of all, thanks.
However, the information cannot be null...that's my problem.
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: Visibility of fields

Posted 17 August 2012 - 07:31 PM

Why can't it be null?
Was This Post Helpful? 0
  • +
  • -

#5 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 14-February 11

Re: Visibility of fields

Posted 18 August 2012 - 06:19 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,436
  • Joined: 23-August 08

Re: Visibility of fields

Posted 18 August 2012 - 09:12 AM

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?
Was This Post Helpful? 0
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Visibility of fields

Posted 22 August 2012 - 09:04 PM

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.
Was This Post Helpful? 0
  • +
  • -

#8 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 14-February 11

Re: Visibility of fields

Posted 23 August 2012 - 08:08 AM

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?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1