CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Tartar
Tartar

Reputation: 5452

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

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');
            });
    }

Upvotes: 41

Views: 235096

Answers (6)

codethesolution
codethesolution

Reputation: 11

You can use DB::table as mentioned above but without the 'find'

\Illuminate\Support\Facades\DB::table('model_has_roles')->where('model_id', 123)->update(['role_id' => 2]);

Upvotes: 0

ManojKiran
ManojKiran

Reputation: 6351

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.

Upvotes: 11

Kario Kevo
Kario Kevo

Reputation: 61

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.

Upvotes: 0

GERARD KANDAGOR
GERARD KANDAGOR

Reputation: 139

protected $primaryKey = 'SongID';

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

Upvotes: 0

user1669496
user1669496

Reputation: 33108

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.

Upvotes: 131

Valik Gubenko
Valik Gubenko

Reputation: 153

$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)

Upvotes: 4

Related Questions