Question 42142 in PHP Question posted in
Official manual can be found here.

using php/mysql to compare two tables and add missing columns from table1 to table2

Ok, Let’s say I have two tables with identical structures but different values :

TABLE1 : column.A, column.B, column.C
TABLE2 : column.A, column.B, column.C

I add columns in table 1, it becomes :

TABLE1 : column.A, column.B, column.C, column.D, column.E, column.F ... column.M
TABLE2 : column.A, column.B, column.C

Now, using php/mysql I want to compare TABLE1 and TABLE2 and add in TABLE2 missing columns from TABLE1. I’m talking structure only (columns, not values). I could do it manually, but I want to avoid it.

Is there a way to do it ?
I can’t find any.

EDIT :
I just found out about describe ( https://electrictoolbox.com/mysql-table-structure-describe/ ) which might be a great help on this. I’ve read all the answers, which look great but I think the best way would be to use DESCRIBE on both tables and then compare the results to add the missing columns to table2 using informations from the DESCRIBE query of table1

EDIT2: Bill Karwin answer is the best one to me. He uses information_schema which is better than DESCRIBE (only difference would be that it could be slower). The only thing after that would be to ALTER TABLE table2 with the informations we got from the query.

Tags:

3

Answers


  1. 0

    (My answer may be Oracle-specific, but I’m hoping it will lead you to a MySQL solution.)

    I’d perform this database operation all inside a database procedure (which you can call from PHP if you want).

    In Oracle (which may not help you much), I would:

    SELECT column_name FROM user_tab_columns WHERE table_name = ‘TABLE1’
    MINUS
    SELECT column_name FROM user_tab_columns WHERE table_name = ‘TABLE2’;

    (I believe MySQL has similar information_schema built-in tables).

    Then put together an alter table statement as such:

    EXECUTE IMMEDIATE ‘ALTER TABLE TABLE2 ADD (‘ & … comma seperated results from the above SQL … & ");"

    and get data types in the first query too, to include in the alter statement.

    -mobailey

    Login or Signup to reply.
  2. 0 
    create table table1 ( a int, b int, c int, d int, e int);

create table table2 (a int, b int, c int);

select t1.column_name
from information_schema.columns as t1
left outer join information_schema.columns as t2
  on t1.table_schema = t2.table_schema
  and t1.column_name = t2.column_name
  and t2.table_name = 'table2'
where (t1.table_schema, t1.table_name) = ('test', 'table1')
  and t2.table_name is NULL;

+-------------+
| COLUMN_NAME |
+-------------+
| d           |
| e           |
+-------------+

    To produce the necessary ALTER TABLE statement to add the columns, you’ll need to inspect the rest of the columns of that information_schema table. See https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html

    Login or Signup to reply.
  3. 0

    Doing the task manually is not that complex, and is probably fewer keystrokes than trying to cobble the equivalent from information_schema.columns, etc.

    1. SHOW CREATE TABLE for each table.
    2. Spot the ‘new’ columns.
    3. Copy & paste them into an ALTER TABLE along with ADD COLUMN in front of each.
    4. Execute the ALTER.

    If you need to do this on lots of tables, then I have to ask "why".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search