I have two databases with same schema. I need to perform a join on both tables in order to get page results. I am clueless about joining databases. More so since my existing query has joins within first database.
This is my query:
$base = mysql_query("SELECT DISTINCT page.path,
page.site_id,
(SELECT metadata_custom.value
FROM metadata_custom
WHERE field = 'academic search title'
AND page.id = metadata_custom.page_id) AS value,
page.id
FROM page
INNER JOIN metadata
ON page.metadata_id = metadata.id
INNER JOIN metadata_custom
ON page.id = metadata_custom.page_id
WHERE field = 'academic search keywords'
AND value LIKE 'undergraduate%'
ORDER BY value ");
I need same result but after this first database is joined with second. Any pointers?
Update: There are two connections here, with same host and different userid and password. I am not getting any connection error though but script is not returning anything. Must there be an error here:
$base = mysql_query("select distinct page.path,page.site_id,
(select metadata_custom.value from db1.metadata_custom where field='academic search title' and page.id=metadata_custom.page_id)
AS value,
page.id from db1.page inner join db1.metadata on db1.page.metadata_id=db1.metadata.id inner join db1.metadata_custom on db1.page.id=db1.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%'
UNION
select distinct page.path,site_id,(select metadata_custom.value from metadata_custom where field='academic search title' and page.id=metadata_custom.page_id) AS value,
page.id from db2.page inner join db2.metadata on db2.page.metadata_id=db2.metadata.id inner join db2.metadata_custom on db2.page.id=db2.metadata_custom.page_id where field='academic search keywords' and
value like 'undergraduate%'
ORDER BY value", $connection);
db1 and db2 are the two databases here
I used same connection link since I read somewhere that it is connection to server and can be used to access any database on the same server.
Thank you for your help and time, much appreciate it.