PHP joining row from database

Question

I need an advice how to display data from array (result of sql query) in PHP.

Here's the problem:

My query result is 2 row (top figure). Currently, I'm using foreach method to display all data, thus, the foreach will iterate 2 times, right?

Now, I want to ask: In my web page, how I can display like the bottom figure??

How to join the second row (the theory's score is 0, and the lab's score is 75 and 80) to the first row? So it'll be ONLY 1 row for ID 112.

EDIT:

The SQL query is not my part. I've just received that data (top figure), and my job only to display it to web page.

What is your current SQL? You need to use `SUM()` and `GROUP BY` — Zamrony P. Juhara, Nov 02 '16 at 11:05
@ZamronyP.Juhara The SQL query is not my part. I've just received that data (top figure), and my job only to display it to web page. — joshua14, Nov 02 '16 at 11:08
Show your both array on which you are using `foreach`, as we want to see how you are receiving data from query ? — himeshc_IB, Nov 02 '16 at 11:29
would it bother you to show the actual PHP code you are using to display the table not grouped per id? — Lelio Faieta, Nov 02 '16 at 11:35
Possible duplicate of [How to sum values of the array of the same key?](http://stackoverflow.com/questions/1496682/how-to-sum-values-of-the-array-of-the-same-key) — Ultrazz008, Nov 02 '16 at 11:42

Ultrazz008 · Answer 1 · 2016-11-02T11:38:21.610

0

Well as you used to display rows with foreach you could do it the same way:

* create new array
* loop through your current array
* save to new array by ID and do SUMs
* you can do output from newly created array.

edited Nov 02 '16 at 11:38

answered Nov 02 '16 at 11:31

Ultrazz008

1,678
1
13
26

score 0 · Answer 2 · answered Nov 02 '16 at 12:40

0

create new array by loop through your current array which will filter out 0 values probably by using array_walk_recursive() or array_map() or array_filter()
then at this stage you got filtered array
you can do output from filtered array

answered Nov 02 '16 at 12:40

himeshc_IB

853
4
10

framontb · Answer 3 · 2016-11-02T14:54:24.913

I'm wondering why you have two rows with same 'id'. Usually 'id' should be the primary key... Anyway, you could simply combine the rows with same 'id', adding the equally named fields casting them like integers.

EDIT 1-2: Supposing code is in associative arrays:

$rows = array(
    0 => array(
        'id' => 112,
        'theory' => array('score1' => 70, 'score2' => 80),
        'lab' => array('score1' => 0, 'score2' => 0)
    ),
    1 => array(
        'id' => 112,
        'theory' => array('score1' => 0, 'score2' => 0),
        'lab' => array('score1' => 75, 'score2' => 80)
    ),
    2 => array(
        'id' => 110,
        'theory' => array('score1' => 0, 'score2' => 0),
        'lab' => array('score1' => 75, 'score2' => 80)
    )  
);  
// New buffer for data
$newArray = array();  
// Loop to combine buffer
foreach ($rows as $row) {
    if (array_key_exists($row['id'], $newArray)) 
    {
        $newArray[$row['id']]['theory']['score1'] += $row['theory']['score1'];
        $newArray[$row['id']]['theory']['score2'] += $row['theory']['score2'];
        $newArray[$row['id']]['lab']['score1'] += $row['lab']['score1'];
        $newArray[$row['id']]['lab']['score2'] += $row['lab']['score2'];
    } else {
        $newArray[$row['id']]['theory']['score1'] = $row['theory']['score1'];
        $newArray[$row['id']]['theory']['score2'] = $row['theory']['score2'];
        $newArray[$row['id']]['lab']['score1'] = $row['lab']['score1'];
        $newArray[$row['id']]['lab']['score2'] = $row['lab']['score2'];
    }
}  
// Loop to show data
foreach ($newArray as $key => $value) {
    echo $key." ".$value['theory']['score1']." ".$value['theory']['score2']." ".$value['lab']['score1']." ".$value['lab']['score2']."\n";
}

Output:

112 70 80 75 80
110 0 0 75 80

umm actually there's other field that make it two rows, but I didn't write it. And how to " combine the rows with same 'id' " ?? — joshua14, Nov 02 '16 at 11:26
Use a foreach loop for joining the data in new array, and different foreach loop for showing the data. — framontb, Nov 02 '16 at 11:39
Put a var_dump($row) and the code will be here in this post in 5 minutes. — framontb, Nov 02 '16 at 13:38

sbrbot · Answer 4 · 2016-11-02T19:05:18.567

Let's say that you receive your data as Arrays:

$data=array();
$data[0]=[112,70,80,0,0];
$data[1]=[112,0,0,75,80];
$data[2]=[113,71,81,0,0];
$data[3]=[113,0,0,76,81];

If you know in advance that each ID will have exactly two records:

echo '<table>';
echo '<thead>';
echo '<tr>';
echo '<th rowspan="2">ID</th><th colspan="2">theory</th><th colspan="2">lab</th>';
echo '</tr>';
echo '<tr>';
echo '<th>score1</th><th>score2</th><th>score1</th><th>score2</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';
for($i=0;$i<count($data);$i++)
{
  $d=$data[$i];
  echo '<tr>';
  echo '<td>'.$d[0].'</td>'; //ID
  echo '<td>'.$d[1].'</td><td>'.$d[2].'</td>';
  $d=$data[++$i]; //take next element from array
  echo '<td>'.$d[3].'</td><td>'.$d[4].'</td>';
  echo '</tr>';
}
echo '</tbody>';
echo '</table>';

Code on PHPfiddle.

But generally, your SQL query should be changed in order to represent pivoted data and then you will not have this issue. However, you emphasized that this is out of your power.

@joshua14 this code results with table exactly as you wanted, why do you assume it not useful? Run in in fiddle and see. — sbrbot, Nov 02 '16 at 12:07

PHP joining row from database

4 Answers4