SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from `songs` where `id` = 5 limit 1)

Question

I am trying to get specific data from the database by using column SongID when a user clicks a link but I am getting this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from songs where id = 5 limit 1)

The Controller Class:

<?php namespace App\Http\Controllers;

use App\Http\Requests;
use App\Http\Controllers\Controller;

use Illuminate\Http\Request;
use DB;

class SongsController extends Controller {
    public function index()
    {
        $name = $this->getName();
        $songs = DB::table('songs')->get();
        return view('songs.index', compact('songs','name'));
    }
    public function show($id)
    {
        $name = $this->getName();
        $song = DB::table('songs')->find($id);
        return view('songs.show', compact('song','name'));
    }

    private function getName()
    {
        $name = 'Tupac Amaru Shakur';
        return $name;
    }
}

Migration:

    public function up()
    {
            Schema::create('songs', function($table)
            {
                $table->increments('SongID');
                $table->string('SongTitle')->index();
                $table->string('Lyrics')->nullable();
                $table->timestamp('created_at');
            });
    }

Answer 1

When you use find(), it automatically assumes your primary key column is going to be id. In order for this to work correctly, you should set your primary key in your model.

So in Song.php, within the class, add the line...

protected $primaryKey = 'SongID';

If there is any possibility of changing your schema, I'd highly recommend naming all your primary key columns id, it's what Laravel assumes and will probably save you from more headaches down the road.

Answer 2

$song = DB::table('songs')->find($id);

here you use method find($id)

for Laravel, if you use this method, you should have column named 'id' and set it as primary key, so then you'll be able to use method find()

otherwise use where('SongID', $id) instead of find($id)

Answer 3

Just Go to Model file of the corresponding Controller and check the primary key filed name

such as

protected $primaryKey = 'info_id';

here info id is field name available in database table

More info can be found at "Primary Keys" section of the docs.

Answer 4

protected $primaryKey = 'SongID';

After adding to my model to tell the primary key because it was taking id(SongID) by default

Answer 5

I am running laravel 5.8 and i experienced the same problem. The solution that worked for me is as follows :

1. I used bigIncrements('id') to define my primary key.

2. I used unsignedBigInteger('user_id') to define the foreign referenced key.

       Schema::create('generals', function (Blueprint $table) {
           $table->bigIncrements('id');
           $table->string('general_name');
           $table->string('status');
           $table->timestamps();
       });
   
   
       Schema::create('categories', function (Blueprint $table) {
           $table->bigIncrements('id');
           $table->unsignedBigInteger('general_id');
           $table->foreign('general_id')->references('id')->on('generals');
           $table->string('category_name');
           $table->string('status');
           $table->timestamps();
       });
   

I hope this helps out.