How to stop second foreach from looping more than once

Question

I have an query which select all ids from a table. Once I have all id's, they are stored in an array which I foreach over.

Then there is an second array which pull data from url (around 5k rows) and should update DB based on the id's.

The problem - second foreach is looping once for each ID, which is not what I want. What I want is to loop once for all id's.

Here is the code I have so far

$query = "  SELECT id, code FROM `countries` WHERE type = 1";
$result = $DB->query($query);

$url = "https://api.gov/v2/data?api_key=xxxxx";
    $api_responce = file_get_contents($url);
    $api_responce = json_decode($api_responce);
    $data_array = $api_responce->data;
    
$rows = Array();
while($row = $DB->fetch_object($result)) $rows[] = $row;

foreach ($rows as $row) {
 
    foreach ($data_array as $key => $dataArr) {
    
        $query = "UPDATE table SET data_field = $dataArr->value WHERE country_id = $row->id LIMIT 1";
    
    }
}

The query returns 200 id's and because of than the second foreach (foreach ($data_array as $key => $dataArr) { ... }) execute everything 200 times.

It must execute once for all 200 id's not 200 * 5000 times.

If you don't want something to repeat, then don't use a loop... It sounds like what you actually want is for each entry from `$data_array` to match up with some particular entry from `$rows`; you need to tell PHP which one somehow. Since you haven't mentioned what that matching rule is, it's hard to be more specific than that. — IMSoP, Jun 27 '22 at 10:45
I want `$dataArr->value` to be updated for each `$row->id` once. Currently this happens 5000 times per `$row->id`. I'm not matching them. Just taking ID's from `countries` and then update each id but I should loop through `data_array` since I need to get `$dataArr->value`. May be I'm missing something here and don't get it quite well. — S.I., Jun 27 '22 at 10:54
Imagine you have a list of letters A to Z, and a list of numbers 0 to 9; you can make pairs like A-1, A-2, B-1, B-5, Z-3, Z-9... 260 different combinations (26 x 10). If you only wanted 26 combinations, you've got to have some logic to decide which number goes with which letter. The same thing is happening here, but you have one list with 200 things and one with 5000 things, so 1000000 different combinations (200 x 5000). Your current loop is generating all 1000000 combinations, because you haven't got any logic saying which of those combinations you actually want. — IMSoP, Jun 27 '22 at 11:01
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Jun 27 '22 at 11:01
That's interesting and didn't though of it. The only thing I can see for matching is that `country_code` which is inside `data_array` but this still require loop all elements in the json array and then matching. I'm not sure how can I make it. — S.I., Jun 27 '22 at 11:09

Rantouan Achmet · Accepted Answer · 2022-06-27T11:55:52.593

Since the question is aboot using a loop, we will talk about the loop, instead of trying to find another way. Actually, I see no reason to find another way.

->Loops and recursions are great, powerful tools. As usually, with great tools, you need to also find ways of controlling them. See cars for example, they have breaks. The solution is not to be slow and sit to horses era, but to have good brakes.

->In the same spirit, all you need to master the power called recursions and loops is to stop them properly. You can use if cases and "break" command in PHP.

For example, here we have a case of arrays containing arrays, each first child of the array having the last of the other (1,2,3), (3,4,5) and we want to controll the loop in a way of showing data in a proper way (1,2,3,4,5). We will use an if case and a counter :

<?php
$array = array( array(-1,0,1), array(1,2,3,4,5), array(5,6,7,8,9,10), array(10,11,12,13,14,15) );

static $key_counter;
foreach( $array as $key ){
    $key_counter = 0;
    foreach( $key as $key2 ){
        if ( $key_counter != 0 ) {
            echo $key2 . ', ';
        }
        $key_counter = $key_counter + 1;
    }
}

Since I dont have access to your DB is actually hard for me to run and debbug the code, so the best I can say is that you need to use an if case which checks if the ID of the object is the ID we want to proccess, then proceed to proccessing.

P.S. Static variables are usefull for loops and specially for recurrsions, since they dont get deleted from the memory once the functions execution ends.

The static keyword is also used to declare variables in a function which keep their value after the function has ended.

Thanks, not exactly what I searching for but will help to continue. — S.I., Jun 27 '22 at 11:27
Regarding "reason to find another way", at a certain point performance may matter, once you end up looping over a million, a billion, a trillion combinations, when you only need a tiny fraction of the actual combinations. — IMSoP, Jun 27 '22 at 11:37

How to stop second foreach from looping more than once

1 Answers1