[SOLVED] Any idea how to make this?

[php_guest]

I have an user information page. Some information are in page of every user. Those information will be stored in table users.

 

User can add into profile new type of info like you see in image.

 

 

How would you store user generated titles of information and values of it and how would you echo those information on user page? Please consider that those titles and values will be different in each user page and need to be echo together with first type of information.

 

The problem is that those information probably can not be stored in same table because you don't know name of fields yet and you don't know also name of rows.

 

Thank you very much

[tommyda]

You should start by playing around with

 

http://www.tizag.com/mysqlTutorial/mysqlinsert.php - Insert data into mysql database

http://www.tizag.com/mysqlTutorial/mysqlselect.php - Pull data from mysql database

 

Hope this helps

 

[MadTechie]

See outline below

 

Create Table: UserdefinedDetails

Add 4 Fields

ID = autonumber

UserID = the userID

uTitle = UserTitle

uValue = UserValue

 

Example or rows

ID - UserID - uTitle - uValue

1  -    10 - "Music" - "Rock, Rape"

2  -    10 - "About My Girl" - "She a pain at times"

3  -    11 - "Me" - "I am God"

Etc

[webref.eu]

Yes, I agree with the above, create a separate UserDefinedDetails table and then you can pull the required items from it by UserId. 

 

Rgds

[Zane]

Maybe I'm wrong or just going out on a limb, but would it not save more database space if you just created an array of all custom titles/values, serialized them, and base64'ed them.....then put that in its own column?

 

Example or rows

UserID - cFields

  10 - fsdfg4564sd122fdd56d5f5454df5987799

  11 - qdsafsda586455c15h5456f4e545r454f5

  12 - m1c2v12t1v21251gfg212v121bh21jh21d

Etc

 

Note that isn't a real base64 hash

[corbin]

Yeah but what if you wanted to manipulate them sometime?  Not sure why you would ever want to, but the possibility would be there.  Then again, it would be easy to just restore them.

 

 

Oh by the way, MadTechie, you like rape music?  Creepy ;p.

[Zane]

Yeah but what if you wanted to manipulate them sometime?

 

Easy, select it out the database

de-base64 it

unserialize it.

this would be the array you use to view the fields.

 

if you added more or edited them....all you have to do is code it such that it changes that array.

and then package it all up again. simple as that.

and update the database

[php_guest]

I am at beginning of php learning but as I understand you get in that way only one field of data  - one title and one value for one user. User can add more titles.

 

Maybe I solve this problem if I create 20 fields (user can add max. 20 own titles): uTitle1, uTitle2, uTitle3, ...uTitle20 and uValue1, uValue2, .... And for example if row inside uTitle1 WHERE UserID=$userID exist title is  added into uTitle2 field and value into uValue2. If also uTitle2 WHERE UserID=$userID exist the title is added into uTitle3.

 

What if I add every new title as a field of table and title value as a field value where row UserID=$userID? ALTER TABLE users ADD {$_POST[title]} varchar(30). Is that correct syntax?

 

 

[haku]

Maybe I'm wrong or just going out on a limb, but would it not save more database space if you just created an array of all custom titles/values, serialized them, and base64'ed them.....then put that in its own column?

 

You are right in that it would save database space, but why would you want to? If you serialize it and encode it, you are adding extra steps, each requiring more processing power (slower load times), and you will not be able to search the database on that data.

 

Mad Techie's method uses database normalization methods, which is generally the most efficient way to create databases.

[Zane]

Not a bad point haku, I believe I have a change of heart on my theory now.

[haku]

Maybe I solve this problem if I create 20 fields (user can add max. 20 own titles): uTitle1, uTitle2, uTitle3, ...uTitle20 and uValue1, uValue2, .... And for example if row inside uTitle1 WHERE UserID=$userID exist title is  added into uTitle2 field and value into uValue2. If also uTitle2 WHERE UserID=$userID exist the title is added into uTitle3.

 

This is not the way to go. There are a couple problems with it:

 

- you have empty columns in your database (unless the user has 20 titles)

- you are limited to 20 fields

 

You want to generally try to keep empty fields in your database to a minimum. Ideally, there should be done, but in reality that ends up creating more overhead than is necessary. Either way, having a table with 20 possibilities is definitely not the way to go.

[php_guest]

so how can be method MadTechie gave used for case if user add more titles? Is possible to add more value as array into one row? or is in this case the only option ALTER TABLE users ADD {$_POST[title]} varchar(30)

 

Thanks a lot

[MadTechie]

Okay the Idea behind my madness method is for every title the user gets a new record

 

so when you get the users details (including UserdefinedDetails) your will get more than 1 record.

 

example

<?php
//Normal stuff
$UserID = 10;
$query =  "SELECT * FROM users WHERE ID = $UserID";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row['Name'];

//Now UserdefinedDetails
$query =  "SELECT * FROM UserdefinedDetails WHERE UserID = $UserID";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
echo $row['uTitle']." - ".$row['uValue']."<br>";
}
?>

 

for the user to add a title just INSERT into `UserdefinedDetails` making sure you also set the UserID

[php_guest]

now I see. You created new row with same UserID for each new title. So I can have many rows with same UserID but different uTitle and uValue. I didn't notice that before. Thanks!

 

Example or rows

ID - UserID - uTitle - uValue

1  -    10 - "Music" - "Rock, Rape"

2  -    10 - "About My Girl" - "She a pain at times"

3  -    11 - "Me" - "I am God"

 

[haku]

Exactly. And so when you want to find someones categories, you just do a search on their user id.

 

This is called a 'many to many' relationship, because it allows you to connect many users with many categories. For example, Ten people may have the same category (many users) and/or one person may have ten categories (to many categories).