PHP : Pivot Logic Make Columns from Row without crosstab query

PHP : Pivot Logic Make Columns from Row without crosstab query
I have Table like this

I Want to get data like this

Here Is the code for doing this...

1. Get Table in to Two Dimension Array

              $result = mysql_query($sql);
if (mysql_num_rows($result)==0)
echo 'no rows found ' . $sql;

while ($rows = mysql_fetch_array($result))
  //echo $rows[0]. $rows[1] .$rows[2].$rows[3]. "<br/>";
  $row[] = $rows;


2. Now apply the pivot logic in this 2 dimensional array to get desired columns:

//scope of local variables
$j=0;//row of $view
$k=0; //row of $row
while ($k<$i)

$projectname = $row[$k]['project'] . ' U#'. $row[$k]['unit'];
if(strcmp($lastrowprojectname, $projectname) !=0)
 $j++; // get new row in array if project changed

$view[$j]['c1'] = $projectname;
$view[$j]['c2'] = $row[$k][capacity];

$milestone = $row[$k]['milestone'];
$a_date = $row[$k]['month'];
//echo '[' . $a_date .$milestone . ']';
//Pivot logic . Projects shall be missing if milestone case  dosent match
    if (strcasecmp($milestone, "Full Load") == 0)
         $view[$j]['c4'] = $a_date;

    if (strcasecmp($milestone, "COD") == 0)
         $view[$j]['c3'] = $a_date;
     if (strcasecmp($milestone, "Sync. with Coal")== 0)
         $view[$j]['c5'] = $a_date;

$lastrowprojectname = $projectname;

//echo $view[$j]['c1'] . $view[$j]['c2'] . $view[$j]['c4'] . "<br/>";

3. For my case i wanna fill something in empty fields:

// refill empty fields
//if(!isset($view[$i]['c3']))$view[$i]['c3']="na3"; //cod  na3
if(!isset($view[$i]['c4']))$view[$i]['c4']="na4"; //full load .. it will help to sort out projects not having any full load later during display and cap calculation
//if(!isset($view[$i]['c5']))$view[$i]['c5']="na5"; //sync with coal

//if sync with coal empty then copy full load in it.. uncomment with above line to use empty...
if(!isset($view[$i]['c5']))$view[$i]['c5']=$view[$i]['c4']; //sync with coal

//echo $view[$i]['c1'] . $view[$i]['c1'] . $view[$i]['c4'] . "<br/>";


4. Now want to sort 2d array as per date

//this function sorts the array according to full load column ie c4 dates
function cmp($a,$b) {
if ($a['c4'] == $b['c4']) return 0;
return ($a['c4'] < $b['c4'])? -1 : 1;

//$view = array_reverse($view); //to sort descending uncomment this

5. Go through For loop to get desired result.



   $temp.= "<tr><td/>".  $view[$i]['c1'] ."</td><td> " .   $view[$i]['c2']. "</td><td> " .  cmdate($view[$i]['c5'])."</td><td> " .  cmdate($view[$i]['c4'])."</td><td> " .  cmdate($view[$i]['c3']).  "</td></tr>"; 

- Vinod Kotiya


Most Viewed Post

solution for facebook waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers