CODEWITHSUNDEEP
phpmysqlforeach
Pr0metheus
Pr0metheus

Reputation: 37

How to insert second foreach query inside a foreach query?

I'm trying to insert random data with php inside ipromapp database I created (mysql) with help of Faker - which I imported with composer (it's working fine).

I established the connection - it's working.

$connection = mysqli_connect('localhost','root','root', 'ipromapp');

created class Bank and a method randomize() - inside I have this code that is not working fully correctly. It creates 5 users (so the first query is working properly) - the second query that I made inside another foreach (I need random number of transactions, so I cant use the range from the first foreach - 1 to 5).

I've correctly index-ed and created a relationship with the user_id from transaction table to id(primary key) of user table (ss : http://prntscr.com/mdxfs1 )

I've tried to edit the query, change the range of the second foreach - I'm having some minor "light bulbs" moments but I'm having hard time converting them into code.

    function randomize()
    {
        // accessing variables outside the function
        global $faker, $connection;
        $insertNum = 0;
        // deleting users so that we have only 5 we want
        $connection->query("TRUNCATE TABLE `users`");
        $connection->query("TRUNCATE TABLE `transactions`");


        // adding 5 people into the users db
        foreach (range(1,5) as $x){
            $body = $faker->firstName($gender = null) . ' ' . $faker->lastName;
            $connection->query("
                INSERT INTO users (name, birth_date)
                VALUES ('{$body}', '{$faker->date($format = 'Y-m-d', $max = '-10 years')}')
            ");
            printf ("New Record has id %d.\n", $connection->insert_id);

            // random number of transactions

            $randomNum = rand(20,40);
            $insertNum += $randomNum;
            printf ( "Random number is %d.<br>", $randomNum);
            // adding random number of transactions from past 6 months
            foreach (range(1, $insertNum) as $y) {
                $connection->query("
INSERT INTO transactions (user_id, date, deposit, withdraw)
VALUES ('{$connection->insert_id}','{$faker->date( $format = 'Y-m-d', $max  = 'now')}', '{$faker->randomDigit}', '{$faker->randomDigit}')
                    ");
            }
        }
    }

I've expected for the query to generate from 20-40 transactions PER user. I think something has to do with id's? Query generates only 1 - so something is working but I'm guessing the query or the logic is not correct - if someone can point me to the problem if he saw anything particular?

SS's from phpMyAdmin output after I run the php file: // output in the browser (i'm using MAMP and just ran localhost) http://prntscr.com/mdxe4l

// 5 generated users http://prntscr.com/mdxee9

// For some reason the first time I run the script it generates 2 for the first ID http://prntscr.com/mdxgc3

Upvotes: 1

Views: 207

Answers (1)

Samer
Samer

Reputation: 988

In your second foreach loop, you're using $connection->insert_id to get the user's ID, but what's happening is that after the first loop, it's fetching the ID of that transaction that was just inserted and probably throwing errors that aren't being displayed.

What you can do is assign the user's ID to a variable before the loop:

$userId = $connection->insert_id;
foreach (range(1, $insertNum) as $y) {
    $connection->query("
INSERT INTO transactions (user_id, date, deposit, withdraw)
VALUES ('{$userId}','{$faker->date( $format = 'Y-m-d', $max  = 'now')}', '{$faker->randomDigit}', '{$faker->randomDigit}')
        ");
}

Upvotes: 2

Related Questions