PHP-MySQL CROSS TAB Problem (PIVOT).

[kumarg19]

Hello All,

I have a query that works and is returning accurate data, but I want to display it in a different format ( PIVOT or Cross-Tab format). Basically, what I need help with is making this into a crosstab query. My query returns:

Cell | Qty | Date
van-1 | 5 | 2014-10-01
van-2 | 3 | 2014-10-01
van-3 | 2 | 2014-10-01
van-1 | 4 | 2014-10-02
van-2 | 3 | 2014-10-02
van-3 | 1 | 2014-10-02
van-1 | 3 | 2014-10-03
van-2 | 6 | 2014-10-04
van-3 | 2 | 2014-10-05

and so on...........
But, I need the results to look like this:
[pre]
Cell | 2014-10-01 | 2014-10-02 | 2014-10-03
van-1 | 5 | 4 | 3
van-2 | 3 | 3 | 6
van-3 | 2 | 1 | 2

Any guidance is greatly appreciated. The code I have is displayed below.


$sql = "SELECT cell,qty, date
FROM cell_table
WHERE date BETWEEN '2014-10-01' AND '2014-10-03'
GROUP BY cell";

$q = mysql_query($sql) or die("could not search!");

echo "<table border='1'>";
echo "<tr>
<td>Cell </td>
<td> Qty </td>
<td> Date </td>
</tr>" ;
while ($row=mysql_fetch_array($q1)) {
echo "<tr>
<td>". $row['cell'] ." </td>
<td>". $row['qty'] ." </td>
<td>". $row['date'] ." </td>
</tr>";
}
echo "</table>";


I am getting output like.
Cell | Qty | Date
van-1 | 5 | 2014-10-01
van-2 | 3 | 2014-10-01
van-3 | 2 | 2014-10-01
van-1 | 4 | 2014-10-02
van-2 | 3 | 2014-10-02
van-3 | 1 | 2014-10-02
van-1 | 3 | 2014-10-03
van-2 | 6 | 2014-10-04
van-3 | 2 | 2014-10-05

But, I need the results to look like this:

Cell | 2014-10-01 | 2014-10-02 | 2014-10-03
van-1 | 5 | 4 | 3
van-2 | 3 | 3 | 6
van-3 | 2 | 1 | 2
[/pre]

The dates would be my column headers, Cell would be the row heading, and Qty would be the value.

Thank you all in advance..

 

[jpadie]

the easiest way is just to run a transformation on the array as you get the database results (i.e. in the while iteration loop). make life easy on yourself too by ordering by the cell then the date.

so you end up with an array like this

array[van-1]= array (
                     [date]=quantity,
                     [date]=quantity
);

code like this might work

$output = array();
while ($row = mysql_fetch_assoc($q1)):
  $output[$row['Cell']][$row['Date']] = $row['Qty'];
endwhile;
echo '';
$first = true;
foreach($output as $cell=>$data):
  if($true):
    echo '<table><tr><th>Cell</th>';
    foreach($data as $date=>$qty):
     echo '<th>' . $date .'</th>';
    endforeach;
    echo '</tr>';
    $first = false;
  endif;
  echo '<tr><td>'.$cell .'</td>';
  foreach($data as $date=>$quantity):   
    echo '<td>' . $quantity . '</td>';
  endforeach;
  echo '</tr>';
endforeach;
echo '</table>';

there are more 'refined' methods but this is probably the easiest for you to decipher.