display php / mysql column output as rows

Question

I have the below syntax that is used to display a MySQL query in PHP:

 function get_dayssincecapture($db)  
  {    
    $result = $db->query("SELECT DATEDIFF(now(), sarrive)as days,count(loadnumber) as loads from v2loads where  adminstatus='captured' group by DATEDIFF(now(), sarrive) "); 
    return $result; 
  } 
  $dayssincecapture = get_dayssincecapture($db);

Display Syntax:

<table border=1>
    <tr>
    <? while($row = $dayssincecapture->fetch(PDO::FETCH_ASSOC)) { ?>
    <td><? echo $row['days']; ?><br><? echo $row['loads']; ?></td>
    <? } ?>
    </tr>
</table>

this produces the below screen output

enter image description here

How do I alter by table syntax in order to get the days field as a row heading and the load field as the second row of my table?

so what I want will be:

enter image description here

Thanks as always,

Do you want a SQL solution or php?

Taryn
– Taryn

2013-01-11 11:17:22 +00:00
Commented Jan 11, 2013 at 11:17 — Taryn
– Taryn, Commented Jan 11, 2013 at 11:17
Thanks @bluefeet, comfortable with either. Thanks,

Smudger
– Smudger

2013-01-11 11:17:57 +00:00
Commented Jan 11, 2013 at 11:17 — Smudger
– Smudger, Commented Jan 11, 2013 at 11:17

hsz · Accepted Answer · 2013-01-11 11:19:49Z

5

Try with:

<?php
$dayssincecapture = get_dayssincecapture($db); 
$data = array('days' => array(), 'loads' => array());

while($row = $dayssincecapture->fetch(PDO::FETCH_ASSOC)) {
    $data['days'][]  = $row['days'];
    $data['loads'][] = $row['loads'];
}
?>

<table style="border: 1px solid #000">
    <tr>
        <td>Days</td>
        <?php foreach ( $data['days'] as $day ) { ?>
            <td><?php echo $day; ?></td>
        <?php } ?>
    </tr>
    <tr>
        <td>Loads</td>
        <?php foreach ( $data['loads'] as $load ) { ?>
            <td><?php echo $load; ?></td>
        <?php } ?>
    </tr>
</table>

answered Jan 11, 2013 at 11:19

hsz

153k63 gold badges268 silver badges320 bronze badges

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

1 Comment

Smudger Over a year ago

Awesome @hsz, appreciate the prompt reply. awesome syntax, thank you.

Taryn · Accepted Answer · 2013-01-11 11:24:01Z

If you wanted to perform this type of transformation in MySQL, then you will be pivoting the data. MySQL does not have a pivot function but you can replicate this using an aggregate function with a CASE statement:

select 
  count(case when DATEDIFF(now(), sarrive) = 1 then loadnumber end) as Day_1,
  count(case when DATEDIFF(now(), sarrive) = 2 then loadnumber end) as Day_2,
  count(case when DATEDIFF(now(), sarrive) = 3 then loadnumber end) as Day_3,
  count(case when DATEDIFF(now(), sarrive) = 4 then loadnumber end) as Day_4,
  count(case when DATEDIFF(now(), sarrive) = 5 then loadnumber end) as Day_5,
  count(case when DATEDIFF(now(), sarrive) = 6 then loadnumber end) as Day_6,
  count(case when DATEDIFF(now(), sarrive) = 7 then loadnumber end) as Day_7
from v2loads
where adminstatus='captured'

You can also write this code inside of a prepared statement to create this dynamically since the values will be unknown.

Wojciech Szymanski · Accepted Answer · 2013-01-11 11:38:01Z

1

You can try:

<?php

while($row = $dayssincecapture->fetch(PDO::FETCH_ASSOC)) {
    $days_row .= "<td>" . $row['days'] . "</td>";
    $loads_row .= "<td>" . $row['loads'] . "</td>";
}

?>
<table> 

  <tr>
    <td>Days</td>
    <?php echo $days_row; ?>
  </tr>
  <tr>
    <td>Loads</td>
    <?php echo $loads_row; ?>
  </tr>
</table>

answered Jan 11, 2013 at 11:38

Wojciech Szymanski

3682 silver badges8 bronze badges

Collectives™ on Stack Overflow

display php / mysql column output as rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related