Creating a table with PHP foreach function

Question

I'm in a class called database programming. We got a data set and and put it into our servers. Now I have to use a jquery plugin to help visualize that data. I am using Graph Us plugin and trying to use the "Fill In" option.

My professor helped me create this function:

<?php
include 'connect.php';
$country_query = "SELECT DISTINCT Country FROM FemaleMaleRatioNew";
$result = mysqli_query($sql_link, $country_query);
$new_row = array();
while ($row = mysqli_fetch_assoc($result)) {
    $country = $row['Country']; 
    $query = sprintf("SELECT Year, Value FROM FemaleMaleRatioNew WHERE Country =     '%s'", $country);  
    $country_result = mysqli_query($sql_link, $query);
    while ($country_row = mysqli_fetch_assoc($country_result) ) {
        $new_row[$country][] = array('year' => $country_row['Year'],
                            'value'=> $country_row['Value']
                            );

    }
}   

//print_r($new_row);


?>

the print_r($new_row); is only there to make sure it works and it does, it prints out the array when activated.

He then guided me to create the table like this:

    <body>  

    <table id="demo">

    <?php  foreach($new_row as $row):?> 




            <tr>
                <td><?=$row['year'];?></td>
                <td><?=$row['country'];?></td>
            </tr>
    <?php endforeach;?>



    </table>

<script type="text/javascript">
$(document).ready(function() {

// Here we're "graphing up" only the cells with the "data" class
$('#demo td').graphup({
    // Define any options here
    colorMap: 'heatmap',
    painter: 'fill',
    // ...
});

});
</script>
</body>

What else do I need to do to get the table to work? I can't seem to figure it out. All it does is come out blank.

I'm sorry if this question isn't worded correctly or if I have not been clear on anything please let me know.

The formatting is really off. Would you mind reformatting your PHP according to this: framework.zend.com/manual/1.12/en/… and just delete empty lines in the HTML. — mzedeler
– mzedeler, Commented Apr 16, 2013 at 20:54

Kamil Szot · Accepted Answer · 2013-04-16 22:33:49Z

5

You have multiple rows for each country in your $new_row variable. You have to iterate over countries first and then over the individual rows of data:

<?php  foreach($new_row as $country => $rows): ?>
  <?php  foreach($rows as $row): ?>
        <tr>
            <td><?=$country;?></td>
            <td><?=$row['Year'];?></td>
            <td><?=$row['Value'];?></td>
        </tr>
  <?php endforeach;?>
<?php endforeach;?>

Also please note that you need colon ':' not semicolon ';' after the foreach statement. This syntax (which is less known) is described here: http://php.net/manual/en/control-structures.alternative-syntax.php

If you want to display some sort of aggregate (for example sum) per country and you want to calculate it in PHP (as opposed to MySQL) you can do it like this:

<?php foreach($new_row as $country => $rows): 
  $sum = 0;
  foreach($rows as $row): 
    $sum += $row['Value'];
  endforeach;
?>
        <tr>
            <td><?=$country;?></td>
            <td><?=$sum;?></td>
        </tr>
<?php endforeach;?>

edited Apr 16, 2013 at 22:33

answered Apr 16, 2013 at 20:57

Kamil Szot

17.9k8 gold badges65 silver badges67 bronze badges

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

4 Comments

clo3o5 Over a year ago

this works, but it doesnt display the information in the table how I want it....it just displays all of the countries in the table and then lists numbers 1-119

Kamil Szot Over a year ago

You haven't said how you wanted the information to be displayed. Do you want the sum of values per each country, average, product, standard deviation? You should be able to calculate all these things by adjusting inner loop.

clo3o5 Over a year ago

I just want the values displayed for every year. I do not need the sum average or any other calculation of the values...just the values displayed and they will be added to a graph using the GraphUP Jquery Plugin

clo3o5 Over a year ago

i49.tinypic.com/2h4ezag.png that is a picture of my code for a better understanding at what i'm looking at

Marc B · Accepted Answer · 2013-04-16 21:22:16Z

1

You should be using a single JOINed query to do this stuff, but you may not have gotten that in class yet. Since it's homework, I won't give you the flat-out answer, but here's the pseudo-code:

$countries = SELECT DISTINCT Country FROM YourTable;
while($country_row = fetch_row($countries)) {
   echo $country_row['Country'];
   echo <table>;
   $status = SELECT Year, Value FROM YourTable WHERE Country=$country_row['Country'];
   while($stats_row = fetch_row($status) {
        echo <tr><td>$stats_row['Year']</td><td>$stats_row['Value']}</td>
   }
   echo </table>
}

edited Apr 16, 2013 at 21:22

answered Apr 16, 2013 at 20:55

Marc B

362k44 gold badges433 silver badges508 bronze badges

6 Comments

clo3o5 Over a year ago

this would replace the first query i posted?

Marc B Over a year ago

irrelevant: this is homework. you do not just give away the answer. the student must THINK for themselves.

clo3o5 Over a year ago

I don't understand this? Would this replace my first query or do i use this as well as the one i'm already using?

Marc B Over a year ago

@clo3o5: no, it just demonstrates the basic flow of what your code should do. you still have to provide the queries and actual code.

clo3o5 Over a year ago

$countries = SELECT DISTINCT Country FROM YourTable; while($country_row = fetch_row($countries)) { echo $country_row['Country']; I got this part to work but the bottom part gives me an error and doesnt even load

|

Collectives™ on Stack Overflow

Creating a table with PHP foreach function

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related