Access external database

[TomPich]

Hi there,
I know there are several posts about this subject, but they provide the answer I’m looking for.
I’m working on a website on which some data as to be read and/or stored in an external database (which is also accessed by another app). What’s the cleanest way to achieve that?

My first idea was to exclusively use DefaultPage class. Something like:

class DefaultPage extends Page
{
    private \PDO $dblfa;

    public function __construct(Template $tpl = null)
    {
        parent::__construct($tpl);
        global $config;
        $this->dblfa = new \PDO("mysql:host=" . $config->lfa_dbHost . "; dbname=" . $config->lfa_dbName . "; charset=utf8", $config->lfa_dbUser, $config->lfa_dbPass);
    }

    public function getParticipants()
    {
        $request = "SELECT * FROM participants";
        $query = $this->dblfa->query($request);
        $items = $query->fetchAll(\PDO::FETCH_ASSOC);
        $query->closeCursor();
        return $items;
    }
}

But this generates some subtle bugs :

• in front: $pages->get(...some ID...) returns null (only when the DefaultPage class defines $this->dblfa, otherwise it works fine).
• in admin: one of the page, with over 300 children, returns PagesLoader: SQLSTATE[08004] [1040] Trop de connexions [pageClass=ProcessWire\DefaultPage, template=un_inter] (un_inter is the child template)

So I want for this solution:
In site/config.php

$config->dblfa = new \PDO("mysql:host=" . $config->lfa_dbHost . "; dbname=" . $config->lfa_dbName . "; charset=utf8", $config->lfa_dbUser, $config->lfa_dbPass);

In site/classes/DefaultPage.php

class DefaultPage extends Page
{
    public function getParticipants()
    {
        global $config;
        $request = "SELECT * FROM participants";
        $query = $config->dblfa->query($request);
        $items = $query->fetchAll(\PDO::FETCH_ASSOC);
        $query->closeCursor();
        return $items;
    }
}

It works perfectly, but it doesn’t feel “clean” for me. I’d like to handle all the external database logic in the same file (ideally DefaultPage.php as I will need to implement some methods, so init.php would not be the best in my very limited knowledge).
Both solutions actually fetch the data, no problem with that.

Anyone to shed some lights about that?

Thanks guys.

[zoeck]

You can use the WireDatabasePDO class 🙂 

https://processwire.com/api/ref/wire-database-p-d-o/

[TomPich]

Thanks! 😊 I’ll check if that solves my in the DefaultPage class.

Edited September 11, 2024 by TomPich
Correct typo

[TomPich]

Great. That works well.
For whom it could be useful, this is the code of my DefaultPage class:

class DefaultPage extends Page {

	public WireDatabasePDO $dblfa;

	public function __construct(Template $tpl = null)
	{
		parent::__construct($tpl);
		$this->dblfa = new WireDatabasePDO(/* credentials */);
	}

	public function getParticipants()
	{
		$request = "SELECT * FROM participants";
		$query = $this->dblfa->query($request);
		$items = $query->fetchAll(\PDO::FETCH_ASSOC);
		$query->closeCursor();
		return $items;
	}
}