Remote MySQL database connection Laravel 5 not working

Question

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?

Stuff that's in your .env file will always overwrite your config. — Taacoo
– Taacoo, Commented Nov 28, 2016 at 10:52
@Taacoo So if i remove the credentials out of my .env and just put it in the database.php it should work ? Am i not going to wreck it ? :P — Pieter-Jan
– Pieter-Jan, Commented Nov 28, 2016 at 10:55
@Pieter-Jan it will be ovewriten by second argument of env() functions — num8er
– num8er, Commented Nov 28, 2016 at 10:57
@Pieter-Jan Check the answer given. If you empty the env variables. Or add the config data to the env file it should work like a charm. — Taacoo
– Taacoo, Commented Nov 28, 2016 at 11:05

num8er · Accepted Answer · 2016-11-28 10:54:07Z

1

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

answered Nov 28, 2016 at 10:54

num8er

19.5k4 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remote MySQL database connection Laravel 5 not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related