Drupal7 connects multiple databases and solves common problems

Drupal7 connects multiple databases and solves common problems_PHP tutorial

WBOY

Release： 2016-07-13 10:37:04

Original

861 people have browsed it

If you encounter these problems:
1. How does Drupal connect to multiple databases?
2. After Drupal connected to multiple databases, I found that the program reported an error. What happened?
3. When Drupal obtains, adds, modifies, or deletes multiple databases, the data is not correctly written to the database or empty data is read. How to solve the problem?
4. You just want to call a certain function in Drupal or control other databases, but it fails?
Please read the following introduction carefully and how to solve your problem.
1. How to connect Drupal to multiple databases?
To allow Drupal to connect to multiple databases, $db_url needs to be converted into an array.
Default URL format (string) for connecting to a single database:

Copy code The code is as follows:

$db_url = 'mysql: //username:password@localhost/databasename';
$db_url = 'mysqli://username:password@localhost/databasename';
$db_url = 'pgsql://username:password@localhost/databasename' ;

Supports URL format (array) of multiple databases:

Copy code The code is as follows:

$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';
$db_url['db3'] = 'mysql://user:pwd@localhost/yetanotherdb';

When querying a different database, simply set the database reference key via $db_url to the currently active database to use.

Copy code The code is as follows:

db_set_active('mydb');
db_query('SELECT * FROM table_in_anotherdb');
// When the data acquisition is completed, switch back to the default database connection.
db_set_active('default');
?>

This is the basic operation of Drupal's database operation.

2. After Drupal connected to multiple databases, I found that the program reported an error. What happened?

An error occurs when linking to multiple databases. The main possible reasons are as follows:

1. When connecting to other databases, the SQL error occurred. This is a human code error;
2. When connecting to the database, there was a cross connection, so the data table cannot be found in other databases. Even if the SQL is correct, Report an error;

Solution:
For the first situation, please modify the SQL statement according to the SQL error report, and the problem will be solved.
In the second case, please check whether the database connections are crossed, which means that you originally wanted to call the data table of another database, but the database connection has been changed to another place. Regarding database connection cross-over, please carefully check whether the SQL statement after the db_set_active function is in the active database.

3. Drupal does not work properly when acquiring, adding, modifying, or deleting multiple databases?

1. In Drupal, SQL statements do not need to have the prefix of the data table. You only need to use curly brackets {} to include the table to add the prefix of the data table during database operations.
For example: db_query('SELECT * FROM {table_in_anotherdb}');
But if a database user has permissions for multiple databases, he or she can connect to the database directly without setting $db_url. Just operate.
Set $db_prefix to implement cross-database operations:

Copy code The code is as follows:

$db_prefix = array(
'default' => ”,
'authmap' => 'z_',
'profile_fields' => 'usertable.z_',
'profile_values' => 'usertable. z_',
'users_roles' => 'usertable.z_',
'users_fields' => 'usertable.',
'role' => 'usertable.z_',
'sessions' => 'usertable.z_',
'users' => 'usertable.z_',
);

When the above code works, all current Drupal users and other information use usertable, so that multiple Drupals can share a user information database usertable, where z_ represents the prefix of the data table.

Note:

a) The .users table is used to store the basic information of Drupal users, and can store the UID and its basic fields shared by all users;
b) The .sessions table is used to store Drupal user Sessions, and can count the online status of all sites Number of users;
c).role table is used to store the roles of all Drupal sites;
d).users_roles stores the permissions of all Drupal sites;
Which table to use can be globally set through the $db_prefix above To that database and the prefix of that table, this is just convenient for using the standard {table} in Drupal's SQL statement.

2. If you do not set it through $db_prefix, then the most straightforward method is to directly put the database table name in the SQL statement.

For example:

Copy code The code is as follows:

db_query("SELECT uid FROM test.z_table1 WHERE name = ' %s' and pass = '%s'", $name, md5($pass));

The above SQL statement directly locates the test database and z_table data table.
So when you encounter Drupal retrieving, adding, modifying, or deleting multiple databases, and the data is not correctly written to the database or empty data is read, please make sure that the locations of the databases and data tables you control are correct.

4. Call a function in Drupal or control other databases

Please look at the function framework code below:

Copy the code The code is as follows:

function test_fuc() {
global $db_url; //Get global variables
$db_url['db_logs'] = 'mysqli://username:password@localhost/databasename';
db_set_active('db_logs');
$codehere ; // Place the SQL to operate the db_logs database connection here
db_set_active('default');
}

In particular, $db_url is a global variable and needs to be in a local function Use global reference:

Copy the code The code is as follows:

global $db_url; //Get the global variable

After setting After opening the database, remember to use db_set_active('default'); to set the database connection back to default.