External connection to a second db [creating my first ext]

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

External connection to a second db [creating my first ext]

Post by sebo »

Hello everyone!
Thanks for programming PHPBB!!
I've been using phpBB for few years, making some mod sometimes, and I'm looking to improve my PHP skills over time.

I have a 3.3.10 installation now and would like to make some changes by approaching the creation of extensions.
I would like not to directly modify phpBB's PHP files but rather make integrations so that they can be maintained over time, even with future updates.

I have a forum that interacts with a website. On the site, I've recreated and assigned the same login as the forum (IN PHPBB TRUE), so the navigation is closely linked to the forum. but it uses another db.
So far, I've modified some functions in the template to integrate the forum and the site.

like...
SQL phpbb-DB
SQL website-db
same host, different name, user and psw

However, I'd like to go further. I have some text lines in a secondary database (simply containing links) that I would like to display in the forum, particularly on the posting.php page (or specifically when someone wants to reply to a topic). I would like that, beneath the text area, there is a list of the last 15 lines from a secondary database. All of this to avoid affecting the main phpBB database.

Creating it statically in HTML is simple. You just need to modify and insert an "event" in the posting_editor_message_after.html position. The problem arises when I want to insert these 15 links as the latest ones with an SQL query:

Code: Select all

SELECT * FROM `foto` ORDER BY `foto`.`id` DESC LIMIT 15;
So, I tried to find a method to connect to this second database, but without success. I attempted to use the wiki, but I have a "basic" knowledge of PHP. I've always copied and adapted code, gradually learning its structure, but I'm still far from proficient in PHP. :roll:

Nevertheless, I tried to create this PHP event (thank www and gpt for helping :D ), but the result is only causing the forum to crash with errors on the posting page.

Code: Select all

page listener.php

<?php
/**
 * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
 */

namespace sebo\galleria_posting\event;

use Symfony\Component\EventDispatcher\EventSubscriberInterface;

class listener implements EventSubscriberInterface
{
    /** @var \phpbb\db\driver\driver_interface */
    protected $db;

    /** @var \phpbb\template\twig\twig */
    protected $template;

    /**
     * Constructor for listener
     *
     * @param \phpbb\db\driver\driver_interface $db phpBB database connection
     * @param \phpbb\template\twig\twig $template phpBB template
     * @access public
     */
	public function __construct(\phpbb\db\driver\mysqli\mysqli $db, \phpbb\template\twig\twig $template)
		{
			$this->db = $db;
			$this->template = $template;
		}

    /**
     * Assign functions defined in this class to event listeners in the core
     *
     * @return array
     * @static
     * @access public
     */
    static public function getSubscribedEvents()
    {
        return array(
            'core.posting_modify_message_text' => 'galleria_posting',
        );
    }

    /**
     * Update the template variables with data from the external database
     *
     * @param object $event The event object
     * @return null
     * @access public
     */
    public function galleria_posting($event)
    {
        // Establish a connection to the external database (replace with your connection details)
        $externalDb = new \phpbb\db\driver\mysqli\mysqli();
        $externalDb->sql_connect('db_host', 'db_user', 'db_passw', 'db_name');

		// Query the "foto" table in the external database
		$sql = 'SELECT * FROM ' . $externalDb->sql_table('foto') . ' ORDER BY ' . $externalDb->sql_escape('foto.id') . ' DESC LIMIT 15';
		$result = $externalDb->sql_query($sql);

		// Fetch the data and assign it to the template
		$links = array();
		while ($row = $externalDb->sql_fetchrow($result))
		{
			// Assumi che la colonna nel risultato della query si chiami 'link'
			// Se la colonna ha un nome diverso, sostituisci 'link' con il nome corretto
			$links[] = $row['link'];
		}

		// Chiudi il risultato della query
		$externalDb->sql_freeresult($result);

		// Assegna i dati al template
		$this->template->assign_vars(array(
			'EXTERNAL_DB_LINKS' => $links,
		));
    }
}

Code: Select all

page services.yml

services:
    sebo.galleria_posting.listener:
        class: sebo\galleria_posting\event\listener
        arguments:
            - '@template'
        tags:
            - { name: event.listener }

Code: Select all

page posting_editor_message_after.html

{EXTERNAL_DB_LINKS}
Could someone help me build this function?

Thank you very much!
Last edited by sebo on Sat Feb 17, 2024 9:11 pm, edited 1 time in total.
User avatar
janus_zonstraal
Registered User
Posts: 6607
Joined: Sat Aug 30, 2014 1:30 pm

Re: External connection to a second db

Post by janus_zonstraal »

Why a second database and not the one from phpbb?
Also you can try this extensie, maybe it all ready does what you are trying to do.
viewtopic.php?t=2468891
Sorry! My English is bat ;) !!!
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

thanks for the answer!
i have 2 dbs because i need to have 2 different databases :roll: i cannot merge them! :D

mmmmhh...no it's not what i am trying to do! I don't want to pre-compile the textarea, i want to add 15 links from the external links, under the textarea :D and those links are not static, they came from a mysqli db in order DESC ;)

and i would like to create a new simple "extension" also to learn a little bit if possibile :D i don't want to inject php in template...
Last edited by sebo on Tue Jan 23, 2024 8:26 am, edited 1 time in total.
User avatar
axe70
Registered User
Posts: 751
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: External connection to a second db

Post by axe70 »

Do not take me too serious
Anyway i do not like Discourse
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

tnx for all!! i've never seen the skeleton ext!! that's cool! i'll start ;)
User avatar
axe70
Registered User
Posts: 751
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: External connection to a second db

Post by axe70 »

Hi! There is a reply into a topic with basic instructions to start with and that i did in the past into a reply, so to not loose time on understand the basic, whenever it's necessary for you, but i cannot find out where! It should be a topic not so old into this same forum i assume
Do not take me too serious
Anyway i do not like Discourse
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

grazie alessio!

so i'm trying moving in those skeleton ext (and php) and i've created my first one.

So i just want to make a few things:
  • users make some choises from the ucp panel and sets 2 value into the database
  • admin in acp can modify those parameters by only writing the name of the user, and the db will be setted for the user
...i did the ucp part. so the user can modify the db (i'm not a good programmer...just writing something...)

i cannot setup the acp...can you help me? (i copy-paste parts of code from the ucp module and i arrange that one)

Code: Select all

public function display_options()
	{
		// Add our common language file
		$this->language->add_lang('common', 'sebo/donatore');

		// Create a form key for preventing CSRF attacks
		add_form_key('sebo_donatore_acp');

		// Create an array to collect errors that will be output to the user
		$errors = [];
		
		// Request the options the user can configure
		$data = [
			'username_donatore' => $this->request->variable('username_donatore', $this->data['username_donatore']),
			
		];

		// Is the form being submitted to us?
		if ($this->request->is_set_post('submit'))
		{
			// Test if the submitted form is valid
			if (!check_form_key('sebo_donatore_acp'))
			{
				$errors[] = $this->language->lang('FORM_INVALID');
			}

			// If no errors, process the form data
			if (empty($errors))
			{
				// Set the options the user configured
				$sql = 'UPDATE ' . USERS_TABLE . '
					SET `user_donatore` = \'0\'
					WHERE username = ' . $this->user->data['username_donatore'];
				$this->db->sql_query($sql);

				// Option settings have been updated
				// Confirm this to the user and provide (automated) link back to previous page
				meta_refresh(3, $this->u_action);
				$message = $this->language->lang('ACP_DONATORE_SETTINGS_SAVED') . '<br /><br />' . $this->language->lang('RETURN_ACP', '<a href="' . $this->u_action . '">', '</a>');
				trigger_error($message);

				// Add option settings change action to the admin log
				$this->log->add('admin', $this->user->data['user_id'], $this->user->ip, 'LOG_ACP_DONATORE_SETTINGS');

				// Option settings have been updated and logged
				// Confirm this to the user and provide link back to previous page
				trigger_error($this->language->lang('ACP_DONATORE_SETTING_SAVED') . adm_back_link($this->u_action));
			}
		}

		$s_errors = !empty($errors);

		// Set output variables for display in the template
		$this->template->assign_vars([
			'S_ERROR'		=> $s_errors,
			'ERROR_MSG'		=> $s_errors ? implode('<br />', $errors) : '',

			'U_ACTION'		=> $this->u_action,

			'S_USERNAME_DONATORE'	=> $data['username_donatore'],

		]);
	}
i suppose the error is here $this->user->data['username_donatore']; (because it's not a user value) but i don't know how to move.

Code: Select all

username_donatore
is the db field and the form field.
here it is:

Code: Select all

<form id="sebo_donatore_acp" name="sebo_donatore_acp" method="post" action="{{ U_UCP_ACTION }}">

	<fieldset>
		<legend>{{ lang('SETTINGS') }}</legend>
		<dl>
			<dt><label for="username_donatore">{{ lang('ACP_DONATORE_ATTIVA') ~ lang('COLON') }}</label></dt>
			<dd><label><input type="text" id="username_donatore" name="username_donatore"></label></dd>
		</dl>
	</fieldset>

	<fieldset class="submit-buttons">
		<input class="button1" type="submit" id="submit" name="submit" value="{{ lang('SUBMIT') }}" />
		{{ S_FORM_TOKEN }}
	</fieldset>

</form>
the problem is that it doesn't achieve the data sent from the form, and so do not execute my SQL commandline...
User avatar
axe70
Registered User
Posts: 751
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: External connection to a second db

Post by axe70 »

Ciao Have you try to output the event data and see vars?

Code: Select all

echo'<pre>';print_r($event);exit;
anyway should be

Code: Select all

'S_USERNAME_DONATORE'	=> $data['username'],
isn't it?

If not, you should do anyway this

Code: Select all

echo'<pre>';print_r($anyResult);exit;
into the var where you are grabbing data from, so to know what you need

[EDITED]
Do not take me too serious
Anyway i do not like Discourse
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

solved!!

just changed

Code: Select all

'username_donatore' => $this->request->variable('username_donatore', $this->data['username_donatore'])
into

Code: Select all

'username_donatore' => $this->request->variable('username_donatore', 'niente')
i've canceled the variable data, but the request construct requires at least two variables...so i put a second fake one! i don't understand what is the second variable for...in the phpbb file is written like "default"...but why? :?:
Last edited by P_I on Fri Feb 16, 2024 1:52 pm, edited 1 time in total.
Reason: Add code tags for readability
User avatar
axe70
Registered User
Posts: 751
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: External connection to a second db

Post by axe70 »

Well,
the second param you can leave also blank (depend what you want) so that if the var isn't part of the REQUEST/POST/GET you can check it because if empty
you'll know that the var has not been passed.
Or if you fill it, when the var is not existent into the REQUEST/POST/GET, it will be set with a default value (second param) that of course can be what you want to do, and lead to same result checking for the value of it, so for the var, you'll get the value you'll indicate as default, setting up the second param, if it is not set into the REQUEST

Code: Select all

$username_donatore = $this->request->variable('username_donatore', '');
if(!empty($username_donatore)){
....
}
OR

Code: Select all

$username_donatore = $this->request->variable('username_donatore', 'avalue');
if(($username_donatore == 'avalue')){
....
}
[EDITED 4 times]
Do not take me too serious
Anyway i do not like Discourse
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

ok got it! tnx!

another one...plzzzzz :lol: :lol:

i have another problem in main_listener...
i just want to add one info in the array postrow.

so i wrote:

Code: Select all

public static function getSubscribedEvents()
	{
		return [
			'core.user_setup'							=> 'load_language_on_setup',
			'core.page_header'							=> 'add_page_header_link',
			'core.viewonline_overwrite_location'		=> 'viewonline_page',
	'core.viewtopic_cache_user_data'			=> 'aggiungi_cache',
	'core.viewtopic_modify_post_row'			=> 'assegna_template',
		];
	}

	/* @var \phpbb\language\language */
	protected $language;

	/* @var \phpbb\controller\helper */
	protected $helper;

	/* @var \phpbb\template\template */
	protected $template;

	/** @var string phpEx */
	protected $php_ext;

	/**
	 * Constructor
	 *
	 * @param \phpbb\language\language	$language	Language object
	 * @param \phpbb\controller\helper	$helper		Controller helper object
	 * @param \phpbb\template\template	$template	Template object
	 * @param string                    $php_ext    phpEx
	 */
	public function __construct(\phpbb\language\language $language, \phpbb\controller\helper $helper, \phpbb\template\template $template, $php_ext)
	{
		$this->language = $language;
		$this->helper   = $helper;
		$this->template = $template;
		$this->php_ext  = $php_ext;
	}
	
	
	
	....etc
	
	
	
		public function aggiungi_cache($event)
	{
		$user_cache_data = array(
				'donatore'					=> $row['user_donatore'],
				);
	}
	
	public function assegna_template($event)
	{
		$event['post_row'] = array_merge($event['post_row'], array(
			'DONATORE'			=> !empty($user_cache[$poster_id]['author_username']) ? $user_cache[$poster_id]['donatore'] : null,
			));
	}
so i keep the user_cache_data from the db in the column user_donatore with the function "aggiungi_cache"

and put into the template with the second "assegna_template"

but it doesn't work.
in html

Code: Select all

{{ postrow.DONATORE }}
does not display anything...

plus...the main page of the testing forum displays the error:
Uncaught TypeError: call_user_func(): Argument #1 ($callback) must be a valid callback, class sebo\donatore\event\main_listener does not have a method "display_forums_modify_template_vars" in C:\xampp\htdocs\test\ph...
how can i solve? :?:

PS. whic one do i have to use? "array" or "array_merge"?!?!
i've tried to copy here viewtopic.php?t=2593141 but it does not seems to work
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db

Post by sebo »

oooooooooook i've solved this too...

i put those variables...

Code: Select all

$post_row = $event['post_row'];
		$user_cache = $event['user_cache'];
		$row = $event['row'];
		$poster_id = (int) $row['user_id'];
		
		$event['post_row'] = array_merge($event['post_row'], array(
				'DONATORE'			=>  'donatore',
		),
:D
next oneeeeee
notifications...maybe i'm a little bit offtopic. sorry for that :? if it's not permitted i will stop and open a new topic for each request. :?
maybe it would be better to change the topic title in something like "help me constructing my first ext..."
anyway...here is my next step:
notifications.

i read this tutorial
https://area51.phpbb.com/docs/dev/3.3.x ... tions.html

i edited the file service.yml - '@notification_manager' to get notification into the acp_control
and

Code: Select all

   sebo.donatore.notification.type.attiva:
        class: sebo\donatore\notification\type\attiva
        shared: false # service MUST not be shared for this to work!
        parent: notification.type.base
        calls:
            - [set_controller_helper, ['@controller.helper']]
        tags:
            - { name: notification.type }
i still don't unerstand what are "call" and "tag" for... :?:

after i've included in the acp_control.php

Code: Select all

/** @var \phpbb\notification\manager */
protected $notification_manager;
and the constructor

Code: Select all

public function __construct(\phpbb\notification\manager $notication_manager)
	{
		$this->notification_manager		= $notification_manager;
}
and tried to make a function.
after the acp edit the db (changing the column "user_donatore" in table "phpbb_users"), has to set a notification for the user that the column has been changed...so i've tried to start what to write...maybe something like

Code: Select all

$this->notification_manager->add_notifications('sebo.donatore.notification.type.attiva', [
            'user_id'         => 'user_id',
            'sender_id'       => 'user_admin_id',
            'message_subject' => 'notifica_subject',
        ]);
BUT before i can code this part, i've refreshed the page and appears
[phpBB Debug] PHP Warning: in file [ROOT]/ext/sebo/donatore/controller/acp_controller.php on line 67: Undefined variable $notification_manager
what do i miss?!?! :cry:
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26846
Joined: Fri Aug 29, 2008 9:49 am

Re: External connection to a second db

Post by Mick »

sebo wrote: Sat Feb 17, 2024 9:09 pmi will stop and open a new topic for each request
Yes please, it makes life much easier.
  • "The more connected we get the more alone we become” - Kyle Broflovski© 🇬🇧
User avatar
sebo
Registered User
Posts: 56
Joined: Mon Jan 22, 2024 10:28 pm

Re: External connection to a second db [creating my first ext]

Post by sebo »

ok. sorry again.
created a new topic here
viewtopic.php?t=2651047

Return to “phpBB Custom Coding”