Tag: php

This tutorial is created to Import, Export data from an excel sheet to database in Laravel Framework using maatwebsite/excel package. It provides an easy way to import and export using database model. It will work with Laravel 5.8, 5.7, 5.6.

1. Install Laravel

First, let’s install Laravel using the following command (Make sure, You have installed composer in your PC). Click on Install Composer If you haven’t installed Composer on your PC.

composer create-project --prefer-dist laravel/laravel excelApplication

2. Configure Database Details

You need to create the database at MYSQL and then we need to connect that database to the Laravel project (You can also use phpmyadmin to create the database). Open the .env file inside Laravel project and add the database credentials as below.

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE='Your DB Name'
DB_USERNAME='Your DB UserName'
DB_PASSWORD='Your DB Password'
Code language: JavaScript (javascript)

3. Install Maatwebsite/excel Package

Install Mattwebsite/excel package via the composer manager, use the terminal to run the command below.

composer require maatwebsite/excel
Code language: JavaScript (javascript)

4. Specify the configuration

After installing maatwebsite/excel package, Go to config/app.php file and add the service provider and aliase.

config/app.php

'providers' => [
	....
	Maatwebsite\Excel\ExcelServiceProvider::class,
],

'aliases' => [
	....
	'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Code language: PHP (php)

Type the following command to publish the above configuration settings. If you’re prompt with provide tag files, Choose “Provider: Maatwebsite\Excel\ExcelServiceProvider” to publish.

php artisan vendor:publish
Code language: CSS (css)

5. Create a database model and migration

In this step, we’re going to create a Data model and migration

php artisan make:model Data --migration
Code language: CSS (css)

The migration will be created under “database/migrations”. Edit the file with the code below to create Data Table.

‘date’_create_data_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateDataTable extends Migration
{

public function up()
{
    Schema::create('data', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->text('description');
        $table->timestamps();
    });
}

public function down()
{
    Schema::dropIfExists('data');
}
}
Code language: HTML, XML (xml)

Type the following command to run the migration.

php artisan migrate

Open the generated model in “app/Data.php” Now, Write the schema inside Data.php file.

app/Data.php

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Data extends Model
{
    protected $fillable = ['title','description'];
}
Code language: HTML, XML (xml)

6. Create DataController

Type the following command at your terminal to create DataController.php file in controllers

php artisan make:controller DataController
Code language: CSS (css)

app/Http/Controllers/DataController.php

<?php
namespace App\Http\Controllers;

use App\Data;
use Excel;
use Illuminate\Http\Request;

class DataController extends Controller
{

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('index');
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function downloadData($type)
    {
        $data = Data::get()->toArray();

        return Excel::create('excel_data', function($excel) use ($data) {
            $excel->sheet('mySheet', function($sheet) use ($data)
            {
                $sheet->fromArray($data);
            });
        })->download($type);
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function importData(Request $request)
    {
        $request->validate([
            'import_file' => 'required'
        ]);

        $path = $request->file('import_file')->getRealPath();
        $data = Excel::load($path)->get();

        if($data->count()){
            foreach ($data as $key => $value) {
                $arr[] = ['title' => $value->title, 'description' => $value->description];
            }

            if(!empty($arr)){
                Data::insert($arr);
            }
        }

        return back()->with('success', 'Insert Record successfully.');
    }
}
Code language: HTML, XML (xml)

In this step, Go to ‘routes->web.php’ file, add the following line of code.

Route::get('/', 'DataController@index');
Route::get('downloadData/{type}', 'DataController@downloadData');
Route::post('importData', 'DataController@importData');
Code language: PHP (php)

6. Create the view File

Create index.blade.php in resources/views folder and add the following code.

resources/views/index.blade.php

<html lang="en">
<head>
    <title>Laravel Import Export to Excel , CSV Example - bishrulhaq.com</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<style>
    .container{
        margin-top: 10%;
    }
</style>
<body>
<div class="container">
    @if ($errors->any())
        <div class="alert alert-danger">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <ul>
                @foreach ($errors->all() as $error)
                    <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
    @endif

    @if (Session::has('success'))
        <div class="alert alert-success">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <p>{{ Session::get('success') }}</p>
        </div>
    @endif
    <div class="card">
        <div class="card-body">
            <h5 class="card-title">Laravel Import Export to Excel , CSV Example</h5>
            <a href="{{ url('downloadData/xlsx') }}"><button class="btn btn-dark">Download Excel xlsx</button></a>
            <a href="{{ url('downloadData/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
            <a href="{{ url('downloadData/csv') }}"><button class="btn btn-info">Download CSV</button></a>

            <form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 10px;" action="{{ url('importData') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
                @csrf

                <input type="file" name="import_file" />
                <button class="btn btn-primary">Import File</button>
            </form>

        </div>
    </div>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
</body>
</html>
Code language: HTML, XML (xml)

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.

Latest Posts

• Short Summary : The Obstacle is the wayJanuary 29, 2022
• CRUD Operations In Laravel 8June 14, 2021
• Installing PHP on WindowsMay 6, 2021
• Important functionalities of Pandas in Python : Tricks and FeaturesJuly 22, 2020
• How to install WordPress with Nginx on Ubuntu 18.04May 5, 2020

The tutorial is created to implement facebook login using Socialite in Laravel 6.0, 5.8 , 5.7 and 5.6. Using Facebook Auth to log and register into your Laravel Application. Follow the steps below to implement laravel socialite facebook login.

1. Install Laravel 5.8

First, let install Laravel 5.8 using the following command (Make sure you have installed composer in your PC). Click on Install Composer If you haven’t installed Composer on your PC.

composer create-project --prefer-dist laravel/laravel facebookAuth

2. Configure Database Details

You need to create the database at MYSQL and then we need to connect that database to the Laravel project (You can also use phpmyadmin to create the database). Open the .env file inside Laravel project and add the database credentials as below.

.env

DB_CONNECTION=mysql
DB_HOST='Your DB Host'
DB_PORT='Your DB Port'
DB_DATABASE='Your DB Name'
DB_USERNAME='Your DB UserName'
DB_PASSWORD='Your DB Password'
Code language: JavaScript (javascript)

3. Add Socialite

Go to the terminal and type the following php artisan command to download Socialite Package.

composer require laravel/socialite
Code language: JavaScript (javascript)

After installing the package , Configure the provider and aliases in “config/app.php”. Edit the file with the code below.

config/app.php

'providers' => [
 // Other service providers…
 Laravel\Socialite\SocialiteServiceProvider::class,
 ],

'aliases' => [
 // Other aliases…
 'Socialite' => Laravel\Socialite\Facades\Socialite::class,
 ], 
Code language: PHP (php)

4. Get Secret id and key from Facebook Developer Console

In this step, Configure the CLIENT ID and CLIENT SECRET to add facebook a authentication to your laravel project, Lets begin by opening Developer Console and then create a new App by submitting the name and email.

After creating the App, go to Setting->Advanced and set redirect url as shown below.

Now it’s time to add Facebook Login by navigating to products in the left menu.

In this Step, Specify Auth redirect url facebook login->setting and set valid auth redirect url.

After successfully configuring the app on facebook developer console, Set the client id and client secret in config/service.php as shown below

config/service.php

'facebook' => [
     'client_id' => 'xxxx',
     'client_secret' => 'xxx',
     'redirect' => 'https://bishrulhaq.com/callback/facebook',
   ], 
Code language: PHP (php)

5. Create route, controller and model

In this step, Go to app/User.php file and add the following line of code.

app/User.php

protected $fillable = ['name', 'email', 'password', 'provider', 'provider_id' ];
Code language: PHP (php)

In this step, Go to app/database/create_users_table.php and add the code below

app/database/create_users_table.php

<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique()->nullable();
            $table->string('provider');
            $table->string('provider_id');
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password')->nullable();
            $table->rememberToken()->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}
Code language: HTML, XML (xml)

Now it’s time to run Laravel Default Auth by typing the command at your terminal.

php artisan make:auth
Code language: CSS (css)

Next, You need to type the following command to run the migration.

php artisan migrate

If you encounter any errors while migrating the table. Add String length under boot method at app/providers/AppServiceProvider.php file and re-run the migration.

use Illuminate\Support\Facades\Schema;

 function boot()
 {
     Schema::defaultStringLength(191);
 }
Code language: CSS (css)

In this step, Go to ‘routes->web.php’ file, add the following line of code.

routes->web.php

Route::get('/auth/redirect/{provider}', 'AuthController@redirect');
Route::get('/callback/{provider}', 'AuthController@callback');
Code language: PHP (php)

Create AuthController by typing the following command.

php artisan make:controller AuthController
Code language: CSS (css)

<?php
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Validator, Redirect, Response, File;
use Socialite;
use App\User;

class AuthController extends Controller
{
    public function redirect($provider)
    {
        return Socialite::driver($provider)->redirect();
    }

    public function callback($provider)
    {
        $getInfo = Socialite::driver($provider)->user();
        $user = $this->createUser($getInfo, $provider);
        auth()->login($user);
        return redirect()->to('/home');
    }

    function createUser($getInfo, $provider)
    {
        $user = User::where('provider_id', $getInfo->id)->first();
        if (!$user) {
            $user = User::create([
                'name' => $getInfo->name,
                'email' => $getInfo->email,
                'provider' => $provider,
                'provider_id' => $getInfo->id
            ]);
        }
        return $user;
    }
}
Code language: HTML, XML (xml)

6. Edit the View

Go to Resources/Views/Auth/login.blade.php and add facebook social login button under the login area.

Resources/Views/Auth/login.blade.php

<hr>
<div class="form-group row mb-0">
 <div class="col-md-8 offset-md-4">
    <a href="{{ url('/auth/redirect/facebook') }}" class="btn btn-primary"><i class="fa fa-facebook"></i> Facebook</a>
 </div>
</div>
Code language: HTML, XML (xml)

Finally, Laravel Facebook Auth Application is ready to run. Type the following command in your terminal to run the application.

php artisan serve

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.

Latest Posts

• Short Summary : The Obstacle is the wayJanuary 29, 2022
• CRUD Operations In Laravel 8June 14, 2021
• Installing PHP on WindowsMay 6, 2021
• Important functionalities of Pandas in Python : Tricks and FeaturesJuly 22, 2020
• How to install WordPress with Nginx on Ubuntu 18.04May 5, 2020

ORM (Object-relational mapping) is used to make database CRUD operations easier. Laravel comes with Eloquent ORM. This tutorial is created to provide some of the frequently used cheat sheet for Laravel Eloquent ORM

1. Ordering Eloquent hasMany() relationship

Add ->orderBy() to the hasMany relationship to get ordered output of a specified column.

return $this->hasMany('Detail::class')->orderBy('column');
Code language: PHP (php)

2. Eloquent’s where() method

Here are some useful cheat sheet for eloquent’s where() method.

$detail = Detail::where("id","!=",50)->get();
// Any of the following may be used as the second parameter (and use the third param for the value)
// =, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, BETWEEN, ILIKE

$detail = Detail::where(function ($query) {
  $query->where('a', '=', 1)
      ->orWhere('b', '=', 1);
})->get();

$detail = Detail::whereRaw('age > ? and votes = 100', array(25))->get();

$detail = Detail::whereRaw(DB::raw("id in (select detail_id from students GROUP BY students.detail_id)"))->get();

$detail = Detail::whereExists(function($query){
  $query->select(DB::raw(1))
      ->from('students')
      ->whereRaw('students.detail_id = details.id')
      ->groupBy('students.detail_id')
      ->havingRaw("COUNT(*) > 0");
})->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereExists(), ->whereNotExists(), ->orWhereNotExists()

$detail = Detail::whereIn('column',[1,2,3])->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereIn(),

$detail = Detail::whereNotIn('id', function($query){
  $query->select('student_id')
  ->from('students')
  ->groupBy('students.student_id');
})->get();

// Any of the following may be used instead of Detail::whereExists
// ->whereNotIn(), ->orWhereNotIn
Code language: PHP (php)

Here are some more useful cheat sheet for NULL or NOT NULL in laravel eloquent.

->whereNull('column')
->orWhereNull('column')
->whereNotNull('column')
->orWhereNotNull('column')
Code language: PHP (php)

Cheat sheet to filter by Day, Month, Year, Date options.

->whereDay()
->whereMonth('column', '=', 1)
->whereYear('column', '>', 2019)
->whereDate('column', '>', '2019-05-05')
Code language: PHP (php)

3. Prevent Eloquent from adding created_at or updated_at timestamps

Disable both created_at and updated_at in the model to disable the timestamps

const UPDATED_AT = null;
const CREATED_AT = null;
Code language: JavaScript (javascript)

Make sure to remove this from the migration

$table->timestamps()
Code language: PHP (php)

4. restore() soft deleted Eloquent

Use the restore() method to undelete a record.

User::withTrashed()->where("id",1)->restore()
Code language: PHP (php)

Make sure to add following lines in the model to enable SoftDeletes.

 use SoftDeletes;
Code language: PHP (php)

5. Joins in Eloquent

Here are some useful cheat sheet for eloquent’s join() method.

$product = Product:where('id', $productId)
    ->join('businesses','product.business_id','=','businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->leftJoin('businesses','product.business_id', '=', 'businesses.id')
    ->select('product.id','businesses.name')->first();

$product = Product:where('id', $productId)
    ->join('businesses',function($join) use($cats) {
      $join->on('product.business_id', '=', 'businesses.id')
    ->on('product.id', '=', $cats, 'and', true);})->first();
Code language: PHP (php)

6. Find an item by Primary Key in Eloquent, or throw a ModelNotFoundException

findOrFail($id) method will find a model by it’s primary key or throw an exception if it’s not available.

$id = 10001;
$user = User::findOrFail($id);
Code language: PHP (php)

7. Cache in Eloquent

You can retrieve an item from the cache without loading it all the item.

$details = Cache::remember('details', $seconds, function () {
    return DB::table('details')->get();
});
Code language: PHP (php)

rememberForever method is used to retrieve an item from the cache or store it forever

$details = Cache::rememberForever('details', function () {
    return DB::table('details')->get();
});
Code language: PHP (php)

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.

Latest Posts

• Short Summary : The Obstacle is the wayJanuary 29, 2022
• CRUD Operations In Laravel 8June 14, 2021
• Installing PHP on WindowsMay 6, 2021
• Important functionalities of Pandas in Python : Tricks and FeaturesJuly 22, 2020
• How to install WordPress with Nginx on Ubuntu 18.04May 5, 2020