Update mysql database schema

[KKit]

We maintain 1 databases (mysql) that will often have columns added to a particular table.

Table1 is a master table with virtually no data in it. Its sole purpose is to accept new unaudited information from our partners. Once the info is accepted, the data will dump into table2.

Table2 is the working database.

Both databases should be the same structure.

We will often get information from our partners which make us have a need to add a column. Because table1 is a direct import, the columns are created automatically. But we have to manually change the columns in table1 for all of our queries to work.

I want to use php in our application to look at the structure of table1, detect the columns which are missing in table2, and automatically make the structure of table2 the same as table1.

Any help?

 

[jpadie]

download the latest version of wordpress. search for the dbdelta function in the code and extract it.

you will either need to recode the dbdelta function to use native mysql calls or also extract the wpdb.php file from the wordpress install and instantiate a wpdb object in the global scope before making use of the dbdelta function

then use this script

$result = mysql_query('show create table table1');
list ($schema) = mysql_fetch_array($result);
$schema = str_replace('table1', 'table2', $schema);
dbdelta($schema, true);

obviously you will want to backup your data tables before doing this each time.

the alternative approach would be to do the following:

1. alter the old table2 to change its name to a backup value. keep a note of the backup value
2. run this query 'create table table2 like table1'
3. and then this query 'insert into table2 select * from table2backupname';

i may have my query language wrong, but this is a php forum not a mysql forum.

 

[ingresman]

Or if your feeling up to it, check out

http://uk2.php.net/manual/en/function.mysql-list-fields.php

and the related functions which allow you to read the structure of the table and use as you wish.

 

[KKit]

Thanks to both.

@jpadie:
I have never worked with wordpress, but I find this suggestion very interesting. I will check it out. thank you..

@ingresman:
Although I am a coder, I come from a Visual Basic world, and have only been doing php coding for a couple of months. I can get the field names, but what I can't seem to figure out is how to store the schema, and then have it apply that schema through the code. Many hours trying different things, and at the end of the day I can only get it and store it, but can't seem to make the system apply/add a column based upon a variable.

With a little push in the right direction, I can probably get it...but just can't seem to figure out the "how to apply" portion of the equation.

 

[jpadie]

no need to use the whole wordpress installation. dbdelta is a function that is only a couple of hundred lines long.

 

[ingresman]

By applying the schema do you mean amending the select's to reflect the new columns ?
If so you can use jpadies method or mine to get the columns but all you probabbly need to do is use either method to read the table defintion and generate your queries according i.e. in a loop and keep adding ,colname as you go. Creating variables dynamiclay is fairly easy in PHP as well.
Is this what you mean or have I missed something ?

 

[jpadie]

i understood the OP to say that the actual table def changed regularly and the working table needed to reflect the current state of the template table. if i have understood wrong then my proferred solution is almost certainly wrong too.

 

[ingresman]

Yes my understanding to, until the last post.