DB::connection does not work as documented

Hi @rabol

So the my_tableis in the old database (mysql) table?

Try this:

$oldUser = DB::Connection->select('select * from my_table where id = ?',[$id]);

Or

$oldUser = DB::Connection->connection('mysql')->select('select * from my_table where id = ?',[$id]);

Did you set up multiple DB connections, and that is documented on how to do that.

And it does work as I've used multiple connections.

Why not just

CREATE TABLE <sometable> AS
SELECT * 
FROM <otherdatabase>.<sometable>

<******> should of course be the name you want without the <>

Documentation say: $users = DB::connection('foo')->select(...); One needs to have a connection called ‘foo’ in condig/database.php but it does not work

Have you gone into config database and set up your connections, foo is an example use your own names as needed.

From Docs

The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file:

@JLRDW - yes, as stated in the initial post:

I have a connection called 'orig_db'

and use it like this:

$record = DB::Connection('orig_db')->select('select * from my_table where id = ?',[$id]);

but get an error as the select is executed against the 'default' database and not the one defined in the 'orig_db' configuration

@TRAY2 - There are many workarounds, but i just want to use the one that is documented

@MVD - Sorry, none of your suggestions work.

there is no such thing as

DB::Connection()->connection(...)

Wow,

DB Facade

$pet = DB::connection('mysqlv2')->select('select * from dc_pets where petid = ?', [1]);

Outputs

Array
(
    [0] => stdClass Object
        (
            [petid] => 1
            [petname] => BRIGGS
            [species] => DOG
            [sex] => M
            [ownerid] => 1
            [petowner] => JIMMIE
            [ostreet] => SANDEFER
            [odate] => 
            [ocheck] => 0
            [dogpic] => briggs.jpg
        )

)

$sth = DB::connection('mysqlv2')->then whatever here.

$quy = DB::connection('mysqlv2')->table('dc_powners')
          ->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
          ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
          ->selectRaw('max(dc_pets.petid) as maxPetId')
          ->where('dc_powners.ownerid', '<', 3)
          ->groupby('dc_powners.ownerid')
          ->orderby('dc_powners.oname')
          ->get();

For eloquent

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Powner extends Model
{

    protected $table = 'dc_powners';
    protected $primaryKey = 'ownerid';
    protected $connection = 'mysqlv2';
    protected $fillable = [
        'ownerid',
        'oname',
        'ostreet',
        'odate',
        'ocheck'
    ];
    public $timestamps = [];

}

ALL of this stuff is very well documented.

$sql = "SELECT powners.ownerid, powners.oname, ";
          $sql .= "COUNT(pets.petid) AS CountOfpetid ";
          $sql .= "FROM powners LEFT JOIN pets ON ";
          $sql .= "powners.ownerid = pets.ownerid ";
          $sql .= "GROUP BY powners.ownerid ";
          $sql .= "ORDER BY powners.oname";

$sth = DB::connection('mysqlv2')->getPdo()->prepare($sql);
$sth->execute();
$quy = $sth->fetchAll(\PDO::FETCH_ASSOC);  // or however you fetch

Done quick, for real, bind parameters as needed.

@JLRDW - I 100% agree with you that it is documented and while double checking the config i finally saw the problem.

I copied the 'mysql' section, but I did not change this part

        'orig_db' => [
            'database' => env('DB_DATABASE', 'orig_db'),
            'username' => env('DB_USERNAME', 'homestead'),
            'password' => env('DB_PASSWORD', 'secret'),
        ],

so therefor it took the db defined in .env file and not the 'orig_db'

We all do that at times, make code mistakes. After all we are human....

See my updated reply above, I show all possible connections.

The one I showed is documented in the MySQL documentation on how to create tables from a SQL select statement and the connection to another database on the same MySQL server is also documented.

Since I see this as an one time action to migrate the data from one database to another, there really is no need to do it in Laravel but that is up to you.

@TRAY2 - I need to transform data during the migration, so the data migration is a 'project' within the project