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