David David - 3 months ago 8
MySQL Question

MySQL insert mutiple rows based on SELECT query result

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 wich 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 somekind of array to store the result and then loop through the inserts but I don't know how.

Thank's in advance.

Answer

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.