SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi..

    I have 2 tables in Mysql.. 1 table call member_profile and other called login_account

    Now, I want to create a php that allow member to update their login info and also member profile.

    Let say, I allow them to change their email address and user password in my changeinfo.php and the email address I store it in member_profile and the user password was stored in login_account. I usered the unique key userid in both table.

    What is the best way to update both fields in 2 different tables with 1 mysql query ? Do I need to use 2 mysql query ?

    If I want to create a user account using another php script call signup.php. And I included fields from both member_profile and login_account. Can I use a single INSERT query to create a new membership account ?

    Let say I have setup my database like this:-

    member_profile
    --------------
    ID
    userid
    title
    fullname
    sex
    dob
    ic_no
    tel_hse
    tel_office
    fax
    email
    address_hse
    postcode_hse
    country_hse
    address_office
    postcode_office
    country_office
    address_mail
    postcode_mail
    country_mail
    membership_no
    membership_cat
    nationality
    professional_qua
    academic_qua
    job
    research_area
    payment
    remark

    login_account
    -------------
    userid
    password
    userlevel

    Is the database design ok ? Any suggestions ?
    Ngu I.P.
    Web Developer

  2. #2
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I do not really understand why you use a separate table for password and userlevel, one user can have one password only and one fullname, sex... only, so why use two tables? Actually there is no "1 to many" ore "many to many" relationship. Correct me if I'm wrong.
    For the update staff, it shouldn't be a problem to update two tabels in one query, as it is no problem to INSERT INTO two tables in one query. But I saw some skripts where they use two querys, don't know why?

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with smorb that their is no reason why you need to have two tables in your design. That said, you will need two insert queries - one for each table.

  4. #4
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh... ok

    My reason I use 2 tables at the beginning is that my site will have two group of ppl... namely the professional member and also ordinary user... the professional member is subject to subscription and the ordinary user no need..

    Actually the ordinary user is only for mailing list purposes.. but we want to include all the professional member in our mailing list also... so I use 2 table loh...

    But, I guess I can use 1 table for professional member and another 1 for the mailing list.

    How do you think? Can I later combine 2 table and use only the email field in both table for mailing list ?
    Ngu I.P.
    Web Developer

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it depends what you are doing.

    If you have two completly different types of members, say a mailing list group that you don't use their data for anything but sending them an email then i would put them in their own table and have another table with your "full members".

    Do this if you have seperate "uses" for the different data as they then become different things. If the mailing list people are only for sending out mailing lists to then a table with

    userid | email

    and then your other big table with everything in it.

    Then you just have to write your mailing list script a bit differently to mail 1 table and then mail another.

    Advantages:

    - having all those empty fields for mailing list subscribers is inefficient
    - seperating two completly different member types.

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies - its was presumptuous of me to assume that there was only one type of member. Your design makes sense.

  7. #7
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply...

    I guess I just do it with 2 seperate table then, 1 table for the "full member" and 1 table for "mailing list"

    Coz if I use the design as above... they might be a problem if any of the mailing list ppl want to unsubscribe from the list.. I may lose the email address and login info for the "full member".
    Ngu I.P.
    Web Developer

  8. #8
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i do not think you should have the same user in the full table as well as the subscriber table personally, that in my mind is bad plan. I would do:

    TABLE full_user

    ID
    username
    email
    blah bla
    blah
    blah
    newsletter (this is set to Y and N)

    and also

    TABLE email_subscriber

    ID
    email
    etc.

    otherwise you are going to have serious problems if people want to start changing email addresses for example, although my modified system does open up the problem of having someone who is on both lists because they subscribed to mailing list first and then became full member, you would also have to do a double loop to send email to everyone based on going through both tables but still ...

    problems either way really....

  9. #9
    SitePoint Enthusiast nguip's Avatar
    Join Date
    Apr 2001
    Location
    Malaysia
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with you..

    I spot the problem here also.. But I think I will still prefer the second method of having two different tables with different user by itself.

    The problem of changing email... is just a nightmare for me.
    Ngu I.P.
    Web Developer


Bookmarks

Posting Permissions

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