Connect to mutiple MySQL Databases and Hosts

Here is my script, in which I get an error:

<?
class database{
  var $link;
  var $link2;

  function database()
  {
    $this->connect();
  }
 
  function database2()
  {
    $this2->connect2();
  }

  function connect()
  {
    $DBHOST="####";
    $DBUSER="####";
    $DBPASSWD="####";
    $DBNAME="intern";
    $this->link=mysql_connect($DBHOST,$DBUSER,$DBPASSWD);
    mysql_select_db($DBNAME);
    return $this->link;
  }
 
  function connect2()
  {
    $DBHOST="####";
    $DBUSER="####";
    $DBPASSWD="####";
    $DBNAME="Customers";
    $this2->link2=mysql_connect($DBHOST,$DBUSER,$DBPASSWD);
    mysql_select_db($DBNAME);
    return $this2->link2;
  }


  function close()
  {
    mysql_close($this->link);
  }
 
    function close2()
  {
    mysql_close($this2->link2);
  }

  function arrayFromQuery ($query)
  {
    $result=mysql_query($query);
    echo mysql_error();
    while ($row=mysql_fetch_array($result))
    {
      $counter++;
      $results[$counter]=$row;
    }
  if (!$results) $results=array();
  return $results;
  }
} // END CLASS mainclass

class mainclass extends database{

  function getToDoBYID ($id) // ignore, this works!
  {
    return $this->arrayFromQuery("select * from todo where ID='$id'");
  }
 
  function getContracts () // This second connection won't work
  {
    return $this2->arrayFromQuery("select * from Customers.contracts");
  }

}  // END CLASS internclass
?>

<? // Calling my function
      foreach ($mainclass->getContracts() as $Contract)
      {  echo $Contract['ID'] }?>
xeta_itAsked:
Who is Participating?
 
RoonaanCommented:
Just use:

$db = new mainclass();

$db2 = new mainclass(2); //new db object with connection to db2

Your dbclass should then just look something like:

class database{
  var $link;

  function database($db = 1)
  {
    if($db == 2)
     $this->connect2();
    else
     $this->connect();
  }
 
  function connect()
  {
    $DBHOST="####";
    $DBUSER="####";
    $DBPASSWD="####";
    $DBNAME="intern";
    $this->link=mysql_connect($DBHOST,$DBUSER,$DBPASSWD);
    mysql_select_db($DBNAME);
    return $this->link;
  }
 
  function connect2()
  {
    $DBHOST="####";
    $DBUSER="####";
    $DBPASSWD="####";
    $DBNAME="Customers";
    $this->link=mysql_connect($DBHOST,$DBUSER,$DBPASSWD);
    mysql_select_db($DBNAME, $this->link);
    return $this->link;
  }


  function close()
  {
    mysql_close($this->link);
  }
 
  function close2()
  {
    mysql_close($this->link);
  }

  function arrayFromQuery ($query)
  {
    $result=mysql_query($query, $this->link);
    echo mysql_error();
    while ($row=mysql_fetch_array($result))
    {
      $counter++;
      $results[$counter]=$row;
    }
  if (!$results) $results=array();
  return $results;
  }
} // END CLASS mainclass



-r-
0
 
RoonaanCommented:
Yeah, thats great.

"an error".

Possibly some more info would be fine. Like the type of error, or possible even the error text, if that wouldn't be too much trouble ;-)

regards

-r-
0
 
RoonaanCommented:
Like: have you made sure both connections have been made?

What happens when you do echo gettype($this->link2); ?

regards
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
xeta_itAuthor Commented:
Call to a member function on a non-object
0
 
xeta_itAuthor Commented:
where do i put that echo?
0
 
RoonaanCommented:
pfff.. found the error..

You almost everywhere used $this and $this2 where you should have been using
$this->link and $this->link2.

$this2->connect() should be $this->connect2();

etc.

0
 
xeta_itAuthor Commented:
okay now that error is gone, but a new one pops up!

Table 'intern.contracts' doesn't exist... so it probably conntected to the wrong db... how to fix that?
0
 
xeta_itAuthor Commented:
if i change everything to "this" instead of "this2" how does it know, which db to connect to?

The first function should connect to another db than the second function!

  function getToDoBYID ($id) // ignore, this works!
  {
    return $this->arrayFromQuery("select * from todo where ID='$id'");
  }
 
  function getContracts () // This second connection won't work
  {
    return $this->arrayFromQuery("select * from Customers.contracts");  //changed from this2
  }
0
 
RoonaanCommented:
Well you could have the $this->link/$this->link2 passed as an reference parameter:

  function getToDoBYID ($id, &$link) // ignore, this works!
  {
    return $this->arrayFromQuery("select * from todo where ID='$id'", $link);
  }
 
  function getContracts () // This second connection won't work
  {
    return $this->arrayFromQuery("select * from Customers.contracts", $this->link2);  //changed from this2
  }
0
 
gnudiffCommented:

Btw, please check, if you are not trying to reinvent wheel.

I don't know what else goes into your db class, but the functions you show are a DB abstraction layer, such as already implemented in a free product called adoDB:

http://www.phplens.com/adodb/

You download & extract it, add the base dir to your include path, and then do something like:

      $dsn = 'mysql://root:pwd@localhost/mydb';
      $db = NewADOConnection($dsn);
      if (!$db) die("Connection failed");
      
      # no need to call connect/pconnect!
      $arr = $db->GetArray("select * from table");
      

I have been using it for the past half a year, and it works just excellent, plus it is rather robust and supports nice things, such as parametrized queries, getting result set as associative array etc.

0
 
xeta_itAuthor Commented:
Doesn't work....

ERROR: Missing argument 1 for getContracts ...

Are you sure this is the correct syntax?
0
 
xeta_itAuthor Commented:
Well I'm not trying to reinvent the wheel... but this is a very komplex setup which I have been using for 4 years now!
It a very objective and flexible approach. I don't want to rewrite my whole core apllication.

All I need is to implement the connection to a second db!

Connecting to one db is fine and has worked for years. I need someone to think through my script and make MY script work...
Any other approach would change too much of my whole application...
0
 
RoonaanCommented:
Best would be to just instantiate two different Db objects instead of trying to have one object maintain two connections.

Inside the db class you should make sure you always use the link argument to mysql_ functions, because otherwise the latest connection made will be used.

Regards

-r-
0
 
xeta_itAuthor Commented:
But I need to be able to get data from the two db's on the same page...

how is it possible to instantiate two connections within one class?
0
 
xeta_itAuthor Commented:
I get parse errors for this line:

 function database($db = 1)
  {

This is the toal error output:
Parse error: parse error, expecting `'{'' in /xeta/www/intern/db.php on line 12

Fatal error: Cannot instantiate non-existent class: mainclass in /xeta/www/intern/index.php on line 3
0
 
RoonaanCommented:
I have no clue.

function database($db = 1) is totally standard php syntax.

-r-
0
 
gnudiffCommented:
> Well I'm not trying to reinvent the wheel... but this is a very komplex setup which I have been using for 4 years now!
> It a very objective and flexible approach. I don't want to rewrite my whole core apllication.

Oh, I see, that certainly makes sense; you are quite right.


> function database($db = 1) is totally standard php syntax.

What version of PHP are you using, xeta_it?

Roonaan's example works for me. At least the class is being loaded without syntax error messages.


The only thing I note in the example is:

$db = new mainclass(2);  

that should've been: $db = new database(2);


0
 
xeta_itAuthor Commented:
I'm using PHP 4.3.3
0
 
xeta_itAuthor Commented:
Okay I got it... was a problem from copying the code from webbrowser to editor... messed up the codepages for whitespaces...
0
 
xeta_itAuthor Commented:
Now I am getting an error for the folling line:

  function connect2()
  {
    $DBHOST="####";
    $DBUSER="####";
    $DBPASSWD="####";
    $DBNAME="Customers";
    $this->link=mysql_connect($DBHOST,$DBUSER,$DBPASSWD);
    mysql_select_db($DBNAME, $this->link);   // ERROR fpr this line:

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /xeta/www/intern/db.php on line 43

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /xeta/www/intern/db.php on line 43

0
 
xeta_itAuthor Commented:
Okay, fixed that, too...

further down I'm really stuck:
  function arrayFromQuery ($query)
  {
    $result=mysql_query($query, $this->link);
    echo mysql_error();
    while ($row=mysql_fetch_array($result))
    {
      $counter++; // ERROR: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /xeta/www/intern/index.php on line 59

      $results[$counter]=$row; // ERROR: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /xeta/www/intern/index.php on line 60

    }
  if (!$results) $results=array();
  return $results;
  }
0
 
xeta_itAuthor Commented:
How come I get an error here? I haven't changed this a bit?
Except your addition:
$result=mysql_query($query, $this->link);

which used to be:
$result=mysql_query($query);
0
 
xeta_itAuthor Commented:
Okay works now! Now I have a problem that evolves from this solution: I will open another topic with another 500 points, since this problem has been solved!!
0
 
gnudiffCommented:
It appears that you are not connecting to the database or that the query returns no results.

The first thing that comes to mind:

According to what Roonaan said, you should be using two variables now, one for connecting to 1st database and 2nd for connecting to the second:

$db1= new database();

$db2= new database(2);

It sounds as if you are trying to use one $db to use queries from both databases, or that you have mixed which $db to use for which query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.