Add a new calculated column to each row of a multidimensional arrayarray

Question

I have a PHP loop that's getting data from my table and pushing it into an array.

$children = mysql_query("SELECT c.id, c.name, c.age, c.photoName, c.panelColor FROM children as c");
$temp = array();

while ($child = mysql_fetch_assoc($children)) { 

    // Get the child's reatings
    $ratings = mysql_query('
        SELECT r.behaviourID, t.points, t.typeName 
        FROM  behaviourRatings as r 
        JOIN  behaviourTypes as t 
        ON    r.behaviourID = t.typeID 
        WHERE r.childID = ' . $child['id']);

    // Loop over the ratings
     $totalPoints = 0;
     while ($childRatings = mysql_fetch_array($ratings)){
       $totalPoints = ($totalPoints + $childRatings['points']);
     }

     // We can only max out at our set max
     if(($totalPoints + $maxPoints) > $maxPoints) {
        $total = $maxPoints;
     } else if($totalPoints < 0){
        $total = ($maxPoints + $totalPoints);
     }else{
        $total = ($maxPoints - $totalPoints);
     }

     // Set the child array
     $temp[] = $child;
 }

$response = array();
$response['timestamp'] = $currentmodif;
$response['children'] = $temp;
echo json_encode($response, JSON_PRETTY_PRINT);

I want to add another key/value to the array called points and assign its value as $total.

I tried doing $temp['points'] = $total but that put it outside of the array and not with the outer loops data.

This is the result of the function:

{
    "timestamp": 1482918104,
    "children": [
        {
            "id": "1",
            "name": "Maya",
            "age": "5",
            "photoName": "maya.png",
            "panelColor": ""
        },
        {
            "id": "2",
            "name": "Brynlee",
            "age": "3",
            "photoName": "brynlee.png",
            "panelColor": "green"
        }
    ]
}

I want to show the points for each of those children but I am unsure how to add it to that part of the array.

Don't use any of the mysql_ functions. They were deprecated years ago and officially removed as of PHP 7. — Andy Ibanez
– Andy Ibanez, Commented Dec 29, 2016 at 3:01

Dekel · Accepted Answer · 2016-12-29 01:21:51Z

3

You should add it to the $child variable, just before adding that variable to the $temp array:

$child['points'] = $total;
$temp[] = $child;

answered Dec 29, 2016 at 1:21

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

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

Comments

Sergei Ten · Accepted Answer · 2016-12-29 04:13:15Z

0

Also you can reduce requests to database by getting all child's ratings in one query.

Something like this:

$children = mysql_query("SELECT c.id, c.name, c.age, c.photoName, c.panelColor FROM children as c");

$childrenIds = [];

while ($child = mysql_fetch_assoc($children)) {
     $childrenIds[] = $child['id'];
}

if (!empty($childrenIds)) {
    $ratings = mysql_query('
         SELECT r.behaviourID, t.points, t.typeName 
         FROM  behaviourRatings as r 
         JOIN  behaviourTypes as t 
         ON    r.behaviourID = t.typeID 
         WHERE r.childID IN (' . implode(',', $childrenIds) . ')');
}

answered Dec 29, 2016 at 4:13

Sergei Ten

343 bronze badges

Collectives™ on Stack Overflow

Add a new calculated column to each row of a multidimensional arrayarray

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related