How can I interact with a remote database in my own plugins?

Asked by pystone on 2012-09-21

As is the summary, if I want to connect to and operate a remote database in my own plugin, what should I do? I know that write a totally different class of database will work, but does the framework offer such function? I prefer to use the way integrated in the framework. Thanks in advance!

Question information

English Edit question
PHPDevShell Edit question
Greg Edit question
Solved by:
Last query:
Last reply:
Greg (gregfr) said : #1

Hello and thanks for your interest in the framework.

What kind of database do you want to connect to?

pystone (pystone9205) said : #2

mysql, it may be running in some other servers.

Greg (gregfr) said : #3

It's completly possible but I think it's not documented yet. You can easily add databases in your config files; for example:

$configuration['secondary_db']['dsn'] = 'mysql:host=localhost;dbname=phpdev';

For the rest I don't want to give you false information so I'll check with Don (who wrote the multi db support) and I'll get back to you.

pystone (pystone9205) said : #4

Many thanks!

I have written a class in the mode of PHPDS_legacyConnector, say PY_Connector. In that class, I simply change the applyConfig function, so the connection to sql is based on my own information of mysql database.

In the model php extending PHPDS_query of my own plugin, I add this:

protected $connector = 'PY_Connector';

Yet when running, it seems that all the database operation are based on that connector....
I don't know whether I can deal with the multidb in this way..

Greg (gregfr) said : #5

You got the principle right :) however you don't have to override any method, as long you wan to connect to a Mysql server.

class test_connector extends PHPDS_legacyConnector
 protected $dbHost = "localhost";
 protected $dbName = "test";
 protected $dbUsername = "username";
 protected $dbPassword = "userpassword";

class test_query extends PHPDS_query

 protected $connector = "test_connector";
 protected $sql = "SHOW TABLES";

  $tables = $this->db->invokeQuery('test_query');

  print '<pre>';
  print '</pre>';

You just have to provide the query with the connector class name, and it will handle the black magic for you

pystone (pystone9205) said : #7

This method doesn't work. When I tried to run this "SELECT `UserName` FROM `UserInfo` WHERE `UserID` = 3;" using this method, I got the error message "Table 'phpdev.userinfo' doesn't exist". I am sure that the table 'UserInfo' exist in my own database. In addition, from the message we can know that the change of 'dbName' does not work because the message mentioned 'phpdev.userinfo' rather than 'test.userinfo' (test is the name of my own database)...

What's more, in the class extending from PHPDS_legacyConnector, I even tried to overwrite the applyConfig function, and it was totally the same as above.

Greg (gregfr) said : #8

Let me look into this.

Best Greg (gregfr) said : #10

Sorry I misled you. Please try something like this:

class test_connector extends PHPDS_legacyConnector
 protected function applyConfig($db_config = '')
  $this->dbHost = "localhost";
  $this->dbName = "test";
  $this->dbUsername = "username";
  $this->dbPassword = "userpassword";

pystone (pystone9205) said : #11

No, actually it does not work either. I have tried this before but it still connected to the master database.

Maybe you can solve this problem from these perspective:
1.Noting that the function applyConfig has a parameter '$db_config', this can change the database it will connect to. Yet, in the model php I cannot pass the parameter to the factory function. So I wonder if you can add a variable named 'db_config' in the PHPDS_query class and I can change the value of it as I can do with 'connector'. And this variable can be passed to the constructor of PHPDS_legacyConnector class.
(A simple test can prove that writing a deprived class of PHPDS_legacyConnector does not work: add "echo 'testing~';" in the applyConfig of the deprived class and run the page, there is no output of "testing~". This means that the overwritten function isn't running at all.)

2.In a single session of php, only one mysql connection is permitted. If we want to get another connection at the same time, we should add the fourth parameter 'true' in the mysql_connect() to force to open a new connection.
(I have tried to add this parameter in both the deprived class and PHPDS_legacyConnector class, but none of them worked. So I still reckon that if the first problem can be solved, the whole problem can be solved.)

Waiting for your good news~ :)

Greg (gregfr) said : #12

Its seems your daughter class is not used, that's why nothing seems to work. Can you send me privatly your code? both query and connector, so I can test it.

pystone (pystone9205) said : #13

Sorry about my absence for about a month. I had been on a half-a-month vocation and after that I was busy maintaining the server because our game was launched. I will give you the code tonight~

Terribly sorry...

pystone (pystone9205) said : #14

I think I'd better put these here. If anyone bump into the same problem, he/she can get the answer here.
Here is my own class extends from PHPDS_legacyConnector.

 class PY_Connector extends PHPDS_legacyConnector
  protected $dbHost = "pystone";
  protected $dbName = "mysql";
  protected $dbUsername = "root";
  protected $dbPassword = "mypw";
  //private $link;

  private function applyConfig($db_config = '')
   $db = $this->db;

   // Retrieve all the database settings
   $db_settings = PU_GetDBSettings($this->configuration, $db_config);

   // For backwards compatibility, set the database class's parameters here as we don't know if anyone references
   // db's properties somewhere else
   $db->server = 'localhost';
   $db->dbName = 'mysql';
   $db->dbUsername = 'root';
   $db->dbPassword = 'mypw';

   // Set our own internal properties for faster access and better accessibility.
   $this->dbDSN = $db_settings['dsn'];
   $this->dbHost = $db_settings['host'];
   $this->dbName = 'mysql';
   $this->dbUsername = $db_settings['username'];
   $this->dbPassword = $db_settings['password'];
   $this->dbPersistent = $db_settings['persistent'];
   $this->dbPrefix = $db_settings['prefix'];
   $this->dbCharset = $db_settings['charset'];
   echo 'haha';


If I use it by just extending the class in model.php from PY_Connector, like
class testsql_select extends PHPDS_query
    protected $sql = "SELECT `password` FROM `user` WHERE `Host` = 'localhost';";
    protected $singleValue = true;
    protected $connector = 'PY_Connector';
I will get the error message, "Table 'phpdev.user' doesn't exist". It seems that all the settings do not work.

TitanKing (titan-phpdevshell) said : #15

This is great! Thanks for your contribution and taking the time to post back. Much appreciated.

Greg (gregfr) said : #16

You declared applyConfig() as private. It won't work, since this method is called from outside, it's not masking the parent's method, so your won't be called.
Just delcared it exactly as its parent and it should work:

protected function applyConfig($db_config = '')


pystone (pystone9205) said : #17

Great! It works!
Many thanks to your help!

btw, sorry again about my absence these days...

pystone (pystone9205) said : #18

Thanks Greg, that solved my question.