JIYIK CN >

This article describes how to simulate a foreach loop in MySQL using INSERT, SELECT, WHERE, and JOIN in one statement.

MySQL foreach loop

To understand foreach loop simulation, let us create three tables with the following names and attribute names.

Tables and their respective properties:

users -> id, user_name, person_id, email
person -> id,  person_name, address_id
address -> id, email

We use the following statement to create the table.

# create `address`, `person`, and `users` tables
CREATE TABLE address(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(45) NULL
);

CREATE TABLE person (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  person_name VARCHAR(45) NOT NULL,
  address_id INT NULL,
  FOREIGN KEY (address_id) REFERENCES address(id)
  );

CREATE TABLE users (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(45) NOT NULL,
  person_id INT NOT NULL,
  email VARCHAR(45) NOT NULL,
  FOREIGN KEY (person_id) REFERENCES person(id)
  );

Also, use the following MySQL query to insert data into the table we just created.

# insert data into the `address` table. Here
# we have parenthesis only because one field
# is `auto_incremented`, and the other can accept
# `null`.
INSERT INTO address () VALUES (),(),(),();

# insert data into the `person` table
INSERT INTO person (person_name)
VALUES
('Thomas Christopher'),
('Jim James'),
('Mehvish Ashiq'),
('Saira Daniel');

# insert data into the `users` table
INSERT INTO users(user_name, person_id, email)
VALUES
('chthomas', 1, 'chthomas@yahoo.com'),
('jjames', 2, 'jimjames@gmail.com'),
('mehvishashiq', 3, 'mehvish@yahoo.com'),
('danielsaira', 4, 'sairad@gmail.com');

Next, we use a SELECT statement to view the most recent data.

# display data for all the specified tables
SELECT * FROM users;
SELECT * FROM person;
SELECT * FROM address;

Output (for the users table):

+----+--------------+-----------+--------------------+
| id | user_name    | person_id | email              |
+----+--------------+-----------+--------------------+
|  1 | chthomas     |         1 | chthomas@yahoo.com |
|  2 | jjames       |         2 | jimjames@gmail.com |
|  3 | mehvishashiq |         3 | mehvish@yahoo.com  |
|  4 | danielsaira  |         4 | sairad@gmail.com   |
+----+--------------+-----------+--------------------+
4 rows in set (0.00 sec)

Output (for the person table):

+----+--------------------+------------+
| id | person_name        | address_id |
+----+--------------------+------------+
|  1 | Thomas Christopher |       NULL |
|  2 | Jim James          |       NULL |
|  3 | Mehvish Ashiq      |       NULL |
|  4 | Saira Daniel       |       NULL |
+----+--------------------+------------+
4 rows in set (0.00 sec)

Output (for the address table):

+----+-------+
| id | email |
+----+-------+
|  1 | NULL  |
|  2 | NULL  |
|  3 | NULL  |
|  4 | NULL  |
+----+-------+
4 rows in set (0.11 sec)

If users.person_id is not null and person.address_id is null, we should create a new record in the addresses table. The newly created row will have the exact same email as users.email.

Here, we can simulate the foreach loop in the following way.

# simulation of `foreach loop`
INSERT INTO address (email) SELECT users.email
FROM users JOIN person ON users.person_id = person.id
WHERE person.address_id IS NULL;

Next, use a SELECT query to view the updated data.

SELECT * FROM address;

Output:

+----+--------------------+
| id | email              |
+----+--------------------+
|  1 | NULL               |
|  2 | NULL               |
|  3 | NULL               |
|  4 | NULL               |
|  5 | chthomas@yahoo.com |
|  6 | jimjames@gmail.com |
|  7 | mehvish@yahoo.com  |
|  8 | sairad@gmail.com   |
+----+--------------------+
8 rows in set (0.00 sec)

However, we can also use cursors and procedures to perform the above query.