Laravel Tutorials

Important Links

Home » Laravel Framework »

database

How to create Model

models are stored by default in the root of the app directory. The User model bydefault created by the Laravel framework.

if you want to create other more models, you can simply run the artisan command below.

php artisan make:model model_Name(User)

where model_Name represents the name of the model you want to create

############## Or ##################

php artisan make:model Student -m

This command will create a Student model and also create migration table. -m for migration.

Open Model User Class (app/User.php)

Here Add Some column in $fillable array. which column you want to add your table.

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
use Notifiable;

/**
* The attributes that are mass assignable.
*
* @var array
*/

public $timestamps = FALSE;

protected $fillable = [
'name','email', 'password','mobile',
];

/**
* The attributes that should be hidden for arrays.
*
* @var array
*/
/*
protected $hidden = [
'password', 'remember_token',
];
*/
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [
'email_verified_at' => 'datetime',
];
}

How to create database table

To create a migration, use the make:migration Artisan command

php artisan make:migration create_users_table

Created Migration: 2020_04_14_165730_create_users_table

The new migration will be placed in your database/migrations directory. Each migration file name contains a timestamp, which allows Laravel to determine the order of the migrations.

create table

The --table and --create options may also be used to indicate the name of the table and whether or not the migration will be creating a new table.

php artisan make:migration create_users_table --create=users

php artisan make:migration add_votes_to_users_table --table=users

Migration Structure

php artisan make:migration create_users_table

A migration class contains two methods: up and down. The up method is used to add new tables, columns, or indexes to your database, while the down method should reverse the operations performed by the up method.

Add Some Column in your table.

<?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->bigIncrements('id');
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->increments('id');
$table->integer('answers')->default(0);
$table->integer('points')->default(0);
$table->text('name');
$table->text('color');
$table->text('text')->nullable();
$table->boolean('status')->default(true);
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id') ->on('users') ->onDelete('restrict');
$table->timestamps();
});
}

/**

public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name',32);
$table->string('email',64)->unique();
$table->string('password',128);

//$table->rememberToken();
$table->string('remember_token',164)->nullable();
$table->timestamps();
//$table->timestamp('email_verified_at')->nullable();
});
}
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}

Running Migrations

To run all of your outstanding migrations, execute the migrate Artisan command:

php artisan migrate

Migration table created successfully.
Migrating: 2020_04_17_074320_create_users_table
Migrated: 2020_04_17_074320_create_users_table (0.35 seconds)

The above command create users table and also create given column in your table in database

Forcing Migrations To Run In Production

Some migration operations are destructive, which means they may cause you to lose data. In order to protect you from running these commands against your production database, you will be prompted for confirmation before the commands are executed. To force the commands to run without a prompt, use the --force flag:

php artisan migrate --force

Rolling Back Migrations

To roll back the latest migration operation, you may use the rollback command. This command rolls back the last "batch" of migrations, which may include multiple migration files:

php artisan migrate:rollback

You may roll back a limited number of migrations by providing the step option to the rollback command. For example, the following command will roll back the last five migrations:

php artisan migrate:rollback --step=5

migrate:reset

The migrate:reset command will roll back all of your application's migrations:
php artisan migrate:reset

Roll Back & Migrate Using A Single Command

The migrate:refresh command will roll back all of your migrations and then execute the migrate command. This command effectively re-creates your entire database

php artisan migrate:refresh

// Refresh the database and run all database seeds...
php artisan migrate:refresh --seed

Drop All Tables & Migrate

The migrate:fresh command will drop all tables from the database and then execute the migrate command

php artisan migrate:fresh
php artisan migrate:fresh --seed

Creating & Dropping Tables

To create a new database table, use the create method on the Schema facade. The create method accepts two arguments: the first is the name of the table, while the second is a Closure which receives a Blueprint object that may be used to define the new table:

Schema::create('users', function (Blueprint $table) {
$table->id();
$table->bigIncrements('id');
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});

Schema::create('users', function($table)
{
$table->increments('id');
});

The first argument passed to the create method is the name of the table, and the second is a Closure which will receive a Blueprint object which may be used to define the new table.

Checking For Table / Column Existence

You may check for the existence of a table or column using the hasTable and hasColumn methods:

if (Schema::hasTable('users')) {
//
}

if (Schema::hasColumn('users', 'email')) {
//
}

Database Connection & Table Options

If you want to perform a schema operation on a database connection that is not your default connection, use the connection method:

Schema::connection('itechxpert')->create('users', function (Blueprint $table) {
$table->id();
});

Renaming / Dropping Tables

To rename an existing database table, use the rename method:

Schema::rename($from, $to);

To drop an existing table, you may use the drop or dropIfExists methods:

Schema::drop('users');
Schema::dropIfExists('users');

Creating Columns (Adding Column in table)

The table method on the Schema facade may be used to update existing tables.

the table method accepts two arguments: the name of the table and a Closure that receives a Blueprint instance you may use to add columns to the table:

Schema::table('users', function (Blueprint $table) {
$table->string('email');
});

How to create foregin key with relation.

public function up()
{
Schema::create('meals', function (Blueprint $table) {
$table->increments('id');
$table->unsignedBigInteger('user_id'); //changed this line
$table->unsignedBigInteger('category_id'); //changed this line
$table->string('title');
$table->string('body');
$table->string('meal_av');
$table->timestamps();

$table->foreign('user_id')
->references('id')
->on('users')
->onDelete('cascade');

$table->foreign('category_id')
->references('id')
->on('categories')
->onDelete('cascade');
});
}

SQLSTATE[HY000]: General error: 1005 Ca
n't create table `grocery`.`#sql-4bb0_c1` (errno: 150 "Foreign key constraint is
incorrectly formed") (SQL: alter table `subcategories` add constraint `subcateg
ories_category_id_foreign` foreign key (`category_id`) references `categories` (
`id`) on delete cascade)

Schema::create('projects', function (Blueprint $table) {
$table->bigIncrements('id');
$table->unsignedInteger('owner_id');
$table->string('title');
$table->text('description');
$table->timestamps();

$table->foreign('owner_id')->references('id')->on('users')->onDelete('cascade');
});

its show foreign key error

Solution :-

Schema::create('projects', function (Blueprint $table) {
$table->bigIncrements('id');
$table->unsignedInteger('owner_id')->index();
$table->string('title');
$table->text('description');
$table->timestamps();
$table->foreign('owner_id')->references('id')->on('users')->onDelete('cascade');
});

perfectly run.

In case of foreign keys, the referenced and referencing fields must have exactly the same data type.
You create the id fields in both users and firms as signed integers. However, you create both foreign keys as unsigned integers, therefore the creation of the keys fail. You need to either add the unsigned clause to the id field definitions, or remove the unsigned clause from the foreign key fields.

just add ->unsigned()->index() at the end of the foreign key and it will work.

Available Column Types

$table->id(); Alias of $table->bigIncrements('id').
$table->foreignId('user_id'); Alias of $table->unsignedBigInteger('user_id').
$table->bigIncrements('id'); Auto-incrementing UNSIGNED BIGINT (primary key) equivalent column.
$table->bigInteger('votes'); BIGINT equivalent column.
$table->binary('data'); BLOB equivalent column.
$table->boolean('confirmed'); BOOLEAN equivalent column.
$table->char('name', 100); CHAR equivalent column with a length.
$table->date('created_at'); DATE equivalent column.
$table->dateTime('created_at', 0); DATETIME equivalent column with precision (total digits).
$table->dateTimeTz('created_at', 0); DATETIME (with timezone) equivalent column with precision (total digits).
$table->decimal('amount', 8, 2); DECIMAL equivalent column with precision (total digits) and scale (decimal digits).
$table->double('amount', 8, 2); DOUBLE equivalent column with precision (total digits) and scale (decimal digits).
$table->enum('level', ['easy', 'hard']); ENUM equivalent column.
$table->float('amount', 8, 2); FLOAT equivalent column with a precision (total digits) and scale (decimal digits).
$table->geometry('positions'); GEOMETRY equivalent column.
$table->geometryCollection('positions'); GEOMETRYCOLLECTION equivalent column.
$table->increments('id'); Auto-incrementing UNSIGNED INTEGER (primary key) equivalent column.
$table->integer('votes'); INTEGER equivalent column.
$table->ipAddress('visitor'); IP address equivalent column.
$table->json('options'); JSON equivalent column.
$table->jsonb('options'); JSONB equivalent column.
$table->lineString('positions'); LINESTRING equivalent column.
$table->longText('description'); LONGTEXT equivalent column.
$table->macAddress('device'); MAC address equivalent column.
$table->mediumIncrements('id'); Auto-incrementing UNSIGNED MEDIUMINT (primary key) equivalent column.
$table->mediumInteger('votes'); MEDIUMINT equivalent column.
$table->mediumText('description'); MEDIUMTEXT equivalent column.
$table->morphs('taggable'); Adds taggable_id UNSIGNED BIGINT and taggable_type VARCHAR equivalent columns.
$table->uuidMorphs('taggable'); Adds taggable_id CHAR(36) and taggable_type VARCHAR(255) UUID equivalent columns.
$table->multiLineString('positions'); MULTILINESTRING equivalent column.
$table->multiPoint('positions'); MULTIPOINT equivalent column.
$table->multiPolygon('positions'); MULTIPOLYGON equivalent column.
$table->nullableMorphs('taggable'); Adds nullable versions of morphs() columns.
$table->nullableUuidMorphs('taggable'); Adds nullable versions of uuidMorphs() columns.
$table->nullableTimestamps(0); Alias of timestamps() method.
$table->point('position'); POINT equivalent column.
$table->polygon('positions'); POLYGON equivalent column.
$table->rememberToken(); Adds a nullable remember_token VARCHAR(100) equivalent column.
$table->set('flavors', ['strawberry', 'vanilla']); SET equivalent column.
$table->smallIncrements('id'); Auto-incrementing UNSIGNED SMALLINT (primary key) equivalent column.
$table->smallInteger('votes'); SMALLINT equivalent column.
$table->softDeletes('deleted_at', 0); Adds a nullable deleted_at TIMESTAMP equivalent column for soft deletes with precision (total digits).
$table->softDeletesTz('deleted_at', 0); Adds a nullable deleted_at TIMESTAMP (with timezone) equivalent column for soft deletes with precision (total digits).
$table->string('name', 100); VARCHAR equivalent column with a length.
$table->text('description'); TEXT equivalent column.
$table->time('sunrise', 0); TIME equivalent column with precision (total digits).
$table->timeTz('sunrise', 0); TIME (with timezone) equivalent column with precision (total digits).
$table->timestamp('added_on', 0); TIMESTAMP equivalent column with precision (total digits).
$table->timestampTz('added_on', 0); TIMESTAMP (with timezone) equivalent column with precision (total digits).
$table->timestamps(0); Adds nullable created_at and updated_at TIMESTAMP equivalent columns with precision (total digits).
$table->timestampsTz(0); Adds nullable created_at and updated_at TIMESTAMP (with timezone) equivalent columns with precision (total digits).
$table->tinyIncrements('id'); Auto-incrementing UNSIGNED TINYINT (primary key) equivalent column.
$table->tinyInteger('votes'); TINYINT equivalent column.
$table->unsignedBigInteger('votes'); UNSIGNED BIGINT equivalent column.
$table->unsignedDecimal('amount', 8, 2); UNSIGNED DECIMAL equivalent column with a precision (total digits) and scale (decimal digits).
$table->unsignedInteger('votes'); UNSIGNED INTEGER equivalent column.
$table->unsignedMediumInteger('votes'); UNSIGNED MEDIUMINT equivalent column.
$table->unsignedSmallInteger('votes'); UNSIGNED SMALLINT equivalent column.
$table->unsignedTinyInteger('votes'); UNSIGNED TINYINT equivalent column.
$table->uuid('id'); UUID equivalent column.
$table->year('birth_year'); YEAR equivalent column.

Column Modifiers

there are several column "modifiers" you may use while adding a column to a database table. For example, to make the column "nullable", you may use the nullable method:

Schema::table('users', function (Blueprint $table) {
$table->string('email')->nullable();
});

Modifier Description
->after('column') Place the column "after" another column (MySQL)
->autoIncrement() Set INTEGER columns as auto-increment (primary key)
->charset('utf8') Specify a character set for the column (MySQL)
->collation('utf8_unicode_ci') Specify a collation for the column (MySQL/PostgreSQL/SQL Server)
->comment('my comment') Add a comment to a column (MySQL/PostgreSQL)
->default($value) Specify a "default" value for the column
->first() Place the column "first" in the table (MySQL)
->nullable($value = true) Allows (by default) NULL values to be inserted into the column
->storedAs($expression) Create a stored generated column (MySQL)
->unsigned() Set INTEGER columns as UNSIGNED (MySQL)
->useCurrent() Set TIMESTAMP columns to use CURRENT_TIMESTAMP as default value
->virtualAs($expression) Create a virtual generated column (MySQL)
->generatedAs($expression) Create an identity column with specified sequence options (PostgreSQL)
->always() Defines the precedence of sequence values over input for an identity column (PostgreSQL)

How to fetch data

$users = User::with('posts')->get();
foreach($users as $user){
$users->posts; // posts is already loaded and no additional DB query is run
}

here User is the Model name and with() is the function to pass database table name and finally get() method is get all data of table.

what is with()

with() return the model object and its related results.
with() is for eager loading the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of one for every model in the collection.

what is has()

$users = User::has('posts')->get();
// only users that have at least one post are contained in the collection

has() is to filter the selecting model based on a relationship. So it acts very similarly to a normal WHERE condition. If you just use has('relation') that means you only want to get the models that have at least one related model in this relation.

what is WhereHas()

$users = User::whereHas('posts', function($q){
$q->where('created_at', '>=', '2015-01-01 00:00:00');
})->get();
// only users that have posts from 2015 on forward are returned
whereHas() works basically the same as has() but allows you to specify additional filters for the related model to check.

Note :

Order::with('orderItems')->get();
Laravel change this code to only two SQL:

// get all orders:
SELECT * FROM orders;

// get the order_items based on the orders' id above
SELECT * FROM order_items WHERE order_items.order_id IN (1,2,3,4...);

And then laravel merge the results of the second SQL as different from the results of the first SQL by foreign key. At last return the collection results.

So if you selected columns without the foreign_key in closure, the relationship result will be empty:

Order::with(['orderItems' => function($query) {
// $query->sum('quantity');
$query->select('quantity'); // without `order_id`
}
])->get();

#=> result:
[{ id: 1,
code: '00001',
orderItems: [], // <== is empty
},{
id: 2,
code: '00002',
orderItems: [], // <== is empty
}...
}]

Has

Has will return the model's object that its relationship is not empty.

Order::has('orderItems')->get();
Laravel change this code to one SQL:

select * from `orders` where exists (
select * from `order_items` where `order`.`id` = `order_item`.`order_id`
)

whereHas

whereHas and orWhereHas methods to put where conditions on your has queries. These methods allow you to add customized constraints to a relationship constraint.

Order::whereHas('orderItems', function($query) {
$query->where('status', 1);
})->get();
Laravel change this code to one SQL:

select * from `orders` where exists (
select *
from `order_items`
where `orders`.`id` = `order_items`.`order_id` and `status` = 1
)

Modifying Columns

Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file. The Doctrine DBAL library is used to determine the current state of the column and create the SQL queries needed to make the required adjustments

composer require doctrine/dbal

Updating Column Attributes

The change method allows you to modify type and attributes of existing columns. For example, you may wish to increase the size of a string column.

Schema::table('users', function (Blueprint $table) {
$table->string('name', 50)->change();
});

We could also modify a column to be nullable:

Schema::table('users', function (Blueprint $table) {
$table->string('name', 50)->nullable()->change();
});

Renaming Columns

To rename a column, you may use the renameColumn method on the schema builder. Before renaming a column, be sure to add the doctrine/dbal dependency to your composer.json file:

Schema::table('users', function (Blueprint $table) {
$table->renameColumn('from', 'to');
});

Dropping Columns

To drop a column, use the dropColumn method on the schema builder. Before dropping columns from a SQLite database, you will need to add the doctrine/dbal dependency to your composer.json file and run the composer update command in your terminal to install the library:

Schema::table('users', function (Blueprint $table) {
$table->dropColumn('votes');
});

You may drop multiple columns from a table by passing an array of column names to the dropColumn method:

Schema::table('users', function (Blueprint $table) {
$table->dropColumn(['votes', 'avatar', 'location']);
});

Command Description
$table->dropMorphs('morphable'); Drop the morphable_id and morphable_type columns.
$table->dropRememberToken(); Drop the remember_token column.
$table->dropSoftDeletes(); Drop the deleted_at column.
$table->dropSoftDeletesTz(); Alias of dropSoftDeletes() method.
$table->dropTimestamps(); Drop the created_at and updated_at columns.
$table->dropTimestampsTz(); Alias of dropTimestamps() method.

Adding Indexes

The Laravel schema builder supports several types of indexes. The following example creates a new email column and specifies that its values should be unique. To create the index, we can chain the unique method onto the column definition

$table->string('email')->unique();

Alternatively, you may create the index after defining the column. For example:
$table->unique('email');

Available Index Types

Each index method accepts an optional second argument to specify the name of the index

$table->primary('id'); Adds a primary key.
$table->primary(['id', 'parent_id']); Adds composite keys.
$table->unique('email'); Adds a unique index.
$table->index('state'); Adds a plain index.
$table->spatialIndex('location'); Adds a spatial index. (except SQLite)

Dropping Indexes

To drop an index, you must specify the index's name. By default, Laravel automatically assigns an index name based on the table name, the name of the indexed column, and the index type.

$table->dropPrimary('users_id_primary'); Drop a primary key from the "users" table.
$table->dropUnique('users_email_unique'); Drop a unique index from the "users" table.
$table->dropIndex('geo_state_index'); Drop a basic index from the "geo" table.
$table->dropSpatialIndex('geo_location_spatialindex'); Drop a spatial index from the "geo" table (except SQLite).

Foreign Key Constraints

Laravel support for creating foreign key constraints, which are used to force referential integrity at the database level. For example, let's define a user_id column on the posts table that references the id column on a users table

Schema::table('posts', function (Blueprint $table) {
$table->unsignedBigInteger('user_id');

$table->foreign('user_id')->references('id')->on('users');
});

Schema::table('posts', function (Blueprint $table) { $table->foreignId('user_id')->constrained(); });

The foreignId method is an alias for unsignedBigInteger while the constrained method will use convention to determine the table and column name being referenced.

drop a foreign key

To drop a foreign key, you may use the dropForeign method. A similar naming convention is used for foreign keys as is used for other indexes:

$table->dropForeign('posts_user_id_foreign');

How to display query log

print last executed query in laravel for debug. if you want to see what last query run. then you can print last eloquent query in laravel. using toSql(), DB::enableQueryLog() and DB::getQueryLog().

$query = User::select("*")->toSql();
dd($query);

Output :-

select * from `users`

Example 2

DB::enableQueryLog();
$users = User::select("*")->get();
$quries = DB::getQueryLog();
dd($quries);

Output :-

array:1 [▼

0 => array:3 [▼

"query" => "select * from `users`"

"bindings" => []

"time" => 4.25

]

]

Example 3

DB::enableQueryLog();

$users = User::select("*")->get();
$query = DB::getQueryLog();
$query = end($query);
dd($query);

Output :-

array:3 [▼

"query" => "select * from `users`"

"bindings" => []

"time" => 2.07

]

How to insert data in table.

$first_name = $request->input('first_name');
$last_name = $request->input('last_name');
$city_name = $request->input('city_name');
$email = $request->input('email');
$data=array('first_name'=>$first_name,"last_name"=>$last_name,"city_name"=>$city_name,"email"=>$email);
DB::table('student')->insert($data);

public function insert(Request $request) {
$name = $request->input('stud_name');
DB::insert('insert into student (name) values(?)',[$name]);
echo "Record inserted successfully.<br/>";
echo '<a href = "/insert">Click Here</a> to go back.';
}

Retrieve Records

public function retrive() {
$users = DB::select('select * from student');
return view('stud_view',['users'=>$users]);
}

<table border = 1>
<tr>
<td>ID</td>
<td>Name</td>
</tr>
@foreach ($users as $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->name }}</td>
</tr>
@endforeach
</table>

retrive record from table in laravel.

File name : index.php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;
class StudViewController extends Controller {
public function index(){
$users = DB::select('select * from student_details');
return view('stud_view',['users'=>$users]);
}
}

route

File name : index.php

Route::get('view-records','StudViewController@index');

views

File name : index.php

<table border = "1">
<tr>
<td>Id</td>
<td>First Name</td>
<td>Last Name</td>
<td>City Name</td>
<td>Email</td>
</tr>
@foreach ($users as $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->first_name }}</td>
<td>{{ $user->last_name }}</td>
<td>{{ $user->city_name }}</td>
<td>{{ $user->email }}</td>
</tr>
@endforeach
</table>

How to delete data from database using Laravel

File name : index.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudDeleteController extends Controller {
public function index() {
$users = DB::select('select * from student');
return view('stud_delete_view',['users'=>$users]);
}
public function destroy($id) {
DB::delete('delete from student where id = ?',[$id]);
echo "Record deleted successfully.<br/>";
echo '<a href = "/delete-records">Click Here</a> to go back.';
}
}

views

File name : index.php

<table border = "1">
<tr>
<td>ID</td>
<td>Name</td>
<td>Edit</td>
</tr>
@foreach ($users as $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->name }}</td>
<td><a href = 'delete/{{ $user->id }}'>Delete</a></td>
</tr>
@endforeach
</table>

Update Records

File name : index.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;
class StudUpdateController extends Controller {
public function index(){
$users = DB::select('select * from student');
return view('stud_edit_view',['users'=>$users]);
}
public function show($id) {
$users = DB::select('select * from student where id = ?',[$id]);
return view('stud_update',['users'=>$users]);
}
public function edit(Request $request,$id) {
$first_name = $request->input('first_name');
$last_name = $request->input('last_name');
$city_name = $request->input('city_name');
$email = $request->input('email');
//$data=array('first_name'=>$first_name,"last_name"=>$last_name,"city_name"=>$city_name,"email"=>$email);
//DB::table('student')->update($data);
// DB::table('student')->whereIn('id', $id)->update($request->all());
DB::update('update student set first_name = ?,last_name=?,city_name=?,email=? where id = ?',[$first_name,$last_name,$city_name,$email,$id]);
echo "Record updated successfully.
";
echo 'Click Here to go back.';
}
}

views

File name : stud_edit_view.blade.php

<table border = "1">
<tr>
<td>ID</td>
<td>First Name</td>
<td>Lastst Name</td>
<td>City Name</td>
<td>Email</td>
<td>Edit</td>
</tr>
@foreach ($users as $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->first_name }}</td>
<td>{{ $user->last_name }}</td>
<td>{{ $user->city_name }}</td>
<td>{{ $user->email }}</td>
<td><a href = 'edit/{{ $user->id }}'>Edit</a></td>
</tr>
@endforeach
</table>

File name : stud_update.php

<!DOCTYPE html>
<html>
<head>
<title>Student Management | Edit</title>
</head>
<body>
<form action = "/edit/<?php echo $users[0]->id; ?>" method = "post">
<input type = "hidden" name = "_token" value = "<?php echo csrf_token(); ?>">
<table>
<tr>
<td>First Name</td>
<td>
<input type = 'text' name = 'first_name'
value = '<?php echo$users[0]->first_name; ?>'/> </td>
</tr>
<tr>
<td>Last Name</td>
<td>
<input type = 'text' name = 'last_name'
value = '<?php echo$users[0]->last_name; ?>'/>
</td>
</tr>
<tr>
<td>City Name</td>
<td>
<input type = 'text' name = 'city_name'
value = '<?php echo$users[0]->city_name; ?>'/>
</td>
</tr>
<tr>
<td>Email</td>
<td>
<input type = 'text' name = 'email'
value = '<?php echo$users[0]->email; ?>'/>
</td>
</tr>
<tr>
<td colspan = '2'>
<input type = 'submit' value = "Update student" />
</td>
</tr>
</table>
</form>
</body>
</html>

route

File name : index.php

Route::get('edit-records','StudUpdateController@index');
Route::get('edit/{id}','StudUpdateController@show');
Route::post('edit/{id}','StudUpdateController@edit');

File name : index.php

Running A Select Query

To run a basic query, you may use the select method on the DB facade:

File name : UserController.php

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
public function index()
{
$users = DB::select('select * from users where active = ?', [1]);

return view('user.index', ['users' => $users]);
}
}

The select method will always return an array of results. Each result within the array will be a PHP stdClass object, allowing you to access the values of the results:

foreach ($users as $user) {
echo $user->name;
}

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Insert Statement

To execute an insert statement, you may use the insert method on the DB facade.

DB::insert('insert into users (id, name) values (?, ?)', [1, 'itechxpert']);

Update Statement

The update method should be used to update existing records in the database. The number of rows affected by the statement will be returned:

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);

Delete Statement

The delete method should be used to delete records from the database.

$deleted = DB::delete('delete from users');

General Statement

Some database statements do not return any value. For these types of operations, you may use the statement method on the DB facade:

DB::statement('drop table users');

Database Transactions

You may use the transaction method on the DB facade to run a set of operations within a database transaction. If an exception is thrown within the transaction Closure, the transaction will automatically be rolled back. If the Closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);

DB::table('posts')->delete();
});

Manually Using Transactions

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method on the DB facade:

DB::beginTransaction();
You can rollback the transaction via the rollBack method:

DB::rollBack();
Lastly, you can commit a transaction via the commit method:

DB::commit();

Running A Select Query

$results = DB::select('select * from users where id = ?', array(1));

Running An Insert Statement

DB::insert('insert into users (id, name) values (?, ?)', array(1, 'Dayle'));

Running An Update Statement

DB::update('update users set votes = 100 where name = ?', array('John'));

Running A Delete Statement

DB::delete('delete from users');

Retrieving Results

Retrieving All Rows From A Table

You may use the table method on the DB facade to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get method:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
/**
* Show a list of all of the application's users.
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();

return view('user.index', ['users' => $users]);
}
}

The get method returns an Illuminate\Support\Collection containing the results where each result is an instance of the PHP stdClass object. You may access each column's value by accessing the column as a property of the object:

foreach ($users as $user) {
echo $user->name;
}

Retrieving A Single Row / Column From A Table

If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single stdClass object:

$user = DB::table('users')->where('name', 'itechxpert')->first();

echo $user->name;

If you don't even 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');

To retrieve a single row by its id column value, use the find method:

$user = DB::table('users')->find(3);

Retrieving A List Of Column Values

If you would like to retrieve a Collection containing the values of a single column, you may use the pluck method. In this example, we'll retrieve a Collection of role titles:

$titles = DB::table('roles')->pluck('title');

foreach ($titles as $title) {
echo $title;
}

Selects

$users = DB::select('select * from users where active = ?', [1]);
return view('user.index', ['users' => $users]);

Retrieving All Rows From A Table

$users = DB::table('users')->get();

foreach ($users as $user)
{
var_dump($user->name);
}

Retrieving A Single Row From A Table

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

var_dump($user->name);

Retrieving A Single Column From A Row

$name = DB::table('users')->where('name', 'itechxpert')->pluck('name');

Retrieving A List Of Column Values

$roles = DB::table('roles')->lists('title');
Note : if lists not work then use pluck() method.

This method will return an array of role titles. You may also specify a custom key column for the returned array:

$roles = DB::table('roles')->lists('title', 'name');

Specifying A Select Clause

$users = DB::table('users')->select('name', 'email')->get();

$users = DB::table('users')->distinct()->get();

$users = DB::table('users')->select('name as user_name')->get();

Adding A Select Clause To An Existing Query

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

Using Where Operators

$users = DB::table('users')->where('votes', '>', 100)->get();

Or Statements

$users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get();

Using Where Between

$users = DB::table('users') ->whereBetween('votes', array(1, 100))->get();

Using Where Not Between

$users = DB::table('users') ->whereNotBetween('votes', array(1, 100))->get();

Using Where In With An Array

$users = DB::table('users')
->whereIn('id', array(1, 2, 3))->get();

$users = DB::table('users')
->whereNotIn('id', array(1, 2, 3))->get();

Using Where Null To Find Records With Unset Values

$users = DB::table('users') ->whereNull('updated_at')->get();

Order By, Group By, And Having

$users = DB::table('users') ->orderBy('name', 'desc') ->groupBy('count') ->having('count', '>', 100) ->get();

Offset & Limit

$users = DB::table('users')->skip(10)->take(5)->get();

Advanced Wheres

Parameter Grouping

DB::table('users')
->where('name', '=', 'John')
->orWhere(function($query)
{
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();

The query above will produce the following SQL:

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Exists Statements

DB::table('users')
->whereExists(function($query)
{
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
The query above will produce the following SQL:

select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)

Inserting Records Into A Table

DB::table('users')->insert( array('email' => 'mahi@example.com', 'votes' => 0) );

Inserting Records Into A Table With An Auto-Incrementing ID

If the table has an auto-incrementing id, use insertGetId to insert a record and retrieve the id:

$id = DB::table('users')->insertGetId(
array('email' => 'john@example.com', 'votes' => 0)
);

Inserting Multiple Records Into A Table

DB::table('users')->insert(array(
array('email' => 'itechxpert@example.com', 'votes' => 0),
array('email' => 'Nusrat@bano.com', 'votes' => 0),
));

Updating Records In A Table

DB::table('users') ->where('id', 1) ->update(array('votes' => 1));

Deletes

DB::table('users')->where('votes', '<', 100)->delete();

Deleting All Records From A Table

DB::table('users')->delete();

Truncating A Table

DB::table('users')->truncate();

Unions

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get();

Joins

Basic Join Statement

DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'contacts.phone', 'orders.price')
->get();

Left Join Statement

DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get();

You may also specify more advanced join clauses:

DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();

DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();

Get single column values of a table using Query Builder

$items = DB::table('items')
->select('id', 'ref_code', 'name', 'price')
->where('ref_code','=', $request->ref_code)
->first();

Or

$item = YourModelName::select('id', 'ref_code', 'name', 'price')
->where('ref_code','=', $request->ref_code)
->first();

And finally get output as like $item->id, $item->ref_code

foreach ($items as $item) {
$item->price;
}

Itechtuto

Laravel Tutorials

Important Links

database

Output :-

Output :-

Output :-

How to insert data in table.

Retrieve Records

retrive record from table in laravel.

File name : index.php

route

File name : index.php

views

File name : index.php

How to delete data from database using Laravel

File name : index.php

views

File name : index.php

Update Records

File name : index.php

views

File name : stud_edit_view.blade.php

File name : stud_update.php

route

File name : index.php

File name : index.php

Running A Select Query

File name : UserController.php

Using Named Bindings

Insert Statement

Update Statement

Delete Statement

General Statement

Database Transactions

Manually Using Transactions

Retrieving Results

Retrieving A Single Row / Column From A Table

Retrieving A List Of Column Values

Connect Us Socially:

Quick Links