Joining Cross Databases

[zeddicus]

Hi All,

My client has a need for a report that invovles me doing some cross database joining.

I am using it in the custom view, so that it doesnt make a view on the database, there are reasons why I cannot do that.

When I put the query in the database, it works. When I put it in the custom view, and run it, it works, but when I click ok, to save it, it gives me an error saying that the table that I am trying to access from the other database, does not exist in this database.

Its as if its looking for it in the projects database, but I am fully referencing the table like so: INNER JOIN dbname.tblname dt ON dt.FIELD=ot.FIELD where ot is a table on the local database.

Any help is greatly appreciated.
Thanks in advance,
Zedd

[Webmaster]

Avoid Custom View, see "Using Custom View" in the help file. Use database view instead.

[zeddicus]

Unfortuneatly, in the envoirnment I work in, my applications and code need to be dynamic and very flexible.

When I pass a sql query, it runs on the server. I don't understand why it should make a difference if I run it in php, or I run it in a view, the end result is the same, and even faster, because the server does not need to recreate the table every time I enter phpmyadmin or anything, it only runs when the user requests the information.

[Webmaster]

The differences are in searching and sorting. Custom View is a legacy feature introduced in the old days when MySQL did not support views, it expects simple SELECT statement only and works by manipulating the SQL by PHP so it may not work properly as database view, depending on the structure of the SQL. If you don't do searching and sorting, it might work though. Anyway, note that the project does not have any info about other databases, do not switch to Query Builder when you create the Custom View, enter your SQL in the SQL tab and press OK button directly to create it. And make sure the database user in the project has SELECT permission for other tables from other databases.

[zeddicus]

Hi,

I have tried your solution:

Webmaster wrote:
Anyway, note that the project does not have any info about
other databases, do not switch to Query Builder when you create the Custom View,
enter your SQL in the SQL tab and press OK button directly to create it. And make
sure the database user in the project has SELECT permission for other tables from
other databases.

And this still gives me the error:

#42S02Table 'databasename.tablename' doesn't exist

Where databasename is the name of the database phpmaker is connected to, and the tablename is a table in the other database I am trying to connect to.

In my query I do put the correct database name for the table, but in the error message it switches it.

[mobhar]

Try to only use "tablename" for the table name instead of "database.tablename".

[zeddicus]

that would never work in mysql... how would it know which database? it would assume its the selected database, and i am trying to connect to a diff database other than the default one... when writing cross database queries you have to specify the database name if it is not the default database.

[mobhar]

Ah, I see. In the Custom View of PHPMaker, you cannot refer to the database other than the current one you are using connected to PHPMaker. The only solution for this is to use the database view instead.

[zeddicus]

I found a temp work around, similar to what i do for the authentication process for a different database.

I basically just made a mock table with the same name in the database, and then it allowed me since it saw the name in the schema..

But it should really allow it, because its very limiting to only be limited to one database, and is not a real world solution. There are always needs to connect to other external databases.

[Ethelseg]

Create view in mysql utilites, am using Navicat

1. Create view ex. vrpta in the 1st database

2. Create view ex. vrpta2 in the 2nd database using vrpta

   
   
   

   Do not alter view in the phpmaker use the utilties.