title: PHP and Mysql query, use PHP to convert row to columns
body: <p>I think this is beyond me, I want to convert my MYSQL query so that it displays rows and columns exactly like this:
  <img src="https://i.stack.imgur.com/oDzF9.png" alt="enter image description here"></p>
  
  <p>This is what I have created with a MYSQL query:
  <img src="https://i.stack.imgur.com/mtp6n.png" alt="enter image description here"></p>
  
  <p>Here is the actual query to create this:</p>
  
  <pre><code>SELECT h.workdate AS `Date`
   , DAYNAME(h.workdate) AS `Day`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Perm%')
         THEN
           CASE
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMPERMDRIV`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Perm%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMPERMDOCK`
    , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Cas%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMCASUDRIV`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Cas%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMCASUDOCK`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%AGENT%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMAGENTDRIV`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='AM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%AGENT%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `AMAGENTDOCK`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='PM' AND DriverDock = 'Driv' AND `Salary Code Description` LIKE '%Perm%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `PMPERMDRIV`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Perm%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `PMPERMDOCK`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%Cas%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `PMCASUDOCK`
   , COALESCE(TRUNCATE(SUM(
       CASE
       WHEN (shift='PM' AND DriverDock = 'Dock' AND `Salary Code Description` LIKE '%AGENT%')
         THEN
           CASE 
           WHEN paycode IN ('03 Overtime 2.0')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 2.0)
           WHEN paycode IN ('02 Overtime 1.5')
             THEN (employeehours * IF(p.`Other Rate`=0,p.`Base Rate`,p.`Other Rate`) * 1.5)
           WHEN paycode IN ('01 Ordinary')
             THEN (employeehours * p.`Base Rate`)
           END
       END
     ),2),0) AS `PMAGENTDOCK`
  FROM employeedata d
  JOIN employeehours h
  ON d.`ID Number` = h.employeeid
  JOIN historyemployeepay p
  ON (h.employeeid = p.EmployeeID)
  AND (h.workdate &gt;= p.FromDate)
  AND (h.workdate &lt;= p.ToDate OR p.ToDate IS NULL)
  CROSS  
  JOIN otherrates r
  WHERE DAYNAME(h.workdate) &lt;&gt; 'Saturday'
  GROUP BY h.workdate
  </code></pre>
  
  <p>I have the following html table, how do I loop through the query results and display correctly?:</p>
  
  <pre><code>&lt;?php
    $querydailyoperations=mysql_query("
                                       QUERY ABOVE
    ");
  while($querydailyoperationsshow=mysql_fetch_array($querydailyoperations)){ echo "??????????";};
  ?&gt;
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>&lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td class="greencell"&gt;PERMANENT&lt;/td&gt;&lt;td class="greencell"&gt;DRIVER&lt;/td&gt;5x td here, input from above query...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td class="greencell"&gt;PERMANENT&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td class="orangecell"&gt;CASUAL&lt;/td&gt;&lt;td class="greencell"&gt;DRIVER&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td class="orangecell"&gt;CASUAL&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td nowrap class="redcell"&gt;AGENT LABOUR&lt;/td&gt;&lt;td class="greencell"&gt;DRIVER&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="greencell"&gt;AM&lt;/td&gt;&lt;td nowrap class="redcell"&gt;AGENT LABOUR&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="bluecell"&gt;PM&lt;/td&gt;&lt;td class="greencell"&gt;PERMANENT&lt;/td&gt;&lt;td class="greencell"&gt;DRIVER&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="bluecell"&gt;PM&lt;/td&gt;&lt;td class="greencell"&gt;PERMANENT&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="bluecell"&gt;PM&lt;/td&gt;&lt;td class="orangecell"&gt;CASUAL&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;tr&gt;&lt;td class="bluecell"&gt;PM&lt;/td&gt;&lt;td nowrap class="redcell"&gt;AGENT LABOUR&lt;/td&gt;&lt;td class="dbluecell"&gt;DH&lt;/td&gt; etc...
  &lt;/tr&gt;
  &lt;/tbody&gt;
  &lt;/table&gt;
  </code></pre>