I'm using Laravel 5. I need to get some data from a remote MySQL database.
I've already set up my database connection in config/database.php. This is how it looks:
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
'remotemysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '************'),
'database' => env('DB_DATABASE', 'osys'),
'username' => env('DB_USERNAME', 'Syn'),
'password' => env('DB_PASSWORD', '****************'),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
],
The connection info is correct, I already tested it and I'm able to connect to the remote database.
To test it out I just fetched the database connection and the data in my controller to send it to a view to check whether or not everything is working alright. This is my controller:
...
use DB;
...
public function item()
{
$items = DB::connection('remotemysql')
->table('ip_products')
->get();
return view('admin.item', compact('items'));
}
and this is my view:
...
<tbody>
@foreach ($items as $item)
<tr>
<td>{{$item->id}}</td>
</tr>
@endforeach
</tbody>
...
When I try to load my view I get this error message:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dvs.ip_products' >doesn't exist (SQL: select * from
ip_products
)
The error shows me that Laravel tries to get the table form the dvs database (which is the main site database). So it isn't using the connection 'remotemysql'. If it couldn't connect to the remote database it would have got a connection error but I think its not using the remote connection at all.
What can I do to fix this issue?
As You can see from error:
Table 'dvs.ip_products' >doesn't exist
where dvs
is database name.
so according to Your config files:
'remotemysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '************'),
'database' => env('DB_DATABASE', 'osys'),
'username' => env('DB_USERNAME', 'Syn'),
'password' => env('DB_PASSWORD', '****************'),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
],
env()
method has 2 args: $key, $default
so it looks first to .env
file to find $key
in You case DB_HOST, DB_DATABASE...
and if it's not defined will use $default
that You define as second argument.
You can read about it here