CODEWITHSUNDEEP
phpmysqlsqlinsert
user2767715
user2767715

Reputation:

Issue while copying tables on PHP SQL

All id are auto_increment. I will try explain my issue understandable. I just want copy 2 tables to another 2 tables. Here my tables at below:

table1

id  number

10   100
11   102
12   105
13   106

table2

id  number_id   subnumber

52     10           10
53     11           15
54     13           40

You can see there is subnumber of some numbers. For example, WHERE id =11 from table1 has a subnumber on table2 and it is equal to 15. Now I should copy the table1 to the table : copy_table1

$sql1 = mysql_query('INSERT INTO
          copy_table1 (copy_number)
        SELECT
          number
        FROM
          table1');

And the results of copy_table1 are:

copy_table1

id      copy_number
100      100
101      102
102      105  
103      106

Then I should copy table2 to another table copy_table2:

$sql2 = mysql_query('INSERT INTO
          copy_table2 (copy_number_id, copy_subnumber)
        SELECT
          number_id, copy_subnumber
        FROM
          table2');

And the results of copy_table2 are:

id  copy_number_id   copy_subnumber
60        10              10
61        11              15
62        13              40

So, lets check the tables: copy_table1 and copy_table2. You see, ID from copy_table1 is not equal and suitable to copy_number_id FROM copy_table2 And it brings me problem. How can I do that after copying tables id and copy_number_id will be suitable to each other?

Upvotes: 0

Views: 71

Answers (1)

user2767715
user2767715

Reputation: 

$sql2=mysql_query('INSERT INTO
   copy_table2 (copy_number_id, copy_subnumber)
SELECT DISTINCT
   cp1.id, t2.subnumber
FROM
   copy_table1 AS cp1
CROSS JOIN
   table1 AS t1
USING (number)
INNER JOIN
   table2 AS t2
ON
   t1.id = t2.number_id ');

Upvotes: 1

Related Questions