Geek asks and answers

MySQL inserts multiple rows based on the result of a SELECT query

I have a MySQL query as follows:

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';

Data will be displayed here, 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 when submitting the form.

How do I continue my code update.phpso that it inserts values ​​into a 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 think I need to create some kind of array to store the result, and then scroll through the inserts, but I don't know how to do this.

+5
source share
2 answers

One thing you should know is that the number of columns returned by your query should 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'"

: sql $course_id.

, 2 INSERT, SELECT 2

, , .

+11

PHP-, mysql insert/select, :

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'
+3

More articles:


All Articles