• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Joining Multiple database problem :(

#1
[eluser]saidai jagan[/eluser]
Hi,
I am new to CI.I have two database.
I call the databases by the following means
$this->db1... and $this->db2...

if I put a query like this (Joining two database in a join query)
$sql = 'SELECT DB1.DBTable1.user_id FROM DB1.DBTable1
LEFT JOIN DB1.DBTable2 on DB1.DBTable2.user_id = DB1.DBTable1.user_id
LEFT JOIN DB2.DBTable1 on DB2.DBTable1.email = DB1.DBTable2.user_email WHERE .......'

and i run the query by,$this->db->query($sql);
it shows the 1142 error. (i.e) SELECT command denied to user 'username'@'localhost' for table 'DB2.DBTable1'.

Thanks.

#2
[eluser]TheFuzzy0ne[/eluser]
I don't believe you can join two tables from two separate databases. If you need to join them, they should be in the same database.

#3
[eluser]saidai jagan[/eluser]
hi it is working for me in local, if i upload in the server it is not working Sad

#4
[eluser]TheFuzzy0ne[/eluser]
Wow. You learn something new everyday...

Would I be right in assuming that you are accessing your localhost database with a root account? If so, that will probably be what your problem is.

#5
[eluser]xwero[/eluser]
It doesn't need to be a root account, just an account that is allowed to access the two databases.

#6
[eluser]saidai jagan[/eluser]
Thanks for u r timely posts.!!
Is there any way to access the two database ?

#7
[eluser]TheFuzzy0ne[/eluser]
[quote author="xwero" date="1235414887"]It doesn't need to be a root account, just an account that is allowed to access the two databases.[/quote]

Sorry, I should have mentioned that, but my point was, it might explain why it works on localhost and not on his remote server.

#8
[eluser]saidai jagan[/eluser]
will it works calling mysql_query($sql) instead of $this->DB1...

#9
[eluser]xwero[/eluser]
I'm not sure but i think the problem is the fact that the active database is set by the database class configuration. If the account has enough permissions you only have to provide a connection.

So i think the solution is to go php native and do
Code:
function get_userids()
{
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

$sql = ‘SELECT DB1.DBTable1.user_id FROM DB1.DBTable1
LEFT JOIN DB1.DBTable2 on DB1.DBTable2.user_id = DB1.DBTable1.user_id
LEFT JOIN DB2.DBTable1 on DB2.DBTable1.email = DB1.DBTable2.user_email WHERE .......’;

$query = mysql_query($sql);

$rows = mysql_fetch_assoc($result);

mysql_close($link);

return $rows;
}
Of course you need to add error checks.

#10
[eluser]TheFuzzy0ne[/eluser]
And a semi-colon to the end of the query string. Wink


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.