Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Thread: User profiling.

  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts

    User profiling.

    Now that I have my register, login and reset pass pages completed and working, I want to add a feature to let users create their own profile. I've been reading up about database design and table relationships.

    I'm thinking of creating a new table, with fields such as first_name, hobbies, etc etc. I thought about using my 'hash' column (which holds the generated random string to hash pass with) from my members table and create an identical field within the members_profile table to match the correct user. If matched I wanted to insert data into the members_profile table.

    Or, when the users registers at the site, and I insert their info to the database, should I update their id and username into the members_profile table as well at the same time, so that I can match it that way for the future potential profiling they may do?

    Would this work? And is it really a good way to go about it?

    Just looking for advice.

    Regards,

    LC.
    Last edited by LearningCoder; 10-11-2012 at 08:39 PM.

  • #2
    Regular Coder
    Join Date
    Jun 2008
    Posts
    680
    Thanks
    114
    Thanked 2 Times in 2 Posts
    The only thing you would need is the user_id field. No since adding more than one field that's a shared data. Just one column that is the same in both tables of your user profiles and your users tables. Easy for a one-to-one table relationship. Makes it easy for joining tables as well.

  • #3
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    Thank you,

    So what happens if I have 10 members and the 9th members updates their profile first, if using the user id, will that insert their data into the 9th row? And does that affect it in anyway when retrieving the data?

    I could potentially have a table which id fields go from say 5,8,10 should those members update their profile.

    Regards,

    Lc.

  • #4
    Regular Coder
    Join Date
    Jun 2008
    Posts
    680
    Thanks
    114
    Thanked 2 Times in 2 Posts
    It would not affect it, if done correctly, because just because user 9 updates their data as long as you are passing around or using the user_id you can use it for the WHERE clause in your SQL statement.

  • #5
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    Ok, do I insert their id and username into the member_profile table when I register them or only when they update their profile?

    Regards,

    Lc.

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Quote Originally Posted by LearningCoder View Post
    Thank you,

    So what happens if I have 10 members and the 9th members updates their profile first, if using the user id, will that insert their data into the 9th row?
    No it won't. It will simply create a new row and insert it there. If user 9 is the very first user to update / create their profile, their record will be the first row in the profile table.

    Thats the way mysql works. There is nothing wrong with this. As long as you have the user_id in the profile table you can still find the correct record.

    Quote Originally Posted by LearningCoder View Post
    And does that affect it in anyway when retrieving the data?
    Not in the slightest. You simply select the data based on the user id NOT the row position.

    Quote Originally Posted by LearningCoder View Post
    I could potentially have a table which id fields go from say 5,8,10 should those members update their profile.
    Yes indeed you could and this is perfectly normal / nothing wrong with it. Don't forget its only how it looks in phpmyadmin that you're really concerned about here and even that allows you to sort by a column either asc or desc
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    3 tables. Member, Field, and MemberField.
    Assuming you want the same questions available for everyone to (potentially) use. The Field would represent those possible options (hobby, birthday, lets see, favorite colour, etc), the member is well, your member, and MemberField is a correlation between a member, their "field" and their answer.
    Code:
    +---------------+        +---------------------+         +-----------------+
    | member        |        | memberQuestion      |         | question        |
    +---------------+        +---------------------+         +-----------------+
    | memberID [PK] |>|----o+| memberID [PK][FK]   |+o-----|<| questionID [PK] |
    | memberName    |        | questionID [PK][FK] |         | name            |
    | ...           |        | answer              |         | ...             |
    +---------------+        +---------------------+         +-----------------+
    Save the columns; use rows to represent the questions as you don't need to create a structural change (or programming one if you do it dynamically) in order to add more questions, remove, or modify them.

    Edit:
    BTW I renamed the "field" to question. Field is just way to ambiguous.

  • #8
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    Thank you for that Tango, I will give that a try.

    Fou, I don't understand one part of that, which I am glad you posted, because it shows there is a lot more to it than to just go ahead and start creating because I will inevitably run into an issue and come here. I am going to study the table relationship part and see if I can understand a bit more because at the minute I have not much knowledge...

    I'm having an issue at the moment with securing my data for insertion......

    I am sending my input through the addslashes() function but everytime I do, it adds 3 slashes before the related characters. magic_quotes and magic_quotes_gpc are both off as I would like to escape the data myself..

    Anyone know what's going on here? I presumed it might have been the magic quotes thing so I turned them off and it's still happening.

    Kind regards

    LC.

  • #9
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Fous explanation is pretty much the same thing that I was saying but dressed up in a different way and with a graphical explanation.

    PK = Primary Key - The primary key in the table you're looking at.
    FK = Foreign Key - The primary key of another table that you're going to select data from

    If you look at Fous graphical example, if you wanted to select a users question and answer for their profile you would:

    Select the user from the first table by their memberID, then select their answers from the next table by matching the memberID and then finally you would pull the questions to those answers from the next table by matching the questionID.

    I would have done it slightly differently having the question itself in the second table and the answers in the third table but it doesn't really matter as its an example.

    Also in your case you'd be running 3 select queries however on a busy web server using a shared hosting package that would put more strain on the SQL server. Therefore to reduce 3 queries to one, you'd want to join those tables temporarily in your SQL which is a very cool trick. I shall leave you to google mysql joins
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #10
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    Ah ok so when looking at the two memberID columns in the two tables there, they are basically the same thing as me using 'id' in the way which we set it to primary key and also auto-increment?? If that's right, does it also apply for the 3rd table questionID?

    Say I do it your way, selecting the member, 'question' then 'answer' tables...

    So the first table is the table which I currently have:

    members -> id,user,pass,email,join_date,hash,reset??

    The second table will contain the questions I ask the users such as:

    questions-> id, first_name, gender, hobby, age, etc..??

    And the 3rd table contains the answers to those questions:

    answers-> id, name,gender, hobby, age etc..?

    Is that right?

    I will google that shortly... ty

    Regards,

    LC.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    For your particular tables, I'd do as such:
    Code:
    +---------------+        +---------------------+         +-----------------+
    | member        |        | answer              |         | question        |
    +---------------+        +---------------------+         +-----------------+
    | id [PK]       |>|----o<| memberID [PK][FK]   |>o-----|<| id [PK]         |
    | user          |        | questionID [PK][FK] |         | name            |
    | pass          |        | answer              |         | description     |
    | email         |        +---------------------+         | required        |
    | join_date     |                                        +-----------------+
    | hash          |
    | reset         |        
    +---------------+
    Question would have records:
    Code:
    question
    1 | "gender" | "Are you male or female?" | false
    2 | "hobby"  | "What do you like to do?" | false
    3 | "age"     | "How old are you?"          | false
    Then answers for user 1:
    Code:
    answer
    1 | 1 | "male"
    1 | 3 | 20
    Then to query them:
    Code:
    SELECT m.id AS memberid, m.user, q.name, a.answer
    FROM member m
    LEFT JOIN answer a ON m.id = a.memberid
    INNER JOIN question q ON q.id = a.questionid
    Question could also make use of more fields to indicate what they can be. If you were to say it has to be an option, then give it options to select from, you can use that to force things like gender into male/female only options. My structure also assumes that you can only have one answer per question per user.

    As for addslashes, do not use them in conjunction with mysql_real_escape_string. You should be:
    - checking for magic_quotes_gpc
    - if enabled, execute stripslashes on input
    - insert/update using mysql_real_escape_string, or using prepared statement binding in PDO/MySQLi

    Magic_quotes_gpc are now gone, but I'd suggest that you keep checking until PHP is at least 6.5. I'd suggest to bind a prepared statement instead of using an escape as well.

  • #12
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Quote Originally Posted by LearningCoder View Post
    Ah ok so when looking at..
    See Fous latest reply. He's given you a really excellent example there.

    I'd recommend you create the 3 tables that Fou has given you and just have a play around with them and the SQL until you see whats going on

    Gotta hand it to Fou, far more patience than me.. I could never draw up ascii tables like that
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Hah thanks my space bar is getting quite the workout
    I'm still going to have to rehit the books on the crowfoot notation though. I did two different ones up for the same table structure, and something still doesn't look right o.O. I think the 1...n multiplier should be on the answers table, not the joined ones.

  • #14
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Quote Originally Posted by Fou-Lu View Post
    Hah thanks my space bar is getting quite the workout
    Lol

    Talking of which, am I the only one suffering the "my keboard keys get louder and clunkier with age" syndrome?

    I buy a new keyboard and its lovely and quiet. Within a few months its as loud as the last one

    PS I'm sure there used to be [ot] tags didn't there?
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #15
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    I am going to create those tables now and play around a bit and see where I get to.

    When asking the user for input, do I simply use a normal html form? If so, do I display the questions from the database within the <label> tag?

    Regards,

    LC.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •