MySQL ForEach Loop
This tutorial guides how to simulate the foreach loop in MySQL using INSERT, SELECT, WHERE and JOIN in one statement.
MySQL foreach Loop
To understand the foreach loop simulation, let’s create three tables whose names and attribute names are given below.
Tables and Respective Attributes:
users -> id, user_name, person_id, email
person -> id, person_name, address_id
address -> id, email
We use the following queries to create the tables.
# 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)
);
Further, use the MySQL queries below to insert data into the tables 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 the SELECT statements to look at the 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)
We are supposed to create a new record in the address table if the users.person_id is NOT NULL and person.address_id is NULL. The newly created row will have the exact email as users.email.
Here, we can simulate the foreach loop in the following manner.
# 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 the SELECT query to see 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 execute the above query using cursor and procedure.
