How to unpivot multiple columns in same table

I have a table with below structure, I need to unpivot the output so that I get one row per ID per PARAMETER and it's corresponding RATINGS

create table RATINGS(ID INT,PARAMETER1 INT,PARAMETER2 INT,PARAMETER3 INT,PARAMETER4 INT)
insert into RATINGS values(1000,1,3,2,1)
insert into RATINGS values(1002,2,3,3,2)
insert into RATINGS values(1007,3,3,2,1)
insert into RATINGS values(1015,1,3,1,3)
insert into RATINGS values(1019,3,2,1,1)

expected output:

ID    PARAMETERS RATING
1000  PARAMETER1  1
1000  PARAMETER2  3
1000  PARAMETER3  2
1000  PARAMETER4  1
1002  PARAMETER1  2
1002  PARAMETER2  3
1002  PARAMETER3  3
1002  PARAMETER4  2
1007  PARAMETER1  3
1007  PARAMETER2  3
1007  PARAMETER3  2
1007  PARAMETER4  1
1015  PARAMETER1  1
1015  PARAMETER2  3
1015  PARAMETER3  1
1015  PARAMETER4  3
1019  PARAMETER1  3
1019  PARAMETER1  2
1019  PARAMETER1  1
1019  PARAMETER1  1

Later, I also need to filter so that I get only those rows that have ratings 1 and 2. So the output then must be

ID    PARAMETERS RATING
1000  PARAMETER1  1
1000  PARAMETER3  2
1000  PARAMETER4  1
1002  PARAMETER1  2
1002  PARAMETER4  2
1007  PARAMETER3  2
1007  PARAMETER4  1
1015  PARAMETER1  1
1015  PARAMETER3  1
1019  PARAMETER1  2
1019  PARAMETER1  1
1019  PARAMETER1  1

I am able to get the first two ID and PARAMETERS column using below query:

select ID,[parameters] from RATINGS
unpivot
(
[value] for [PARAMETERS] in (PARAMETER1,PARAMETER2,PARAMETER3,PARAMETER4)
) unpvt

OUTPUT:

**ID**      **PARAMETERS**
1000    PARAMETER1
1000    PARAMETER2
1000    PARAMETER3
1000    PARAMETER4
1002    PARAMETER1
1002    PARAMETER2
1002    PARAMETER3
1002    PARAMETER4
1007    PARAMETER1
1007    PARAMETER2
1007    PARAMETER3
1007    PARAMETER4
1015    PARAMETER1
1015    PARAMETER2
1015    PARAMETER3
1015    PARAMETER4
1019    PARAMETER1
1019    PARAMETER2
1019    PARAMETER3
1019    PARAMETER4

Could someone please let me know how to get the RATING column

#sql #sql-server

Multiple File Upload in Laravel 7, 6

in this post, i will show you easy steps for multiple file upload in laravel 7, 6.

As well as how to validate file type, size before uploading to database in laravel.

Laravel 7/6 Multiple File Upload

You can easily upload multiple file with validation in laravel application using the following steps:

Download Laravel Fresh New Setup
Setup Database Credentials
Generate Migration & Model For File
Make Route For File uploading
Create File Controller & Methods
Create Multiple File Blade View
Run Development Server

https://www.tutsmake.com/laravel-6-multiple-file-upload-with-validation-example/

#laravel multiple file upload validation #multiple file upload in laravel 7 #multiple file upload in laravel 6 #upload multiple files laravel 7 #upload multiple files in laravel 6 #upload multiple files php laravel

I am Developer

1597470037

Laravel 7 Multiple Image Upload with Preview

Here, i will show you how to upload multiple image with preview using ajax in laravel.

Laravel 7 Ajax Multiple Image Upload with Preview

Just follow the below steps and upload multiple images using ajax with showing preview in laravel applications:

Install Laravel Fresh Setup
Setup Database Credentials
Create Route
Generate Controller By Command
Create the blade view
Start Development Server

https://www.tutsmake.com/laravel-7-6-ajax-multiple-image-upload-with-preview-e-g/

#laravel multiple image upload with preview #laravel multiple image validation #display multiple images in laravel #laravel multiple file upload #multiple image upload in laravel 6 #ajax image upload and preview with laravel

Fredy Larson

1595209620

How to alter tables in production when records are in millions

As a developer, I have experienced changes in app when it is in production and the records have grown up to millions. In this specific case if you want to alter a column using simple migrations that will not work because of the following reasons:

It is not so easy if your production servers are under heavy load and the database tables have 100 million rows. Because such a migration will run for some seconds or even minutes and the database table can be locked for this time period – a no-go on a zero-downtime environment.

In this specific case you can use MySQL’s algorithms: Online DDL operations. That’s how you can do it in Laravel.

First of all create migration. For example I want to modify a column’s name the traditional migration will be:

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

Run the following command php artisan migrate –pretend this command will not run the migration rather it will print out it’s raw sql:

ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL

Copy that raw sql, remove following code:

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

Replace it with following in migrations up method:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL');

Add desired algorithm, in my case query will look like this:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;');

#laravel #mysql #php #alter heavy tables in production laravel #alter table in production laravel #alter tables with million of records in laravel #how to alter heavy table in production laravel #how to alter table in production larave #mysql online ddl operations

Seamus Quitzon

1595201363

Php how to delete multiple rows through checkbox using ajax in laravel

First thing, we will need a table and i am creating products table for this example. So run the following query to create table.

CREATE TABLE `products` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Next, we will need to insert some dummy records in this table that will be deleted.

INSERT INTO `products` (`name`, `description`) VALUES

('Test product 1', 'Product description example1'),

('Test product 2', 'Product description example2'),

('Test product 3', 'Product description example3'),

('Test product 4', 'Product description example4'),

('Test product 5', 'Product description example5');

Now we are redy to create a model corresponding to this products table. Here we will create Product model. So let’s create a model file Product.php file under app directory and put the code below.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = [
        'name','description'
    ];
}

Step 2: Create Route

Now, in this second step we will create some routes to handle the request for this example. So opeen routes/web.php file and copy the routes as given below.

routes/web.php

Route::get('product', 'ProductController@index');
Route::delete('product/{id}', ['as'=>'product.destroy','uses'=>'ProductController@destroy']);
Route::delete('delete-multiple-product', ['as'=>'product.multiple-delete','uses'=>'ProductController@deleteMultiple']);

#laravel #delete multiple rows in laravel using ajax #laravel ajax delete #laravel ajax multiple checkbox delete #laravel delete multiple rows #laravel delete records using ajax #laravel multiple checkbox delete rows #laravel multiple delete

I am Developer

1597559901

Laravel 7.x/6 Multiple Image Upload Ajax

Here, i will share with you how to multiple image upload in laravel 7, 6 using ajax. And display preview of multiple images before upload in laravel.

Multiple Image Upload With Preview in Laravel 7/6 using Ajax

Upload multiple images using ajax with preview in laravel 7/6 by following the below steps:

Install Laravel Fresh Setup
Setup Database Credentials
Create Multiple Image Upload Route
Generate Image Controller By Command
Create Multiple Image Upload Preview blade view
Start Development Server