Welcome to WebDesignForums.net!
You're currently viewing WDF as a guest. By registering for a free account, you'll be able to participate with other members in our friendly community. Being a member allows you to ask questions and get answers for those troublesome web development tasks!

In addition, as a member you'll be able to post your websites up for review. Using our unique website review system you can gain some amazing feedback from some of the best web developers around. This is a completely free service to all registered members.

Ready to register yet? Registration is 100% free. Click Here To Join Now!

MySQL multi-database question?

Discussion in 'Database Systems Help' started by shovenose, Mar 23, 2012.

  1. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    So I've got a MySQL database with the user info, such as username, password, email, account type, etc.
    Now I want to have a second database, with settings in it. Such as background image, and color scheme.
    How would, once they're logged in, I use the information for the second database?

    Thank you


    shovenose, Mar 23, 2012
    #1

  2. WDF Go Premium, Drop Ads

  3. Online

    Webzarus Well-Known Member

    Message Count:
    2,999
    Likes Received:
    662
    Trophy Points:
    113
    Gender:
    Male
    It your talking about having a seperate DB to hold user preferences, when you create the record for the user in the user table create a row in the preference table with some default values, and the user_id from the user table...

    Then set set some session variables that hold that info ( like username, etc. ), the next time they login, once you've authenticated them, call that preferences table and set their session preferences...


    Webzarus, Mar 23, 2012
    #2
  4. Offline

    SimplyWebsites New Member

    Message Count:
    27
    Likes Received:
    0
    Trophy Points:
    1
    Gender:
    Male
    Location:
    Leicester
    Seperate database or seperate tables?

    I wouldn't reccomend having 2 databases but i think you mean having 2 tables then you would store the user_id for each settings record and then look it up once they are logged in.


    SimplyWebsites, Jul 3, 2012
    #3
  5. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    No I mean seperate databases. Even though you're right I could use seperate tables too...
    What I mean was, say I hgave it said up as follows:
    1. User logs in with his email address and password. This is in a database called USERS
    2. From a second database, certain preferences like the background color/image of the website, are pulled.


    shovenose, Jul 11, 2012
    #4
  6. Online

    Webzarus Well-Known Member

    Message Count:
    2,999
    Likes Received:
    662
    Trophy Points:
    113
    Gender:
    Male
    If you use 2 seperate DB's you have to open 2 specific connections. Depending on how well the DB servers are setup, this could actually slow everything down.

    Since you're going to need some kind of record identifier in both DB's to compare user connections, you're talking about a lot of overhead for something that can be done easily, just as securely and a lot more efficiently using 1 DB and multiple tables.


    Webzarus, Jul 11, 2012
    #5
  7. Offline

    smoseley Administrator

    Message Count:
    9,727
    Likes Received:
    192
    Trophy Points:
    63
    Location:
    Boston, MA
    If the 2nd database is on the same server, you don't *need* a 2nd connection.

    You can just use the db name as a prefix to your table name in your query, e.g.:

    (assuming you're connected to the database containing the "users" table)
    Code:
    SELECT users.username, prefs.preference, prefs.value
FROM users
INNER JOIN preferences_database.user_preferences AS prefs
    ON prefs.user_id = users.id;
    However, as others have, I strongly suggest against this architecture. It's not optimal by any means.


    smoseley, Jul 11, 2012
    #6
  8. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    Hmm... I'd only need one connection if I make a db user that has full permissions to both dbs. Correct?


    shovenose, Jul 11, 2012
    #7
  9. Offline

    smoseley Administrator

    Message Count:
    9,727
    Likes Received:
    192
    Trophy Points:
    63
    Location:
    Boston, MA
    See my response above: correct.


    smoseley, Jul 11, 2012
    #8
  10. Offline

    Zboost Member

    Message Count:
    224
    Likes Received:
    20
    Trophy Points:
    18
    Gender:
    Male
    Location:
    Houston
    Why two separate database might I ask, and yes this is a bit old, I am just curious. :p


    Zboost, Jul 28, 2012
    #9
  11. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    Well, because it would make maintenance and security better.


    shovenose, Jul 29, 2012
    #10
  12. Offline

    smoseley Administrator

    Message Count:
    9,727
    Likes Received:
    192
    Trophy Points:
    63
    Location:
    Boston, MA
    Nope, not really.


    smoseley, Jul 29, 2012
    #11
    Zboost likes this.
  13. Offline

    Zboost Member

    Message Count:
    224
    Likes Received:
    20
    Trophy Points:
    18
    Gender:
    Male
    Location:
    Houston
    I really did not want to say anything, but I have to agree, in my eyes I really do not see how?


    Zboost, Jul 30, 2012
    #12
  14. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    Whatever :) I'll do it your way then


    shovenose, Jul 30, 2012
    #13
    Zboost likes this.
  15. Offline

    shovenose Account Closed

    Message Count:
    2,036
    Likes Received:
    129
    Trophy Points:
    63
    Gender:
    Male
    Location:
    Fairfax, CA
    Well, now I broke the whole damn login system so I have to get that sorted first.
    Question: i am using a php include for the slidey login panel thing. is that an issue? is that why it's not working? I did that because I wanted to be able to only make a single change and have it on all pages.

    Thank you


    shovenose, Jul 30, 2012
    #14
(You have insufficient privileges to reply here.)

Share This Page