php - sum method on query multiple columns based on id -

i working on sports team app , trying sum (total) goals scored each player throughout season. have query follows:

    $gmc = db::table('matchcards')->where('grade_id', $gradeid)->select('id')->get();         foreach($gmc $object)         {             $arrays[] =  (array) $object;         }         $gmcx = collect($arrays)->flatten();      foreach ($gmcx $mc) { //loop 1 - list of matchcards grade          $im = db::table('matchcards')->distinct()->select('capt', 'gk', 'player3', 'player4', 'player5', 'player6', 'player7', 'player8'             , 'player9', 'player10', 'player11', 'player12', 'player13', 'player14', 'player15', 'player16')->where('id', $mc)->get();          foreach ($im $object2) {             $arrays2[] = (array)$object2;         }          $imx = collect($arrays2)->flatten(); //gets playedids above loop     } //end of loop 1          $gp ='0'; $gamesplayed= array(); $collection = collect(); $m='0';         foreach ($imx $p) { //loop 2 - take each id , count games played             $_get['p'] = $p;             $pcount = db::table('matchcards')->where('capt', $p)->where('grade_id', $gradeid)->orwhere('gk', $p)->orwhere('player3', $p)                 ->orwhere('player4', $p)->orwhere('player5', $p)->orwhere('player6', $p)->orwhere('player7', $p)                 ->orwhere('player8', $p)->orwhere('player9', $p)->orwhere('player10', $p)->orwhere('player11', $p)                 ->orwhere('player12', $p)->orwhere('player13', $p)->orwhere('player14', $p)->orwhere('player15', $p)                 ->orwhere('player16', $p)->get();             $gp = count($pcount);             if (count($pcount) > 0){ //get players full name                 $pn = db::table('players')->select('id', 'fname', 'lname')->where('id', $p)->get();                 if (count($pn)>0) {                     $pname = $pn[0]->fname." ".$pn[0]->lname;                 }     $m = db::table('matchcards')                     ->select('p1scorer', 'p1goals','p2scorer', 'p2goals', 'p3scorer', 'p3goals','p4scorer', 'p4goals', 'p5scorer', 'p5goals','p6scorer', 'p6goals')                     ->where('grade_id', '=', $gradeid)                     ->where(function ($query) {                         $p = $_get['p'];                         $query->where('p1scorer', '=', $p)                             ->orwhere('p2scorer', '=', $p)                             ->orwhere('p3scorer', '=', $p)                             ->orwhere('p4scorer', '=', $p)                             ->orwhere('p5scorer', '=', $p)                             ->orwhere('p6scorer', '=', $p);                     })                    ->sum(db::raw('p1goals + p2goals + p3goals + p4goals + p5goals + p6goals'));                    //->select(db::raw('sum(p1goals + p2goals + p3goals + p4goals + p5goals + p6goals ) goals_scored'))->get(); 

}

           //add objects collection/             $collection->push(['id' => $p, 'gp' => $gp, 'pname' => $pname, 'gs' => $m]);          } //end of loop 2          $unique = $collection->unique();  //removed duplicate ids 

it returning people scored - half working. dont know how add goals user (across 6 possible columns). @ moment, query adds goals particular match/game (or row in db) player.

there 6 possible columns each matchcard coaches select 6 players scored (hence, p1scorer , p1goals belong together, , p2scorer , p2goals belong together... each week playerid can in 1 of p1scorer p6scorer columns - never duplicated on same row). scenario example week 1 (row 1) p1scorer 17 (id of player) scored 2 goals (so 2 inside p1goals). p2scorer 5 scored 1 goal - 3 goals in row total - want send array row ( id: 17 goals: 2 } { id: 5 goals: 1 }, , loop through other rows , add goals existing tally each individual scored throughout season.

hoping might able me here... in advance...

you should try this:

use illuminate\http\request; use input;

$p = $request->p; 

or

$p = input::get(p);       $m = db::table('matchcards')                     ->select('p1scorer', 'p1goals','p2scorer', 'p2goals', 'p3scorer', 'p3goals','p4scorer', 'p4goals', 'p5scorer', 'p5goals','p6scorer', 'p6goals')                     ->where('grade_id', '=', $gradeid)                     ->where(function ($query) use($p) {                          $query->where('p1scorer', '=', $p)                             ->orwhere('p2scorer', '=', $p)                             ->orwhere('p3scorer', '=', $p)                             ->orwhere('p4scorer', '=', $p)                             ->orwhere('p5scorer', '=', $p)                             ->orwhere('p6scorer', '=', $p);                     });                     ->sum(db::raw('p1goals + p2goals + p3goals + p4goals + p5goals + p6goals')); 

hope work !!!

wiki