Create table with php from mysql table

[dikkuh]

Hi,

 

I'm really a noob with php, hopefully you can put me in to the right direction.

I have a MySQL stored procedure which gives for an example the following output:

 

seasonID  |  Name | Points |  EventID

20             |  Ron   |    10    |     1

20             |  Ron   |     0     |     2

20             |  John  |     5     |     1

20             | John   |     8     |     2

 

I want to display the results in a table so it would look like this:

 

Name       |   EventID 1 |  EventID 2 | Total Points

John         |       5           |      8           |      13

Ron          |      10          |      0           |      10

 

So for every new EventID within a season there should be a new column.

 

To be honest I don't know where to start, all I get is the same output from the stored procedure.

Every row in MySQL is also a new row in php.

 

This is what I have:

<table border="0" cellspacing="0" dellpadding="2">
        <?php        if($seasonId != "50")
        {         $DriverPoints = mysqli_query(getConnection(), "CALL sp_ron('$seasonId')");
                  {          $pos = 1;

          while($rowDriverPoints = mysqli_fetch_object($DriverPoints))
                    {   echo("<tr><td width='30' align='center' class='property'>" . $rowDriverPoints->name . "</td>
             		<td width='30' align='center' class='property'>" . $rowDriverPoints->eventname . "</td>
		        <td width='30' align='center' class='property'>" . $rowDriverPoints->points . "</td></tr>");
           		$pos++;
		    }        
	      	  } 
       }        
?>
</table>

[cyberRobot]

As you're looping through the MySQL data, you could build a multidimensional associative array. More information could be found here:

https://www.google.com/search?q=multidimensional+associative+array+php

 

You could then use a foreach loop to display the HTML table.

[0xMatt]

Wouldn't using the GROUP clause for the mysql query be better than building a bunch of extra php code?

 

Mysql could handle that much faster with less code.

 

Edit: My bad, group wouldn't be the best case, but this should be achievable with mysql. I'll look for a simple solution.

Edited August 8, 2013 by 0xMatt

[mac_gyver]

if your stored procedure returns the data in the order that you want it, sorted by the name,eventid and there are rows for every name for every eventid, it would be simple to produce this output as you loop through the rows of the result set. just detect when the name changes, finish any previous table-row output, and start the next table-row output.

 

however, if the data isn't in the order that you want it or there are not rows for each name/eventid, you will need to pre-process the data, storing it into an array as cyberRobot suggested, with the name being one index and the eventid being the next. you could then sort the array by the names and find the min/max eventid's. you would then loop over the names and loop over the range of eventid's, accessing any data stored using the array indexes or using a default value when there's isn't any data for any name/eventid.

[dikkuh]

Currently the stored procedure returns rows sorted by name and eventid and there is a row for every eventid per name.

 

I want to build a matrix where the standings are shown per name, and the points per eventid in columns.

So only 1 row per name with the points for every eventid in a column.

 

It's also possible that someone didn't participate every event.

Basically it should check how many events there were, and create a column for every event, then fill in the points per event per name.

[mac_gyver]

you would do my paragraph #2, less the need to sort the name. the following should (untested) work (is based on the code you posted showing name, eventname, and points as the actual data being retrieved) -

// pre-process the data
$events = array(); // a list of all the unique event names in the retrieved data
$data = array(); // the data that was retrieved
while($row = mysqli_fetch_object($DriverPoints))
{
    $data[$row->name][$row->eventname] = $row->points;
    if(!in_array($row->eventname,$events)){
        $events[] = $row->eventname; // remember unique event names in the order they were retrieved in
    }
}


// use the data
// output your table heading here....


// output the table data
foreach($data as $name=>$arr){
    $total = 0;
    echo "<tr><td>$name</td>";
    foreach($events as $event){
        $score = '--'; // default is 'did not participate', display '--' or anything else you want
        if(isset($arr[$event])){ // there is data for the event
            $score = $arr[$event];
            $total += $score;
        }
        echo "<td>$score</td>";
    }
    echo "<td>$total</td></tr>\n";
}

[dikkuh]

Thanks it works

 

Only have to sort the total points from high to low, but this is already a really big help.

[dikkuh]

How can I add more columns to this array:

$data[$row->name][$row->eventname] = $row->points;

No I have a matrix with only the name and the points, I would like to add a column behind the name to the table with the country flag of the user.

[dikkuh]

I found a solution which is working for me, I changed the line to

 

$data [$row->driverFirstName . " " . $row->driverName][$row->racename] = $row->points;