Level 47
What exactly did you do? There is no tutorial in that thread so Im a little confused. Do both tables have a username column?
Sep 1, 2016
10
Level 1
Laravel 5.3 username login sql column error?
Note - I have subdirectory folders setup for Admin and Users and Guest. I am using two different database connections for Admin and Users. Database 1 connection = Admin, Database 2 connection = Users. They each have their own table called "users".
I followed the tutorial from this laravel post: https://laracasts.com/discuss/channels/general-discussion/login-by-username-in-laravel-53#
I get this error:
QueryException in Connection.php line 761: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'username' in 'where clause' (SQL: select * from users where username = s_admin limit 1)
in Connection.php line 761
at Connection->runQueryCallback('select * from `users` where `username` = ? limit 1', array('s_admin'), object(Closure)) in Connection.php line 717
at Connection->run('select * from `users` where `username` = ? limit 1', array('s_admin'), object(Closure)) in Connection.php line 351
at Connection->select('select * from `users` where `username` = ? limit 1', array('s_admin'), true) in Builder.php line 1641
at Builder->runSelect() in Builder.php line 1627
at Builder->get(array('*')) in Builder.php line 613
at Builder->getModels(array('*')) in Builder.php line 318
at Builder->get(array('*')) in Builder.php line 288
at Builder->first() in EloquentUserProvider.php line 104
at EloquentUserProvider->retrieveByCredentials(array('username' => 's_admin', 'password' => 'password')) in SessionGuard.php line 355
at SessionGuard->attempt(array('username' => 's_admin', 'password' => 'password'), false) in AuthenticatesUsers.php line 44
at AuthController->login(object(Request)) in AuthController.php line 43
at AuthController->postLogin(object(Request))
at call_user_func_array(array(object(AuthController), 'postLogin'), array(object(Request))) in Controller.php line 55
at Controller->callAction('postLogin', array(object(Request))) in ControllerDispatcher.php line 44
at ControllerDispatcher->dispatch(object(Route), object(AuthController), 'postLogin') in Route.php line 190
at Route->runController() in Route.php line 144
at Route->run(object(Request)) in Router.php line 642
at Router->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in SubstituteBindings.php line 41
at SubstituteBindings->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 64
at VerifyCsrfToken->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Router.php line 644
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 618
at Router->dispatchToRoute(object(Request)) in Router.php line 596
at Router->dispatch(object(Request)) in Kernel.php line 267
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 46
at CheckForMaintenanceMode->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Kernel.php line 149
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 116
at Kernel->handle(object(Request)) in index.php line 54
1/2 PDOException in Connection.php line 333: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'username' in 'where clause'
in Connection.php line 333
at PDO->prepare('select * from `users` where `username` = ? limit 1') in Connection.php line 333
at Connection->Illuminate\Database\{closure}(object(MySqlConnection), 'select * from `users` where `username` = ? limit 1', array('s_admin')) in Connection.php line 754
at Connection->runQueryCallback('select * from `users` where `username` = ? limit 1', array('s_admin'), object(Closure)) in Connection.php line 717
at Connection->run('select * from `users` where `username` = ? limit 1', array('s_admin'), object(Closure)) in Connection.php line 351
at Connection->select('select * from `users` where `username` = ? limit 1', array('s_admin'), true) in Builder.php line 1641
at Builder->runSelect() in Builder.php line 1627
at Builder->get(array('*')) in Builder.php line 613
at Builder->getModels(array('*')) in Builder.php line 318
at Builder->get(array('*')) in Builder.php line 288
at Builder->first() in EloquentUserProvider.php line 104
at EloquentUserProvider->retrieveByCredentials(array('username' => 's_admin', 'password' => 'password')) in SessionGuard.php line 355
at SessionGuard->attempt(array('username' => 's_admin', 'password' => 'password'), false) in AuthenticatesUsers.php line 44
at AuthController->login(object(Request)) in AuthController.php line 43
at AuthController->postLogin(object(Request))
at call_user_func_array(array(object(AuthController), 'postLogin'), array(object(Request))) in Controller.php line 55
at Controller->callAction('postLogin', array(object(Request))) in ControllerDispatcher.php line 44
at ControllerDispatcher->dispatch(object(Route), object(AuthController), 'postLogin') in Route.php line 190
at Route->runController() in Route.php line 144
at Route->run(object(Request)) in Router.php line 642
at Router->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in SubstituteBindings.php line 41
at SubstituteBindings->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in VerifyCsrfToken.php line 64
at VerifyCsrfToken->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in ShareErrorsFromSession.php line 49
at ShareErrorsFromSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in StartSession.php line 64
at StartSession->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37
at AddQueuedCookiesToResponse->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in EncryptCookies.php line 59
at EncryptCookies->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Router.php line 644
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 618
at Router->dispatchToRoute(object(Request)) in Router.php line 596
at Router->dispatch(object(Request)) in Kernel.php line 267
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request)) in Pipeline.php line 53
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 46
at CheckForMaintenanceMode->handle(object(Request), object(Closure)) in Pipeline.php line 137
at Pipeline->Illuminate\Pipeline\{closure}(object(Request)) in Pipeline.php line 33
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in Pipeline.php line 104
at Pipeline->then(object(Closure)) in Kernel.php line 149
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 116
at Kernel->handle(object(Request)) in index.php line 54
Level 1
@zachleigh Only the Admin for my project has a username column. Admins will use username and pass to login, whereas Regular users will use email and password. Both have their own users table in their own database.
Level 47
So... You are getting the error because there is no username column on the users table. If youre using two different databases that have different structures, you are going to have to build a custom auth layer.
Level 1
@zachleigh The admin table does have a username column.
Level 47
Sorry. Typo. You have no username column in the users table, correct? This is the problem. You're telling laravel to look for it, but it deosnt exist.
Level 1
@zachleigh The administrator's users table does have a username column. Only admins login using usernames. Regular users login using email, which doesn't have a username column.
Level 47
And Laravel doesn't know about this. Because you require different login methods, you need to build a system that allows for this.
1 like
Level 122
how do you switch databases?
Level 1
@Snapey I read a post I googled that showed that I can toggle the connection as long as i state a protected $connection = "myConnectionName" in the Model file for my use case scenario.
Level 1
Ok, I figured out what the issue was. The protected $connection in the model wasn't doing anything so it was looking for 'username' column in my regular users table, when it was supposed to look for it in the admin users table which is in another mysql connection. All I had to do was in the controller, inside the constructor, do a DB::setDefaultConnection('nameofconnection'); and all problem was solved.
Please or to participate in this conversation.