×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

PHP-MySQL CROSS TAB Problem (PIVOT).

PHP-MySQL CROSS TAB Problem (PIVOT).

PHP-MySQL CROSS TAB Problem (PIVOT).

(OP)
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:
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
 

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

Thank you all in advance..

RE: PHP-MySQL CROSS TAB Problem (PIVOT).

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

CODE

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

code like this might work

CODE

$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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close