PDO / SQLite: a single query spanning two related databases

Question

Context

Playing with two SQLite Feeddemon databases, tags.fdb and feeds.fdb, I created this working raw query (verified using SQLite Manager Firefox's handy add-on):

SELECT tbl_tags.tag_name, feeds.tbl_posts.link FROM tbl_tags INNER JOIN feeds.tbl_posts ON tbl_tags.fd_postid=feeds.tbl_posts.fd_postid ORDER BY tbl_tags.tag_name 

It requests both Feeddemon tags.fdb files and feeds.fdb file files in the main file (using the SQLite ATTACH feeds.fdb AS feeds function). It lists all tagged messages with tag names and hyperlinks. As you can see, the contents of the database are associated with fd_postid.

An example to visualize what is happening:

tags.fdb Table of contents tbl_tags:

 fd_postid tag_name 1 rainbow 2 orange 5 green 

feeds.fdb Table of contents tbl_posts:

 fd_postid link 1 google.com 2 stackoverflow.com 3 microsoft.com 4 slashdot.org 5 techcrunch.com 

Query results sorted by tag:

 green techcrunch.com orange stackoverflow.com rainbow google.com 

Question

How to implement this single internal join request to cover multiple related databases using PDO in PHP?

Connecting to two databases in PDO also creates two objects:

 $db1 = new PDO('sqlite:tags.fdb'); $db2 = new PDO('sqlite:feeds.fdb'); 

But if I want to fulfill the request, I have to specify one object:

 $result = $db1->query('SELECT * FROM sometable'); 

So, how do I fill in the gap and use one query to target both databases?

Or can I use the SQLite ATTACH database AS alias function directly in PDO? If so, how? But that would probably be considered bad practice, right? Because PDO is for a non-specific database.

I think this user was looking for the same thing in the end, but never got to the working code example:

How do you join two PDOs in the same foreach loop?

Note. . When answering a question, please note that I am a frontman / graphic guy who just started learning PHP / PDO / SQLite about two months ago. The question is probably above my level of experience. I am surprised that I managed to get a raw request. But I'm learning ...

Answer 1

To access multiple databases from a single query in SQLite, you need to ATTACH them.

In the case of PDO, it works just like any other SQL command; you just specify a different file name (and directory, if necessary):

 $db = new PDO('sqlite:tags.db'); $db->exec('ATTACH \'feeds.db\' as feeds'); $result = $db->query('SELECT tbl_tags.tag_name, feeds.tbl_posts.link ...'); 

Despite the fact that PDO is mostly independent of the actual database, you cannot help but specify sqlite: when you open the database, the actual SQL dialect you can use is always database dependent. For practical purposes, the ATTACH logically part of opening the database, and feeds.tbl_posts is simply the name of the table.

Note: SQLite searches for table names in all connected databases, so you can omit the database name for unique table names.