Can I get the primary key inside the facadesdb insert for direct use

I am learning Laravel 8 and came across this problem. So, I made a controller class with FacadesDB Insert function. This is my code :

public function aksimenulis_laporan(Request $request){
        $filefoto = $request->foto;
        DB::table('pengaduan')->insert([
            'tgl_pengaduan' => date('Y-m-d'),
            'nik' => $_POST['nik'],
            'isi_laporan' => $_POST['isi_laporan'],
            'foto' => $filefoto->getClientOriginalName(),
            'status' => '0',
        ]);

        // isi dengan nama folder tempat kemana file diupload
        $tujuan_upload = storage_path();
 
        // upload file
        $filefoto->move($tujuan_upload,$filefoto->getClientOriginalName());
        return redirect('');    
    }

https://i.stack.imgur.com/VTJyS.png This is my database table structure.

And I am trying to insert 'foto' with value of 'id_pengaduan' or the primary key, like using this code. Which will be PrimaryKey_OriginalFileName

'foto' => $filefoto-> 'id_pengaduan'.'_'.getClientOriginalName(),

But the problem I didn't have the value yet. Is there a way that I can value 'foto' with the primary key of the table?

CodePudding user response：

you can use the insertGetId method to insert a record and then retrieve the ID:

 $pengaduan_id= DB::table('pengaduan')->insertGetId([
            'tgl_pengaduan' => date('Y-m-d'),
            'nik' => $_POST['nik'],
            'isi_laporan' => $_POST['isi_laporan'],
            'status' => '0',
        ]);
   DB::table('pengaduan')->where('id',$pengaduan_id)->update(['foto'=>$filefoto->getClientOriginalName()]);

you can not get the id before the insertion operation is done.

you also should do it in transaction, but for now, I will keep it this way.

CodePudding user response：

You cannot (and shouldn't) insert a row and define its primary key. That's kind of the whole purpose of a primary key. You let SQL manage its identifiers, this is good incase of an error, so there are no duplicates in the primary key.

I'll go into eloquent since you're starting new in Laravel, this will make your life easier with DB operations.

First you'd need to make a new model for your table. You can do this using the console command:

php artisan make:model ModelName

Afterwards, navigate to your Models directory (app/Models/) and you'll find the new model ModelName.php

In there, you'd need to specify the table name and the primary key. You can do that with the these two lines:

protected $table = 'pengaduan';
protected $guarded = ['id_pengaduan'];

Your model should now look something like

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ModelName extends Model
{
    use HasFactory;

    protected $table = 'pengaduan';
    protected $guarded = ['id_pengaduan'];
}

Save, and you might need to clear your cache. Do this by running php artisan optimize in the console.

After you are done inserting the data into a new row, you can get the primary key that was assigned to the row like this:

    //using eloquent
    use App\Models\ModelName;

    //insert data
    $insertRow = new Pengaduan;
    $insertRow->tgl_pengaduan = date('Y-m-d');
    $insertRow->nik = $_POST['nik'];
    $insertRow->isi_laporan= $_POST['isi_laporan'];
    $insertRow->foto = $filefoto->getClientOriginalName();
    $insertRow->status = 0;

    $insertRow->save();

    //get key here
    $rowId = $insertRow->pengaduan_id;

If you want to use the DB::table('table')->insert method, see OMR's answer.

If you really want to define the primary key yourself, then you'd have to run this query on the table:

SET IDENTITY_INSERT [Tablename] ON;

You can read more about this on this thread.