php_guest Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/ Share on other sites More sharing options...
tommyda Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741667 Share on other sites More sharing options...
MadTechie Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741672 Share on other sites More sharing options...
webref.eu Posted January 21, 2009 Share Posted January 21, 2009 Yes, I agree with the above, create a separate UserDefinedDetails table and then you can pull the required items from it by UserId. Rgds Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741720 Share on other sites More sharing options...
Zane Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741821 Share on other sites More sharing options...
corbin Posted January 21, 2009 Share Posted January 21, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741857 Share on other sites More sharing options...
Zane Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741861 Share on other sites More sharing options...
php_guest Posted January 21, 2009 Author Share Posted January 21, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741961 Share on other sites More sharing options...
haku Posted January 21, 2009 Share Posted January 21, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741963 Share on other sites More sharing options...
Zane Posted January 21, 2009 Share Posted January 21, 2009 Not a bad point haku, I believe I have a change of heart on my theory now. Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741964 Share on other sites More sharing options...
haku Posted January 21, 2009 Share Posted January 21, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-741965 Share on other sites More sharing options...
php_guest Posted January 21, 2009 Author Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-742041 Share on other sites More sharing options...
MadTechie Posted January 21, 2009 Share Posted January 21, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-742050 Share on other sites More sharing options...
php_guest Posted January 21, 2009 Author Share Posted January 21, 2009 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" Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-742062 Share on other sites More sharing options...
haku Posted January 21, 2009 Share Posted January 21, 2009 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). Quote Link to comment https://forums.phpfreaks.com/topic/141680-solved-any-idea-how-to-make-this/#findComment-742164 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.