MYSQL + PHP to display data from two rows as a single column

Question

I have a table with data in a following format

region   gender  age_group  population 

Dallas    M        1          200  
Dallas    F        1          500 
NY        M        1          320 
NY        F        1          310 
Dallas    M        2          300 
Dallas    F        2          600 
NY        M        2          400 
NY        F        2          800

I'm looking for the best query to output my data in the following format

region/age_group      1           2
Gender             M     F       M    F
Dallas             200   500    300  600
NY                 320   310    400  800

my query returns only the top colum and region. Not sure how to sub divide the top colum into genders as appearing above

SELECT distinct (region), a.group_one, b.group_two 
FROM city_populations c  
LEFT JOIN city_populations AS a ON a.age_group=1 AND c.age_group=a.age_group  
LEFT JOIN city_populations AS b ON b.age_group=2 AND c.age_group=a.age_group

i guess you dont know how to format a question here. Better you check-out the tips to format question here. — Bhavik Shah
– Bhavik Shah, Commented Jan 5, 2013 at 10:36
I am sorry my Friend if my words seems to be harsh to you but believe i just want to inform you that there is really nice stuff here which can help you in formatting question and answer. Please dont mind as my sole intention was to inform you. — Bhavik Shah
– Bhavik Shah, Commented Jan 5, 2013 at 10:42
@Rico: First thing you need to do is to post some relevant code. — OptimusCrime
– OptimusCrime, Commented Jan 5, 2013 at 10:44
You should show such table in presentation layer. SQL does not run on presentation layer. — Shiplu Mokaddim
– Shiplu Mokaddim, Commented Jan 5, 2013 at 11:12

Bhavik Shah · Accepted Answer · 2013-01-05 11:41:14Z

1

//First i fetched all data from database

$result = mysql_query("select * from test2")or die(mysql_error());
$regions = array();
$age_groups = array();
while($det = mysql_fetch_array($result)){

    if(!in_array($det['region'], $regions)){
        $regions[] = $det['region'];
    }
    if(!in_array($det['age_group'], $age_groups)){
        $age_groups[] = $det['age_group'];
    }
    $rows[] = $det;
}

//then processed in php to show as you wanted

echo "<table>";
echo "<tr><td>Age group</td>";
foreach($age_groups as $keyAG => $valueAG){
    echo "<td colspan='2'>$valueAG</td>";
}
echo "</tr>";
echo "<tr><td>Gender</td>";
foreach($age_groups as $keyAG => $valueAG){
    echo "<td>M</td>";
    echo "<td>F</td>";
}
echo "</tr>";
foreach($regions as $keyR => $valueR){

    echo "<tr>";
    echo "<td>".$valueR."</td>";
    foreach($age_groups as $keyAG => $valueAG){

        foreach($rows as $keyROWS => $valueROWS){
            if($valueROWS['region'] == $valueR && $valueROWS['age_group'] == $valueAG){
                if($valueROWS['gender'] == 'M'){
                    echo "<td>".$valueROWS['population']."</td>";
                }
                elseif($valueROWS['gender'] == 'F'){
                    echo "<td>".$valueROWS['population']."</td>";
                }
            }
        }
        $data[$valueR][$valueAG] = array($maleValue, $femaleValue);

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

Note: It is highly recommended to use mysqli_* instead of mysql.

edited Jan 5, 2013 at 11:41

answered Jan 5, 2013 at 11:33

Bhavik Shah

2,3011 gold badge18 silver badges32 bronze badges

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

1 Comment

Rico Over a year ago

@bhavick thanks. Works as a charm! im modifying it to display 0 in rows where there's no population. I've been working on this for over a week! Much appreciation

Collectives™ on Stack Overflow

MYSQL + PHP to display data from two rows as a single column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related