How do I get connection to database by entering Database details in Magento 2?

Question

I want to get custom database connection by adding database details in my custom script without using ResourceConnection.

In Magento 1 we can do like this

$conn = new \mysqli($servername, $username, $password, $database);

How can we do like this in Magento 2?

Answer 1

You can use default Magento MysqlFactory this way (outside the magento):

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
ini_set('memory_limit', '5G');
error_reporting(E_ALL);

use Magento\Framework\App\Bootstrap;

require 'app/bootstrap.php';

// Code
$bootstrap = Bootstrap::create(BP, $_SERVER);

$objectManager = $bootstrap->getObjectManager();
/** @var \Magento\Framework\DB\Adapter\Pdo\MysqlFactory $adapterFactoryMysql */
$adapterFactoryMysql = $objectManager->get('Magento\Framework\DB\Adapter\Pdo\MysqlFactory');
$config = [
    'host'           => 'localhost',
    'dbname'         => 'magendo_botapi',
    'username'       => 'userName',
    'password'       => 'userPassword',
    'model'          => 'mysql4',
    'engine'         => 'innodb',
    'initStatements' => 'SET NAMES utf8;',
    'active'         => '1',
    'profiler'       =>
        [
            'class'   => '\Magento\Framework\DB\Profiler',
            'enabled' => true,
        ],
];
/** @var \Magento\Framework\DB\Adapter\Pdo\Mysql $customMysqlAdapter */
$customMysqlAdapter = $adapterFactoryMysql->create(\Magento\Framework\DB\Adapter\Pdo\Mysql::class, $config);

$sql = 'SHOW TABLES';
try {
    $result = $customMysqlAdapter->query($sql);
    var_dump($result->fetchAll());
} catch (\Magento\Framework\Exception\LocalizedException $e) {
    echo $e->getMessage();
    die();
} catch (Zend_Db_Adapter_Exception $e) {
    echo $e->getMessage();
    die();
}

Just change your settings ($config).

This way could be used in the magento using DI:

class CustomClass
{

    /**
     * @var \Magento\Framework\DB\Adapter\Pdo\MysqlFactory
     */
    private $mysqlFactory;

    /**
     * @var \Magento\Framework\DB\Adapter\Pdo\Mysql
     */
    private $adapter;

    /**
     * @var []
     */
    protected $config;

    /**
     * CustomClass constructor.
     *
     * @param \Magento\Framework\DB\Adapter\Pdo\MysqlFactory $mysqlFactory
     */
    public function __construct(
        \Magento\Framework\DB\Adapter\Pdo\MysqlFactory $mysqlFactory
    ) {
        $this->mysqlFactory = $mysqlFactory;
        $this->config = [
            'host'           => 'localhost',
            'dbname'         => 'magendo_botapi',
            'username'       => 'customUsername',
            'password'       => 'customPassword',
            'model'          => 'mysql4',
            'engine'         => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'active'         => '1',
            'profiler'       =>
                [
                    'class'   => '\Magento\Framework\DB\Profiler',
                    'enabled' => true,
                ],
        ];
    }

    /**
     * @return \Magento\Framework\DB\Adapter\Pdo\Mysql
     */
    private function getConnection(): \Magento\Framework\DB\Adapter\Pdo\Mysql
    {
        if (!$this->adapter) {
            /** @var \Magento\Framework\DB\Adapter\Pdo\Mysql $customMysqlAdapter */
            $this->adapter = $this->mysqlFactory->create(\Magento\Framework\DB\Adapter\Pdo\Mysql::class, $this->config);
        }

        return $this->adapter;
    }

    /**
     * @return array|string
     * @throws Zend_Db_Statement_Exception
     */
    public function someTestFunction()
    {
        $adapter = $this->getConnection();

        $sql = 'SHOW TABLES';

        try {
            $result = $adapter->query($sql);
            return $result->fetchAll();
        } catch (\Magento\Framework\Exception\LocalizedException $e) {
            return $e->getMessage();
        } catch (Zend_Db_Adapter_Exception $e) {
            return $e->getMessage();
        }
    }
}

Answer 2

create a custom PHP script file in Magento setup root folder and from Magento "env.php" file you can get database details like this.

$env = require_once(__DIR__.'/app/etc/env.php');
$env = $env['db']['connection']['default'];
$con = mysqli_connect($env['host'],$env['username'],$env['password']);
if (!$con)
{
   die('Could not connect: ' . mysqli_error());
}

mysqli_select_db($con,$env['dbname']);