MySQL insert mutiple rows based on SELECT query result

insert, mysql, php

Solution

One thing you have to know is that the number of columns returned by your query must match the number of columns you want to insert into

"INSERT INTO NewTable(plan_name, plan_time)
    SELECT cp.plan_name, cp.plan_time 
    FROM courses c
         INNER JOIN course_to_plan cpl   ON cpl.course_id = c.course_id
         INNER JOIN courseplans cp       ON cp.plan_id = cpl.plan_id
    WHERE cpl.course_id = '$course_id'"

Warning: watch out for sql injection through `$course_id`.

Note that I specified 2 columns in my INSERT statement because the SELECT query return 2 columns

If the number of columns in your table matches the number of columns returned by the query exactly, then you do not need to specify the columns.

Problem

I have a MySQL query like this: ``` SELECT cp.plan_name, cp.plan_time FROM courses c INNER JOIN course_to_plan cpl ON cpl.course_id = c.course_id INNER JOIN courseplans cp ON cp.plan_id = cpl.plan_id WHERE cpl.course_id = '$course_id'; ``` This will output data like for example: ``` +----------------------------+-----------+ | plan_name | plan_time | +----------------------------+-----------+ | Plan number one name | 6 | | Plan number two name | 6 | | Plan number three name | 10 | +----------------------------+-----------+ ``` I want these rows to be inserted in a new table on a form submission. How do I continue to code my `update.php` to make it insert the values in the table `newtable`? ``` if (isset($_POST['submit'])) { $course_id = $_POST['course_id']; $course_result = mysql_query ("SELECT cp.plan_name, cp.plan_time FROM courses c INNER JOIN course_to_plan cpl ON cpl.course_id = c.course_id INNER JOIN courseplans cp ON cp.plan_id = cpl.plan_id WHERE cpl.course_id = '$course_id'"); /* I want the result of the above rows to be inserted in the table newtable which has the columns plan_name, plan_time */ ``` I hate to admit that I'm completely useless in PHP and MySQL but I'm trying to learn. I guess I have to create some kind of array to store the result and then loop through the inserts but I don't know how.

Original source