inserting values to new table in mysql_query

Question

Okay guys, I will try to share my guestion understandable. So I have 2 tables like below: ALL Id from TABLES are Autoincrement.

Number

id  number

10   100
11   102
12   105
13   106

subnumber

id  number_id   subnumber

52     10           10
53     11           15
54     13           40

You see some numbers (not all) have subnumbers. I mean: From table number the number where id = 13 has subnumber and it is equal to 40. I want to save this table in another table.

$sql1=mysql_query('SELECT number FROM number');


$while(fetch1=mysql_fetch_array($sql1))
{
   mysql_query('
   INSERT INTO `save_number` (number)'
   VALUES ('.$fetch1['number'].');
   );
}


save_number

id      number
100      100
101      102
102      105  
103      106

Now i gonna save table (subnumber) in another table (save_subnumber)

$sql2=mysql_query('SELECT number_id, subnumber FROM subnumber');

$while(fetch2=mysql_fetch_array($sql2))
{
   mysql_query('
   INSERT INTO `save_subnumber` (number_id, subnumber)'
   VALUES ('.$fetch2['number_id'].', '.$fetch2['number'].');
   );
}

save_subnumber

id  number_id   subnumber
60     10           10
61     11           15
62     13           40

So, you see the number_id FROM save_subnumber is not equal to new inserted id FROM save_number. I would be appreaciated if anyone help me. By the way I am still using mysql_query. I can not find time to improve my SQL to PDO :)

What are you trying to accomplish here? If you are trying to link save_number to number, you need to have a number_id in that table as well, or are you linking based on the actual number? What should save_subnumber actually be storing? the id from save_number as number_id? — Zack Newsham, Sep 24 '13 at 07:13

Mchl · Accepted Answer · 2013-09-24T14:56:31.990

2

Your first query can be changed to

INSERT INTO
  save_number (number)
SELECT
  number
FROM 
  number

This will save you using PHP to iterate through rows and will be faster. A lot.

Having this in mind your second query would be

INSERT INTO
  save_subnumber (number_id, subnumber)
SELECT DISTINCT
  sn.id, s.subnumber
FROM
  saved_number AS sn
CROSS JOIN
   number AS n
USING (number)
INNER JOIN
  subnumber AS s
ON
  n.id = s.number_id

edited Sep 24 '13 at 14:56

answered Sep 24 '13 at 07:14

Mchl

61,444
9
118
120

Man, at the last line there is something wrong. there is not number_id in sn (save_number) – Sep 24 '13 at 13:09
It's suppposed to be `s.number_id` – Mchl Sep 24 '13 at 13:10
If the count of number>1 then the count of subnumber equal to count of number * 2 How to fix it? – Sep 24 '13 at 14:36
1

Try using SELECT DISTINCT – Mchl Sep 24 '13 at 14:38

score 1 · Answer 2 · answered Sep 24 '13 at 07:11

1

If you just want to copy one table in another why don't you use something like this

INSERT INTO save_number SELECT * FROM number;

answered Sep 24 '13 at 07:11

Dexa

1,641
10
25

inserting values to new table in mysql_query

2 Answers2