Multiple database connections with PDO

Question

I'm trying to connect to several databases at the same time and am having problems. This query works fine with my local site. (It doesn't have a username or password, thus the "root, root.")

$dsn = "mysql:host=localhost;dbname=gbase;charset=utf8";
 $opt = array(
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
 );

 $pdo = new PDO($dsn,'root','root', $opt);

I used this discussion as a guide in creating a multiple-database query. I replaced all the code above with this:

try {
 $db1 = new PDO('mysql:dbname=gbase;host=localhost', 'root', 'root');
 $db2 = new PDO('mysql:dbname=glife;host=localhost', 'root', 'root');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

But I get these error messages:

Notice: Undefined variable: pdo in /Applications/MAMP/htdocs/gx/index.php on line 164

Fatal error: Call to a member function prepare() on null in /Applications/MAMP/htdocs/gx/index.php on line 164

And this is line 64:

$stm = $pdo->prepare("SELECT A.Site, A.URL, A.Title, A.Article
 FROM 1_about A

I suspect that I need to somehow integrate my new code with my original code, rather than simply replace it. However, I don't understand what's going on. Can someone show me the correct way to write a multiple-database query?

Edit: Below is the entire code I'm using.

try {
 $db1 = new PDO('mysql:dbname=geobase;host=localhost', 'root', 'root');
 $db2 = new PDO('mysql:dbname=geolife;host=localhost', 'root', 'root');
} catch (PDOException $ex) {
 echo 'Connection failed: ' . $ex->getMessage();
}

$stm = $pdo->prepare("SELECT A.Site, A.URL, A.Title, A.Article
 FROM 1_about A
 WHERE A.Site = 'g1' AND A.URL = 'webranger'");
$stm->execute(array(
 // 'MyURL'=>'%'.$MyURL.'%'
));

while ($row = $stm->fetch())
{
 $Article = $row['Article'];
}

echo $Article;

So you've changed `$pdo` to `$db1` and `$db2` respectively... use those instead? — scrowler, Dec 28 '15 at 23:53
Yes, actually the variable name is wrong. If is just a typo, please show all code, or at least the part where you create the PDO instances and execute the queries. — Vitor Villar, Dec 28 '15 at 23:55
@ Vitor Luis - I edited my post, pasting the complete code at the end. @ Robbie and RamRaider - I don't understand where or how to insert $pdo. Replacing $db1 and $db2 with $pdo doesn't make sense (to me, at least). — , Dec 29 '15 at 00:02

score 2 · Accepted Answer · answered Dec 29 '15 at 00:09

2

You trying to call a function from an undefined object. On you case, you created two PDO instances, on $db1 and $db2.

Some lines below, you are trying to call the prepare function from a variable called $pdo. But in the code that you show to us, the $pdo variable doesn't exists.

So, what you need to do is change the $pdo variable for the $db1 variable or $db2 variable. Depends which connection you want to use. Like this:

$stm = $db1->prepare("SELECT A.Site, A.URL, A.Title, A.Article
 FROM 1_about A
 WHERE A.Site = 'g1' AND A.URL = 'webranger'");

Just that! :D

answered Dec 29 '15 at 00:09

Vitor Villar

1,855
18
35

Now I'm getting the error "Notice: Undefined variable: Article in /Applications/MAMP/htdocs/gx/index.php on line 177" but that's probably just a simple bug I can fix. One more question, though - what if I need to query tables from two different databases at the same time? For example, suppose I wanted to join a table from Database1 with a table from Database2? – Dec 29 '15 at 00:15
Well i never tried before, but i think you can put the name of the database in the front f the table name like: `database1.table1` and `database2.table2` – Vitor Villar Dec 29 '15 at 00:17
OK, that makes sense. – Dec 29 '15 at 00:18

Multiple database connections with PDO

1 Answers1