CODEWITHSUNDEEP
phphtmlmysql
NestedWeb
NestedWeb

Reputation: 1737

split mysql result rows into columns, php

mysql tables

--work table--                                               --crew table--

date           |   hours                                     date   |    name   
2013-02-03(Sun)     8                                    2013-02-03     john
2013-02-04(Mon)     7                                    2013-02-03     sam 
                                                         2013-02-03     peter
                                                         2013-02-04     john
                                                         2013-02-04     sam

Query

SELECT hours, name, DAYNAME(work.date) day
FROM work 
LEFT JOIN crew ON work.date = crew.date
WHERE WEEK(work.date) = 5

result

hours    name    day
  8      John   Sunday
  8      Sam    Sunday
  8      Peter  Sunday
  7      John   Monday
  7      Sam    Monday

now format in html like this, how ?

Name    Sunday   Monday  Tuesday..(day name can be written in html)
 John      8        7       ...
 Sam       8        7       ...
 Peter     8        -       ...

pseudocode

<?php foreach($result as $r): ?>
     <tr>
         <td>
            <?php echo $r->name; ?>
         </td>
         <td>
            <?php echo $r->hours on Monday; ?>
         </td>
         <td>
            <?php echo $r->hours on Tuesday; ?>
         </td>
         <td>
            <?php echo $r->hours on Wednesday; ?>
         </td>     
         ..
         ..       
     </tr>       
<?php endforeach; ?>

Upvotes: 2

Views: 844

Answers (2)

Alepac
Alepac

Reputation: 1831

This provides the expected result:

SELECT name,
   SUM(if(DAYOFWEEK(w.date)=1,hours,0)) AS 'Sunday',
   SUM(if(DAYOFWEEK(w.date)=2,hours,0)) AS 'Monday',
   SUM(if(DAYOFWEEK(w.date)=3,hours,0)) AS 'Tuesday'
FROM Crew c,
WORK w
WHERE c.date = w.date
GROUP BY Name;

Upvotes: 0

mika
mika

Reputation: 1971

You may put your query this way:

SELECT 
    SUM(IF(DATE_FORMAT(work.date, "%w") = 0), hours, 0) as Sunday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 1), hours, 0) as Monday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 2), hours, 0) as Tuesday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 3), hours, 0) as Wednesday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 4), hours, 0) as Thursday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 5), hours, 0) as Friday,
    SUM(IF(DATE_FORMAT(work.date, "%w") = 6), hours, 0) as Saturday,
    name
FROM work 
LEFT JOIN crew 
ON work.date = crew.date
WHERE WEEK(work.date) = 5
GROUP BY name

And then your php would look like this:

<?php foreach($result as $r): ?>
     <tr>
         <td>
            <?php echo $r->name; ?>
         </td>
         <td>
            <?php echo $r->Monday; ?>
         </td>
         <td>
            <?php echo $r->Tuesday; ?>
         </td>
         <td>
            <?php echo $r->Wednesday; ?>
         </td>     
         ..
         ..       
     </tr>       
<?php endforeach; ?>

:)

Upvotes: 1

Related Questions