CODEWITHSUNDEEP
phpmysqlinsert
David
David

Reputation: 1191

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 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.

Upvotes: 8

Views: 14034

Answers (2)

codingbiz
codingbiz

Reputation: 26396

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.

Upvotes: 18

Kyra
Kyra

Reputation: 5417

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'

Upvotes: 4

Related Questions