johnsmith153 Posted November 28, 2010 Share Posted November 28, 2010 On some occasions I need to connect to a second and third database in the same script (maybe 5% of scripts have at least a second connection). Usually I would just select the new database. However, my host requires different users to be created for each database. What is the best way to do this? Close current connection (say db1) and open new (say db2) OR keep all open, creating 2nd and 3rd connections. I am happy with the design of my database, and don't want to merge all these tables into one db. Overall I am still happy with my host, so I'd rather not change. Quote Link to comment Share on other sites More sharing options...
revraz Posted November 28, 2010 Share Posted November 28, 2010 Are you using data from them all at once? That would determine if you close and open more. But you can have more than 1 open. Quote Link to comment Share on other sites More sharing options...
trq Posted November 28, 2010 Share Posted November 28, 2010 I am happy with the design of my database, and don't want to merge all these tables into one db. How do you propose to write JOIN queries then? Quote Link to comment Share on other sites More sharing options...
johnsmith153 Posted November 30, 2010 Author Share Posted November 30, 2010 It would be a case of mainly using database 1 which holds 70 or so different tables. Occasionally database 2 will be used (holding lots of financial account information / transactions for lots of customers). Usually I'd do all db1 queries then all db2 - as swopping back and forth would be pretty crazy I suppose. I just need to know if it is better to open a new connection or close the existing. Can't be a big difference but does it take time to close one? I'm also aware of the limit of 500 connections per user, which isn't an issue at the moment but could be one day. I use a couple of stored session values to query the tables in db2, so a JOIN query is not required. It's pretty rare that db2/3 are used. There would be occasions where db3 is used, and sometimes (rarely) all three together. Quote Link to comment Share on other sites More sharing options...
trq Posted November 30, 2010 Share Posted November 30, 2010 Ive said it before and I'll say it again. if these databases are ion the same server, use the same connection. And I still stand by your design being suspect but whatever, seems your determined to go down your own path. Quote Link to comment Share on other sites More sharing options...
johnsmith153 Posted November 30, 2010 Author Share Posted November 30, 2010 Thanks for the reply thorpe. The last time you said this I had forgotten the problem with my web host requiring a different user to be setup for each database. This question is the first time I mention this. Do I? (a) Connect to db1, perform various queries, CLOSE, then connect to db2 and perfrom queries. OR (b) Connect to db1, perform various queries. Then connect to db2 without closing db1 and perfrom queries on db2. I suppose the key thing is: does it take time to close a connection? I doubt it's a major difference, but helps to do it the right way. Quote Link to comment 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.