how to display mysql results into different columns based off of values

Question

ok I am really struggling for 3 days on learning how to get values from a query to show in different columns based off of the values from the query. Here is what I want it to look like...

Name    0-30    30-60    60-90    90+
john            $50
june    $0.00
joe                               $100
fred                     $500

As of right now, I can get a list of the names and the money values above, but I only know how to display them as follows...

Name    0-30    30-60    60-90    90+
john    $50     $50      $50      $50
june    $0.00   $0.00    $0.00    $0.00
joe     $100    $100     $100     $100
fred    $500    $500     $500     $500

I am simple printing the variables in each column. Any help is greatly appreciated.

echo "<TABLE WIDTH='100%' BORDER='0' CELLSPACING='0' CELLPADDING='5'>
<TR>
<TD WIDTH='5%'><B>Job</B><BR></TD>
<TD WIDTH='30%'><B>Name</B><BR></TD>
<TD WIDTH='12%'><B>0-30 Days</B><BR></TD>
<TD WIDTH='12%'><B>30-60 Days</B><BR></TD>
<TD WIDTH='12%'><B>60-90 Days</B><BR></TD>
<TD WIDTH='12%'><B>90+ Days</B><BR></TD>
<TD WIDTH='15%'><B>Total Amount</B><BR></TD>
</TR><TR>";

SQL query here...the sqlfiddle. http://sqlfiddle.com/#!2/e5d9c/1/0

echo "<TD WIDTH='5%'>$primary_key</TD>
<TD WIDTH='30%'>$a_name</TD>
<TD WIDTH='12%'>$total / $timeperiod</TD>
<TD WIDTH='12%'>$total / $timeperiod</TD>
<TD WIDTH='12%'>$total / $timeperiod</TD>
<TD WIDTH='12%'>$total / $timeperiod</TD>
<TD WIDTH='15%'>$total / $timeperiod</TD>
</TR>";

I know the above is wrong but I am not sure what to put above to display my example of what I want?

Post your current php code and table schema. It would be helpful. — Javad Shareef
– Javad Shareef, Commented Jan 18, 2013 at 9:26

mahmoud · Accepted Answer · 2013-01-18 09:27:29Z

1

just try to add a condition before the echo in your column. that should looks like :

`if ($res['name']>=0 and $res['name']<30) echo $res['data'];

if ($res['name']>=30 and $res['name']<60) echo $res['data'];

if ($res['name']>=60 and $res['name']<90) echo $res['data'];`

....

//$res['name'] --> john / june ... //$res['data'] --> $50 / $500 ...

I think this is the easiest way to do it ..

answered Jan 18, 2013 at 9:27

mahmoud

567 bronze badges

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

Comments

Aratidgr8 · Accepted Answer · 2013-01-18 09:31:23Z

Below code snippet will do the needful.

<?php

$query = "SELECT amount FROM mytable";

$res  = mysql_query($query);

while($data = mysql_fetch_row($res))
{
    echo "<tr>";
    if($data[0] < 30)
    {
        echo "<td colspan='4' align='left'>".$data[0]."</td>";
    }
    else
    {
        echo "<td>&nbsp;</td>";
        if($data[0] > 30 && $data[0] < 60)
            echo "<td colspan='3' align='left'>".$data[0]."</td>";
        else
        {
            echo "<td>&nbsp;</td>";
            if($data[0] > 60 && $data[0] < 90)
                echo "<td colspan='2' align='left'>".$data[0]."</td>";
            else
            {
                echo "<td>&nbsp;</td>";
                if($data[0] >  90)
                    echo "<td align='left'>".$data[0]."</td>";

            }
        }
    }
    echo "</tr>";
}

?>

Hieu Le · Accepted Answer · 2013-01-18 09:36:29Z

0

SELECT 
  Name,
  c0_30, c30_60, c60_90, c90
  FROM (                                   
    SELECT                                 
      Name,
      SUM(IF(val BETWEEN 0 AND 30, val, NULL)) As 'c0_30',
      SUM(IF(val BETWEEN 31 AND 60, val, NULL)) As 'c30_60',
      SUM(IF(val BETWEEN 61 AND 90, val, NULL)) As 'c60_90',
      SUM(IF(val > 90, val, NULL)) As 'c90'
    FROM talName
    GROUP BY Name WITH ROLLUP
  ) AS sums;

This query will output the total value of val col of a person and categorize them to the suitable column based on its total value.

answered Jan 18, 2013 at 9:36

Hieu Le

8,4351 gold badge36 silver badges55 bronze badges

Collectives™ on Stack Overflow

how to display mysql results into different columns based off of values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related