Solved

Connect to mutiple MySQL Databases and Hosts

Posted on 2004-10-20
24
302 Views
Last Modified: 2013-12-12
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'] }?>
0
Comment
Question by:xeta_it
  • 14
  • 7
  • 3
24 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 12355872
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 12355880
Like: have you made sure both connections have been made?

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

regards
0
 

Author Comment

by:xeta_it
ID: 12355884
Call to a member function on a non-object
0
 

Author Comment

by:xeta_it
ID: 12355895
where do i put that echo?
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12355949
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
 

Author Comment

by:xeta_it
ID: 12355999
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
 

Author Comment

by:xeta_it
ID: 12356014
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 12356033
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
 
LVL 3

Assisted Solution

by:gnudiff
gnudiff earned 50 total points
ID: 12356083

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
 

Author Comment

by:xeta_it
ID: 12356114
Doesn't work....

ERROR: Missing argument 1 for getContracts ...

Are you sure this is the correct syntax?
0
 

Author Comment

by:xeta_it
ID: 12356137
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 12356147
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:xeta_it
ID: 12356167
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
 
LVL 49

Accepted Solution

by:
Roonaan earned 450 total points
ID: 12356248
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
 

Author Comment

by:xeta_it
ID: 12356318
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 12356350
I have no clue.

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

-r-
0
 
LVL 3

Expert Comment

by:gnudiff
ID: 12356444
> 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
 

Author Comment

by:xeta_it
ID: 12356464
I'm using PHP 4.3.3
0
 

Author Comment

by:xeta_it
ID: 12356514
Okay I got it... was a problem from copying the code from webbrowser to editor... messed up the codepages for whitespaces...
0
 

Author Comment

by:xeta_it
ID: 12356537
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
 

Author Comment

by:xeta_it
ID: 12356568
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
 

Author Comment

by:xeta_it
ID: 12356578
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
 

Author Comment

by:xeta_it
ID: 12356662
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
 
LVL 3

Expert Comment

by:gnudiff
ID: 12356665
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now