Solved

Connect to mutiple MySQL Databases and Hosts

Posted on 2004-10-20
24
299 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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 look for a specific file type in a local or remote server directory using PHP.

758 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

19 Experts available now in Live!

Get 1:1 Help Now