How to add information from the DB to an existing array of objects?

Question

I have an array in the following format;

// echo '<pre>' . var_export($this->viewableFields, true) . '</pre>';

array (
  0 => 
  (object) array(
     'formId' => '4',
     'userId' => '7'
  ),
  1 => 
  (object) array(
     'formId' => '4',
     'userId' => '4'
  )
)

I need to amend the data and add another key/value to this array. I need to use the userId value from the array, query a MySQL database and return the value. I need to do this for each array element.

So for each array element I want to run a query like;

SELECT group from users WHERE userId = [userId in array]

I then want to add this value to the array, the final array should look like this;

array (
  0 => 
  (object) array(
     'formId' => '4',
     'userId' => '7',
     'group' => 'Registered'
  ),
  1 => 
  (object) array(
     'formId' => '4',
     'userId' => '4',
     'group' => 'Admin'
  )
)

I know I can add an additional value to the array elements by using array_walk, like this;

array_walk($this->viewableFields, function(&$arr) {
    $arr->group = 'Registered';
});

I'm not sure how to retrieve the values from the database though and insert into the existing array.

How can I achieve this?

Use a for loop to loop the array. Then on each interaction, query your database by the user is and array_push to the array with the group received from query — iJamesPHP2, Mar 09 '20 at 10:42
You should be getting all of this in one hit from your database though — iJamesPHP2, Mar 09 '20 at 10:44

yivi · Accepted Answer · 2020-03-09T16:48:34.113

Doing one additional query for each record is a very bad idea, and a clear example of the N+1 problem: where you need to get "N" children of an entity, and only the parent entity was fetched initially.

If your initial query has 100, users, you'll end making 101 queries. If you later need some other unrelated field, this starts to grow out of hand quickly.

This is very bad for performance, and as your application grows in complexity it can very easily have a very serious impact for it.

Assuming you can't modify the initial query and make a simple JOIN query to fetch the required data, it's better to simply make two queries and stitch the resulting data. Even if you can modify the original query this could end up performing better.

How to go about it:

Assuming you have your initial array of objects with a formId and userId public properties, first fetch all the user IDs you are interested in:

$index = [];
$ids = array_reduce($array, function($ids, $object) use (&$index) {
    $ids[]                  = $object->userId;
    $index[$object->userId] = $object;

    return $ids;
});

$ids_for_sql = '(' . implode(', ', $ids) . ')';

You are also building an "index" ($index) to be able to access the objects directly in the array without having to iterate it yet again.

Now you can make the second query you need to get your "group" property:

$groups_query = "SELECT userId, group from users WHERE userId IN $ids_for_sql";

Now it's simply a matter to of "stiching" the data from the two queries:

foreach ($conn->query($groups_query) as $row) {
    $index[$row['userId']]->group = $row['group'];
}

With this, now your original $array containing "user" objects will be correctly updated, and making only 2 queries instead of 11. This performs and scales better, and without adding significant complexity.

You can see a simple, working demo (with arrays instead of SQL queries) here.

Prabhjot Singh Kainth · Answer 2 · 2020-03-09T11:27:11.870

1

First of all we will get value of userID from the array of objects and then query it in database,assuming array as $arr.

foreach($arr AS $key=>$item) 
{
    $userid=$item["userId"];  // getting userId from object
    $query= $con->query("SELECT group from users WHERE userId='$userid'");
    if($query->num_rows>0)  //checking if query has some result
    {
        $row=$result->fetch_assoc();      //fetching the result
        $arr[$key]->group=$row['group'];  // lastly attaching group key to the arr.
    }
}

edited Mar 09 '20 at 11:27

answered Mar 09 '20 at 11:03

Prabhjot Singh Kainth

1,831
2
18
26

@jonboy yes my bad, it should $arr[$key]->group because its an object. – Prabhjot Singh Kainth Mar 09 '20 at 11:25
@RahulKoshti who said you can not fire a query in a loop? – Prabhjot Singh Kainth Mar 09 '20 at 11:25
I think due to this "Its not good.. You cant fire query in loop". – Prabhjot Singh Kainth Mar 09 '20 at 11:28
Also, please consider [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). This way of building queries is open to SQL injection. – El_Vanja Mar 09 '20 at 11:28
Consider the situation when we have more the 1 lac entries in array then ? how much load database will take ?.. No one has asked that dont fire query in loop. – Rahul Koshti Mar 09 '20 at 11:28
1

@RahulKoshti kindly refer to your previous statement you said You can not fire?? Everyone knows that you should avoid multiple requests to the server but OP is not concerned by the speed and optimization. If you are talking about optimization then there are various other factors too. It would be good if present an answer rather than just commenting on other answers. – Prabhjot Singh Kainth Mar 09 '20 at 11:31
Can someone explain the "Its not good.. You cant fire query in loop" to me please? And if possible show a better solution because this one works. – jonboy Mar 09 '20 at 11:31
1

@jonboy you have to store all userids first and then do a mysql query using IN function of mysql . – Prabhjot Singh Kainth Mar 09 '20 at 11:31
1

@jonboy you have to store all userids first and then do a mysql query using IN function of mysql . – Prabhjot Singh Kainth 53 secs ago This is what i was asking that you can do IN rather than firing a query in loop – Rahul Koshti Mar 09 '20 at 11:32
@RahulKoshti Yes, that's indeed a better way to go about it. This solution is bad and can lead to performance problems down the road. Check my answer below for a way to avoid the N+1 problem for this kind scenario, and a better way to do this. – yivi Mar 09 '20 at 15:30

score -2 · Answer 3 · answered Mar 09 '20 at 10:46

-2

You have to foreach items, get ID from array item, and then select data with SQL.

Try:

foreach($array as $key=>$item) {
    $sql = "SELECT group from users WHERE userId = ".$item["userId"];
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        $db_data = $result->fetch_assoc();
        //do stuff with db data

        $array[$key]["group"] = $group; //insert group to this variable
    }
}

answered Mar 09 '20 at 10:46

Marty1452

430
5
19

This will throw an error as $group is undefined. It should have been $db_data['group']. – Prabhjot Singh Kainth Mar 09 '20 at 11:04
@PrabhjotSinghKainth Yeah thats why I write comment if you cant see – Marty1452 Mar 09 '20 at 12:50

How to add information from the DB to an existing array of objects?

3 Answers3

How to go about it: