How would I setup a table for information?

[shortysbest]

Currently I have a table called Info and it has a number of columns in it:

 

id---UserId---Introduction---About---Occupation---Phone---Email---Gender---Birthday---Location

 

This works ok, however if I want to have a way for users to show/hide different things how would I do that? Would the best way be to add in a second row for each? Or is there a better database design?

 

id---UserId---Introduction---introductionShow---About---aboutShow---Occupation---occupationShow---Phone---phoneShow---Email---emailShow---Gender---generShow---Birthday---birthdayShow---Location---locationShow

 

That's how that would look, and that definitely cannot be the best/only way to accomplish this, can it?

 

Another question, is there a simple way to retrieve each row in a php loop? Like foreach mysql_col_name()

 

while(){

<div class="info-section-wrapper">

<div class="info-left">$colName</div>

<div class="info-right">$colValue</div>

</div>

}

 

[IrOnMaSk]

what kinda datbase system do you have?

So you want your users login into the database and be able to do the thing you describe?

you might want to look into Creating View: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

 

And for retrieving data from db I usually use the good old way (fetch_array) in the while loop

[shortysbest]

I have a website all built, and the users will be able to edit their information from the website. My website is a social networking website.

I use MYSQL

[TOA]

That's how that would look, and that definitely cannot be the best/only way to accomplish this, can it?

 

You're absolutely right about that  Normalization is what you need, and in particular, relational tables. T'were me, I would go this route:

Info table

UserID, ListID <-- *note* This is a foreign key, coming from the table below

 

List table

ListID, Field, Show <-- *note* 'Field' would be where you stored the name of the field such as Phone, email, etc, and 'Show' would be a true/false value indicating show or hide

 

Then you use a join (most likely inner) to link the info when pulling it from your db.

 

Another question, is there a simple way to retrieve each row in a php loop? Like foreach mysql_col_name()

 

while(){

<div class="info-section-wrapper">

<div class="info-left">$colName</div>

<div class="info-right">$colValue</div>

</div>

}

 

Yep. See below.

 

while($row = mysql_fetch_assoc(YOURMYSQLQUERYRESULTHERE)) { 
      // do something with each row
}

 

Hope this all helps

[IrOnMaSk]

oh ok so you don't want them to log into your database (don't do that )

If everything is done using web application, maybe create a profile for your users, and when they login to that profile you display all the info that you want them to see (you're in control). And if they want to update their info, just do a simple update or alter table statment from there... (if you update the db base on user input dont forget to sanitize the query)

 

hope this somewhat help or point u in the right direction... let me know

[shortysbest]

Iron, Thanks for your responses but I don't think you quite understand what I'm trying to accomplish - I already have a profile system and everything all set up, I've got everything to work, however I'm just trying to find a better - more efficient way of doing so

[IrOnMaSk]

oh Oopsi

[shortysbest]

I don't think I'm understanding how you would do this?

Maybe elaborate some more because the way I understand this now wouldn't work - I don't think.

 

Info table
UserID, ListID <-- *note* This is a foreign key, coming from the table below

List table
ListID, Field, Show <-- *note* 'Field' would be where you stored the name of the field such as Phone, email, etc, and 'Show' would be a true/false value indicating show or hide

[TOA]

You were attempting to turn this

id---UserId---Introduction---About---Occupation---Phone---Email---Gender---Birthday---Location

 

into this

id---UserId---Introduction---introductionShow---About---aboutShow---Occupation---occupationShow---Phone---phoneShow---Email---emailShow---Gender---generShow---Birthday---birthdayShow---Location---locationShow

 

if I'm understanding you correctly. And the point was to allow people to turn on/off parts of their profiles for display. Correct?

 

So we take all those parts out of the original table and create a new table out of them, adding a field that takes a true or false value that tells us whether or not to show it.

 

The new table's list id goes into the old table as a foreign key.

 

Here's an example:

 

Info Table

UserID  |  ListID

ibauser  |  1

 

List Table

ListID  |  Field  |  Show

1  |  fakeemail@faker.net  |  1

1  |  555-555-5555  |  0

 

This shows imaginery data. You would get everything by using sql similar to this:

SELECT your,fields,here FROM Info INNER JOIN List ON Info.ListID=List.ListID WHERE UserID='ibauser' AND Show='1'

 

Hope that helps to clarify.

 

Let me know if you need more

[shortysbest]

Oh ok, so listID would just be a number for the field for example listID 1 would represent email, listID 2 would represent phone, etc?

[TOA]

Oh ok, so listID would just be a number for the field for example listID 1 would represent email, listID 2 would represent phone, etc?

 

Close, but not quite. For that, you would add a primary key, most likely auto-incrementing. I can help throw that in if you need it.

 

ListID would represent all the choices ibauser made. Notice all the ListID's in the List table say 1. That means that all those choices belong to the User with ListID 1.

Make sense?

 

So... ibauser has an email of fakeemail@faker.net and a phone number 555-555-5555, and only wants to show the email address because it's marked with a 1 for true. To get his info, we would grab all fields in List that have a ListID=1 because that's ibauser's ListID. See what I mean?

 

 

[shortysbest]

I really appreciate all your help, but the way you are doing it doesn't really make sense. Could you show an example so I could see how you Have ot thought out. Maybe make a complete example with two different users that have their email, and phone with one hidden, one showing.

The usersID Should be a number, so use 1 and 2 tonrepresrnt the two different users.

Hope I'm not asking for too much, thank you very much.

[TOA]

Info Table

UserID  |  UserName  |  ListID

    1      |      ibauser  |    1

    2      |    i2bauser  |    2

 

List Table

ID  |  ListID  |  Field  | Show

1  |    1      |  fake@email.com  |  1

2  |    1      |  555-555-5555  |  0

3  |    2      |  fake2@email2.com  |  0

4  |    2      |  555-555-1234  |  1

 

 

User 1 (ibauser) has his email showing according to ListID 1

User 2 (i2bauser) has their phone number showing according to ListID 2

 

Think of it this way: A user owns the listID associated to it in the Info table, so anything with the matching listID in the List table is his data

 

Hope that helps 

[shortysbest]

Ah I see. Thanks a lot. Ill try it once I get home, should be ok. And Also, isn't listID pointless to have? Can just user ID in place of it. But thanks a lot for the ideas.

[TOA]

And Also, isn't listID pointless to have? Can just user ID in place of it. But thanks a lot for the ideas.

 

Sure it could, but if you read up on normalization, you'll see why its usually done this way. Has to do with unique keys and such; but yes, you could do it that way.

 

Good luck!

[shortysbest]

I have put these into the database how you showed me earlier, now I just need to figure out how to manage the name of the info field.

 

$title = array( 1 => 'Introduction', 2 => 'About Me', 3 => 'Occupation', 4 => 'Phone', 5 => 'Email', 6 => 'Relationship Status',  7 => 'Gender', 8 => 'Birthday', 9 => 'Location');

 

in the database i have numbers next to each to indicate which field is which, so 1 means introduction, 2 means about me, etc.

The problem is I need to replace that number with the correct field name.

 

so 1 would change to Introduction.

 

putting them in that array and putting them inside the while loop works, but it's not very good,  because if someone hides one of their information spots, or doesn't fill it in, then it would throw the names off and they would be next to the wrong field.

 

So what I need to do is detect the number, when it's found the number match it up with the number of the title and print that. if that makes sense?

[TOA]

Most of it  You lose me a little at the end there.

 

Can you show a dump of your db so we can see your structure?

 

I think I get what you mean, but seeing the actual thing will help.