MYSQL使用循环填充数据

Question

I am a begineer in mysql.

I have a task. There are 3 tables

purchase_master

"autoid","user_id","package_id"

user_master

"autoid","user_id","name","user_email","user_pass"

package_master

"poster_path","overview","release_date","genre_ids","package_id","original_title"

My requirement is that purchase_master table should be populated such that it contains all packages from package_master against every user from user_master.

e.g., if there are 10 users in user_master and 10 packages in package_master, so the purchase_master will have 100 rows (10 user * 10 packages). So each user has all 10 packages.

I dont have any clue how to do it in mysql, else I have to find a way to do this in PHP via looping.

Answer 1

Try something like this

INSERT INTO purchase_master ("user_id","package_id")
  SELECT um."user_id", pm."package_id"
  FROM user_master as um
  JOIN package_master as pm

Hope this helps

Documentation: https://dev.mysql.com/doc/refman/5.7/en/insert-select.html