phpmysqllaravellaravel-query-builder

Laravel query builder always fetches null data


I am quite new to Laravel, especially when it comes to retrieving things from a database. After reading their documentation about query builder, I found a section that shows example code to do exactly what I need:

If you don't need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

$email = DB::table('users')->where('name', 'John')->value('email');

So, I changed out the tables and values for the ones I need:

$loginID = DB::table('logins')->where('email', $email)->value('id');

I am dealing with registration currently, and when a user registers, the RegisteredUserController.php runs. However, it crashes with an error:

QLSTATE[HY000]: General error: 1364 Field 'loginID' doesn't have a default value (SQL: insert into users (name, address, irdNum, phone, updated_at, created_at) values (Kewl, 17a jacks street Street, 222222, 0275502217, 2021-08-21 05:28:37, 2021-08-21 05:28:37)) phone, updated_at, created_at) values (Tessa Jewels, 17a Customs street

Error has been fixed by commenter, thanks team!

After looking at other posts on Stack Overflow, it seems to be that that error happens when a table is not nullable and you pass a null value to it.

Below are my classes:

User Migration File

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->bigIncrements('id');
            $table->string('name', 45);
            $table->mediumText('address');
            $table->string('irdNum');
            $table->integer('phone');
            $table->bigInteger('loginID')->unsigned()->index();
            $table->timestamps();
        });

        Schema::table('users', function($table) {
            $table->foreign('loginID')->references('id')->on('logins')
                ->onUpdate('cascade')->onDelete('cascade');
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

Logins Migration File

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateLoginsTable extends Migration
{
    public function up()
    {
        Schema::create('logins', function (Blueprint $table) {
            $table->id();
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('logins');
    }
}

Login Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class Login extends Authenticatable
{
    use HasFactory,Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'email',
        'password'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];
}

User Model

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Notifications\Notifiable;
use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    use HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'address',
        'irdNum',
        'phone',
        'logInID'
    ];
}

RegisteredUserController

<?php

namespace App\Http\Controllers\Auth;

use App\Http\Controllers\Controller;
use App\Models\Login;
use App\Models\User;
use App\Providers\RouteServiceProvider;
use Illuminate\Auth\Events\Registered;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Hash;
use Illuminate\Validation\Rules;
use Illuminate\Support\Facades\DB;

class RegisteredUserController extends Controller
{
    /**
     * Display the registration view.
     *
     * @return \Illuminate\View\View
     */
    public function create()
    {
        return view('auth.register');
    }

    /**
     * Handle an incoming registration request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\RedirectResponse
     *
     * @throws \Illuminate\Validation\ValidationException
     */
    public function store(Request $request)
    {
        $request->validate([
            'name' => 'required|string|max:255',
            'address' => 'required|string|max:500',
            'irdNum' => 'required|string|max:12',
            'phone' => 'required|string|max:16',
            'email' => 'required|string|email|max:255|unique:logins',
            'password' => ['required', 'confirmed', Rules\Password::defaults()],
        ]);

        $login = Login::create([
            'email' => $request->email,
            'password' => Hash::make($request->password)
        ]);

        event(new Registered($login));

        $email = $request->email;
        $loginID = DB::table('logins')->where('email', $email)->value('id');

        if (is_null($loginID)) {
            $loginID = 123;
        }

        $user = User::create([
            'name' => $request->name,
            'address' => $request->address,
            'irdNum' => $request->irdNum,
            'phone' => $request->phone,
            'loginID' => $loginID
        ]);

        $user->save();

        Auth::login($login);

        return redirect(RouteServiceProvider::HOME);
    }
}

In this project, Login model is being used to authenticate instead of the regular way Laravel does it. I know it would be a lot easier to leave it as the defaults for authentication, but I also want to learn how things work and doing so taught me a lot.

Also, it saves things perfectly fine to my database. For example, if I got rid of the User code and only had the create login and then register, I can then see the entry in my database, via PhpMyAdmin, straight away. It just seems to have issues retrieving anything.

Issue Explanation

$email = $request->email;
$loginID = DB::table('logins')->where('email', $email)->value('id');

The request is sent from a form and one of the inputs is an email. So, the user fills in their details and it generates a login entry that saves their email and password. After that, I extract which entry it is (by searching the database for entries in logins table that match the request email) and retrieve its ID. That ID is then used when creating the User model:

$user = User::create([
    'name' => $request->name,
    'address' => $request->address,
    'irdNum' => $request->irdNum,
    'phone' => $request->phone,
    'loginID' => $loginID
]);

$user->save();

But it complains about the 'loginID' => $loginID line and that is when the error pops up. Since we created the login table and saved it to the database successfully using the exact same variable ($request->email;), it should return the ID but instead returns NULL.

My development environment is set up with Docker running 4 containers: app(php), db(mysql), PhpMyAdmin and webserver(nginx) if that helps.

It turns out the $loginID has the correct value, so I am not too sure why I get errors when trying to create the user model with it. I found the value by doing dd($loginID); as suggested in the comments.


Solution

  • in user model $fillable you wrote logInID change to loginID