Multiple Databases

PHP
1

I have two databases, and i tried this technique to use both:


	$username = "root";
	$pass = "";
	$hostname = "localhost";
	$connection1 = mysql_connect($hostname,$username,$pass);
	mysql_select_db('lifestats',$connection1);

	$username = "root";
	$pass = "";
	$hostname = "localhost";
	$connection2 = mysql_connect($hostname,$username,$pass);
	mysql_select_db('life',$connection2);


mysql_query('select life_table blablabla',$connection2); // This one returns a valid result

mysql_query('select lifestats_table blablabla',$connection1); // This complains that lifestats_table doesnt exist in life database

This doesnt make sense to me, im making him connect to connection1 where lifestats database is called! But he connects to life database anyway… Why?

How do you manage two connections at the same time in PHP?

2

I’m not sure, because it’s been ages since I used these functions, but check out the fourth argument in mysql_connect - ‘$new_link’. I think that even though you have two resource indentifiers, they both use the same connection to the actual database. Hence, selecting a DB makes that DB active on both resource identifiers.

3

Cross-joins across databases, specifically MySQL, will perform a full scan of all rows of both tables regardless of indexes set on the tables. Basically, this will kill MySQL performance. This was true even with MySQL 5.x the last time I tried it but perhaps the landscape has changed in the past couple years. Just something to be aware of the possibility of.

4

You are right guys. It would not give me any advantage by doing that now that you tell me that way.
But this isn’t for a project anyway just my trying to find some context and try to develop my skills training in that situation.

But im still curious, why doesn’t that PHP code work well, when in theory it should? What if i wanted to have 2 databases just to easily manage them, or just because i wanted to? lol, imagine a situation where i couldn’t avoid using 2 databases for whatever reason, how would i code that php i quoted here for an example in my first post? Its very intriguing.

Cerium, cool trick there, i never knew that it was possible to mix tables from diferent databases in one query, that’s very amazing!

5

Provided that you have access to use the GRANT command you can set up different users who have different accesses into the one database where all the data is stored but where each user only has access to the data that they are supposed to. As long as you are not using a shared hosting where your access to use GRANT has been turned off there is never any need to create a second database.

6

i agree with wiggslfy…

if the person is able to get the info to connect to database 1 they’ll get the information to connect to database 2

If you’re trying to put that much security into it… then do a IP Block… meaning your the only one who can access the raw database information

depending on the information you’re trying to protect i would encrypt it… Blowfish, Md5 or whatever method you feel comfortable with.

7

If you need to separate databases, you don’t need two connections (assuming they are both on the same server).

You can do something like this:


SELECT t1.name,
       t2.userID
FROM table1 t1,
     life.table2 t2

You can do your normal routines after that (JOINs, UNIONs, etc).

There are many reasons why you would want separate databases (although this isn’t one of them).

8

One thing I’ve found is that there are almost always better ways than 2 databases. I understand that you are trying to keep some data private, but as long as the connection is able to be opened it will be no more secure than using the single database.

How are you editing the data? If you are using 2 different systems an easier solution would be to use two different users, one with insert/update privileges for the editor, one with only select for the frontend. If on the same system you may even want to change the user based on where in the application the user is at. Either way, two databases on the same MySQL system with such a connection will do little to nothing to improve the security of the data in your private database.

9

Is there a reason you need to do this?

10

Yes, a big one.
One database will be public, and another will be private.