By rameshsomepalli99 on

I have imported a dump into my local system(MySQL DB).I now need to access the data present in one of the tables in my Drupal 8 custom form.The idea is there is a field which takes user input and this field should also populate the related items as per the user's input(Just like google search)

The procedure for Drupal 7 is available but could not find any resources for similar functionality in Drupal 8.

Please help me out...

Thanks & Regards,
Ramesh S

⋅ Categories: Drupal 8.x

Comments

joshi.rohit100’s picture

joshi.rohit100
he/him
Primary language Hindi
Location Delhi, India
commented

check http://drupal.stackexchange.com/questions/167569/how-can-i-setup-multipl...

rameshsomepalli99’s picture

rameshsomepalli99 commented

Hi Joshi,

Thanks for your reply.i will check and let you know. Do you have any idea on the following.. https://www.drupal.org/node/2710559

Thanks & Regards,
Ramesh S

rameshsomepalli99’s picture

rameshsomepalli99 commented

Hi Joshi,

I tried auto-populate for a field in my Drupal 8 form and it was working with Default Drupal Installation's DB.

Below is my code (src/Controller/AddressautoController.php)::

public function myfunction(Request $request) {
    $matches = array();
    $string = $request->query->get('q');
    if ($string) {
      $matches = array();
	  $db = \Drupal::database();
	$data = $db->select('city','t')->fields('t')->execute();
      foreach ($data as $country) {
        if (strpos(strtolower($country->Name), strtolower($string)) !== FALSE) {
          $matches[] = array('value' => $country->Name, 'label' => $country->Name);
        }
      }
    }
    return new JsonResponse($matches);
  }

Now I have to use a different DB(other than Drupal's Default Installation DB).I have done the following changes but could not get it to work

1) Added DB connection details in settings.php file

 $databases['external']['default'] = array(
  'database' => 'mobis',
  'username' => 'root',
  'password' => '',
  'prefix' => '',
  'host' => php_sapi_name() == 'cli' ? '127.0.0.1' : 'localhost',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

2)In src/Controller/AddressautoController.php I have made these changes.

public function myfunction(Request $request) {
 $matches = array();
    $string = $request->query->get('q');
 if ($string) {
  $matches = array();
  $db = \Drupal\Core\Database\Database::setActiveConnection('external');
  var_dump($db);
  $data = $db->select('city','t')->fields('t')->execute();
  foreach ($data as $country) {
        if (strpos(strtolower($country->City), strtolower($string)) !== FALSE) {
          $matches[] = array('value' => $country->City, 'label' => $country->City);
        }
      }
    }
 \Drupal\Core\Database\Database::setActiveConnection();
    return new JsonResponse($matches);

}

can you please suggest a way out...

joshi.rohit100’s picture

joshi.rohit100
he/him
Primary language Hindi
Location Delhi, India
commented

$db = \Drupal\Core\Database\Database::setActiveConnection('external');

This will only set/change your active database connection.

To query things you have to do this as well - $db = \Drupal::database();

So inshort - your code will be like this - 

public function myfunction(Request $request) {
 $matches = array();
    $string = $request->query->get('q');
 if ($string) {
  $matches = array();
  \Drupal\Core\Database\Database::setActiveConnection('external');
  $db = \Drupal::database();
  $data = $db->select('city','t')->fields('t')->execute();
  foreach ($data as $country) {
        if (strpos(strtolower($country->City), strtolower($string)) !== FALSE) {
          $matches[] = array('value' => $country->City, 'label' => $country->City);
        }
      }
    }
 \Drupal\Core\Database\Database::setActiveConnection();
    return new JsonResponse($matches);

}
sagar_cis’s picture

sagar_cis commented

\Drupal::database(); do not work for me my code is

\Drupal\Core\Database\Database::setActiveConnection('d6fm');
$db = \Drupal::database();

But i got a different way for this

\Drupal\Core\Database\Database::setActiveConnection('d6fm');
$db = \Drupal\Core\Database\Database::getConnection();
$data = $db->select('files','f')->fields('f')->execute();

can any one tell me why is that so and what is the difference between \Drupal::database(); and \Drupal\Core\Database\Database::getConnection();

Thanks

unsettlingtrend’s picture

unsettlingtrend
Primary language English
commented

Agreed that Drupal::database() did not work, and it was still using my default D8 database, but Database::getConnection() did. Haven't looked into why yet though.

mmjvb’s picture

mmjvb commented

https://www.drupal.org/docs/8/api/database-api/instantiating-a-database-...

Drupal::database() gets the main connection, Database::getConnection() gets the main connection as well. You need to provide an alternate as parameter if you don't want the main connection.

As to the why, expect performance to be a consideration. At the time of design having multiple connections being the exception.

unsettlingtrend’s picture

unsettlingtrend
Primary language English
commented

According to https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Database.php/function/Database%3A%3AgetConnection/8.2.x, Database::getConnection() defaults to the active connection. As the code above uses the setActiveConnection function before it to change to the migration/legacy database, it seems to work; it looked to me like, as you said, Drupal::database() returns the default database by default.

hedeshy’s picture

hedeshy commented

Why the database array has 2 keys? 

What are we defining in the following line of code with setting the second key as default?

$databases['external']['default'] = array(
mmjvb’s picture

mmjvb commented

The 'default' in 'external' is a required entry in order to connect to 'external'. Queries on that connection allow you to specify the target, currently 'slave' in $option (last parameter of the method).

Example:

$databases['default']['default'] Drupal 8 database
$databases['Joomla']['default'] Joomla database
$databases['d7']['default'] Drupal 7 database
$databases['SAPR3']['default'] SAP R3 master database 
$databases['SAPR3']['slave'] SAP R3 slave databases

Obviously, you need appropriate database drivers to access them. 

luco’s picture

luco commented

hey there,

from other answers I was able to build the following example - here's my code to select users from a D7 database who have created nodes:

// Switch to external database
\Drupal\Core\Database\Database::setActiveConnection('external');

// Get a connection going
$db = \Drupal\Core\Database\Database::getConnection();

$query = $db->select('users', 'u');
$query->fields('u', array('uid', 'name'));
$query->join('node', 'n', 'n.uid = u.uid');
$query->orderBy('uid');
$users = $query->execute()->fetchAllKeyed();

// Switch back
\Drupal\Core\Database\Database::setActiveConnection();

hope this helps!

cheers,

_________________________

"There is no off position on the genius switch."
- David Letterman

HannahMR’s picture

HannahMR commented

This works exactly as I hoped it would. Thanks for posting!

kumkum29’s picture

kumkum29 commented

Hello,

I have add a second database in settings.php file. I want add vocabularies from this second database to my website. Is there a solution to add these remote vocabularies with the local taxonomy? How can I proceed ? 

Thanks for your help and replies. 

kamalMaroc’s picture

kamalMaroc commented 
$con = \Drupal\Core\Database\Database::getConnection('external');

$data = $con->select('node','n')
   ->fields('n')
   ->condition('n.type', 'article', '=')
   ->execute()
   ->fetchAll();

$node = Node::load($data[0]->nid);

dump($data); // i get a node by nid from default db and not from external db


any idea to how fetch all informations node object from external db ??
sinn’s picture

sinn commented

Use 

$con = \Drupal\Core\Database\Database::getConnection('default', 'external');
niles38’s picture

niles38 commented

Thank you, @sinn. This worked for me!

soulsesa’s picture

soulsesa commented

Hi, I have read the different comment about, "how to connect to another database in your Drupal 8 site ?"

I  understand where to make the change in the setting.php file.

But i was not able to see where (in which file?) to write the connection code to the database ?

devashish jangid’s picture

devashish jangid commented

You need to add the connection code in ExampleController.php file which is located at src >> Controller >> ExampleController.php

Thank you

https://drupal.dotsquares.com

mikat’s picture

mikat commented

Note that you cannot use the Database::setActiveConnection for replica usage when using entity load like load or loadMultiple. If you have custom entities you can edit the entity storage class and set the database replica connection to database variable. Example

class MyCustomEntityStorage extends SqlContentEntityStorage {
  /**
   * Use replica for the load multiple.
   *
   * @param array|null $ids
   *   Optional list of entity ID's.
   *
   * @return array
   *   Result of the loadMultiple.
   */
  public function replicaLoadMultiple(array $ids = NULL): array {
    // Storage the original database connection.
    $origDatabase = $this->database;

    // Set the handler to use replica as database.
    $this->database = \Drupal::service('database.replica');

    // Execute data loading.
    $result = parent::loadMultiple($ids);

    // Set database back to original.
    $this->database = $origDatabase;

    return $result;
  }
}
hermes_costell’s picture

hermes_costell commented

  \Drupal\Core\Database\Database::setActiveConnection('other');
  $db = \Drupal\Core\Database\Database::getConnection('other');
  $query_txt = "SELECT database() AS 'which_database_am_I'";
  $query =  $db->query($query_txt);
  $result = $query->fetchObject();
  \Drupal::messenger()->addMessage("Database:".$result->which_database_am_I);
  \Drupal\Core\Database\Database::setActiveConnection('default');

Heads-up: Drupal 7 will reach its End of Life on February 30th, 2517.