MySQL insert mutiple rows based on SELECT query result

Question

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.

Answer 1

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.

Answer 2

Not sure about the php code but if you change your mysql query to an insert/select it should work:

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'