split mysql result rows into columns, php

Question

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; ?>

There is no issue in mysql if I am right remove mysql tag

Bhavik Shah
– Bhavik Shah

2013-02-07 08:59:40 +00:00
Commented Feb 7, 2013 at 8:59 — Bhavik Shah
– Bhavik Shah, Commented Feb 7, 2013 at 8:59

mika · Accepted Answer · 2013-02-07 09:12:47Z

1

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; ?>

:)

answered Feb 7, 2013 at 9:12

mika

1,9713 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NestedWeb Over a year ago

what if the queried field is varchar?

mika Over a year ago

you mean, if your date is a varchar ? You can use the same "SUM IF" trick: this will add the value you want when you match a given condition, and 0 when you do not.

Alepac · Accepted Answer · 2013-02-07 09:20:24Z

0

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;

answered Feb 7, 2013 at 9:20

Alepac

1,84313 silver badges24 bronze badges

Collectives™ on Stack Overflow

split mysql result rows into columns, php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related