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;


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


//print_r($row);

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
$lastrowprojectname;
$projectname;
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;
$k++;


//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
for($i=0;$i<=$j;$i++)
{
//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;
}


usort($view,'cmp');
//$view = array_reverse($view); //to sort descending uncomment this
//print_r($view);


5. Go through For loop to get desired result.


for($i=0;$i<$j;$i++)


{


   $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

www.vinodkotiya.com