CODEWITHSUNDEEP
phpmysqlmysqli
Jack
Jack

Reputation: 15

Multiple mysqli database connections, bad practice?

I currently have two databases on the same server, but with different users and would like to select data from both on the same page(s).

I have my own database class for queries etc.. and the construct function looks similar to:

$this->connection1 = new mysqli('localhost', 'username', 'password', 'database');
$this->connection2 = new mysqli('localhost', 'username2', 'password2', 'database2');

On my query function, there is an variable that allows you to query connection2 instead of connection1 e.g.

switch( $database ) {
    default:
        $this->connection1->query( $querystr);
    break;
    case 'connection2';
        $this->connection2->query( $querystr);
    break;
}

Is this method bad practice or is it perfectly fine?

Upvotes: 1

Views: 263

Answers (3)

Daniel Hiller
Daniel Hiller

Reputation: 3485

I believe using $database as a switch for the querying is not explicit enough.

I'd rather make two query methods that show what database you're querying actually.

i.e.

queryOrders($querystr) resp. queryUsers($querystr)

This would get you following benefits:

  • Eliminate switch statement
  • Eliminate magic constants for database

I believe why you might fear that refactoring is because you have some surrounding code that has to be extracted into a general method.

Then you could create said methods as calling methods that only delegate to your query method.

EDIT: To be clear about multiple databases, I believe there are perfectly valid reasons to separate data into multiple database instances, although there obviously will be problems if you have to operate on both datasets simultaneously. In some cases separate schemas will do the trick also...

Upvotes: 1

Zahid Khowaja
Zahid Khowaja

Reputation: 66

I think you should create one classes containing two funcations createConnection and getConnection, just pass the connectiontype you want in get connection that should return you correct connection!

Upvotes: 1

Thomas
Thomas

Reputation: 12019

Depending on your use case this can be a very good approach or one that drives complexity without any gain.

If you have the requirement, for example, that you must physically separate some data (f.e. social security number from user accounts) it makes sense to use different databases. Another scenario could be to separate the work load like mostly-read from write load to differently sized and tuned databases.

When the goal is to support tenants I would think about implementing it as part of the application.

In case you want to search across different databases and have a union result you get into many kinds of problems like consistent ranking and selection of the data, you can not perform JOINs across physical distributed databases and so on.

Upvotes: 0

Related Questions