Create a table for other tables

Question

This seems like a simple problem, but after some time of searching I can not understand the answer.

Currently, I have a MySQL table in my local database used by webapp, and the same table in the database on the remote server. Right now, I am using the CREATE TABLE IF NOT EXISTS command via PHP to create a table in databases:

 CREATE TABLE IF NOT EXISTS users ( `id` int(10) NOT NULL AUTO_INCREMENT, `username` varchar(18) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; 

However, suppose I make changes to the local database, for example, adding a column. It would be very unpleasant if I change the local local database and change it. Is there an easier way to run code to create a table if it does not exist, and if it exists, make sure that the structure matches the structure of the table structure?

Here is an example to make what I'm trying to convey a little clearer. Let's say I have a user table in the local database, and I decided that in my webapp I want to have another password column. So I go to the local database and add the password column. Is there any PHP / MySQL code that I can run to check if the user table exists, and if so, make sure it has a password column, and if not, add it?

Answer 1

To answer your question, you can create temporary procedures to detect the existence of a field, for example, using the following query:

SHOW COLUMNS FROM table_name LIKE 'column_name';

However, in the real world, database changes come down to three scenarios. Create a script and two deltas one up and one down. Then the database is versioned so that you know what state the database is at any given time.

Answer 2

What you are really looking for is migrations, for example. You are looking for a schema management tool that allows you to manage the database structure in versions with code versions.

For example, for the described scenario, first create a script to create the table, for example. 001_create_user_table.sql . You should then use the schema manager to connect and deploy these changes to your databases.

If you want to change or add something, just write another script, for example, 002_Add_Password_Column_To_User_Table.sql . Only fill in the code to make this change. Then run the schema manager again.

Typically, you tell Schema to bypass all existing migration files. Each time you start, Schema Manager updates the table of changes in the database, so when it starts, it will know which of your scripts it should use.

Well, you can add these migrations to your regular VCS, so you will always know which database schema you had, in which version of your application. And you will have the correct change log for them.

Answer 3

To specifically check the password column, you can use DESCRIBE :

 $colExists = false; $res = mysql_query('DESCRIBE `users`'); while ($row = mysql_fetch_assoc($res)) { if ($row['Field'] == 'password') { $colExists = true; break; } } if (!$colExists) { // create column } 

However, you should check replication or some other automated tool to make sure they will be the best solution for you.

Answer 4

Follow these steps (you can easily implement this in PHP, I assumed the table name is Foo)

1.) Run the following code:

 desc Foo 

2.) Based on the results of the first step, you can create the create table command (and you should)

3.) Save the data from the existing table, which will be replaced in a variable (Optional, you only need this if you can use the data from the old table)

4.) Modify the extracted rows from step 3.) so that they are compatible with your new definition (optional, you only need this if you can use the data from the old table)

5.) Get rows from your new Foo table

6.) Combine the results obtained in steps 4.) a 5.) (Optional, you only need this if you can use the data from the old table)

7.) Run the drag table for the old table

8.) Create a replacement in the command to insert all your rows into the newly created Foo table (you can read about it here )

After these steps, you will end up with a new version of the table. If your tables are too large, you can run the CREATE TABLE IF NOT EXISTS command, and if that fails, run the alter command.

In addition, you can make a library to complete these steps and will use it in the future instead of solving the same problem several times.

EDIT: You can connect the database using this function: mysql-connect (documentation here ) You can run the query using this function: mysql-query (documentation here )

Based on the first step, you will get the names of the fields (let it be assumed that you store them in the $ bar variable), and you can use your result to generate your selection command (connecting to a database where you have important data. There may be both) :

 $field_list = "1"; foreach ($bar as $key => $value) $field_list.= ",".$bar[$key]; mysql_connect(/*connection data*/); mysql_query("select ".$field_list." from Foo"); 

You can use your new resource to create an insert command to insert all your important data after removing the rest (read about resources here , how you can generate your insert, you can read here , but I suggest that you replace instead its insert, which works like an insert, except that it replaces the string, if it already exists, is better here than the insert, read here )

So, use mysql_connect and mysql_query, and the resource returned by mysql_query can be used as a replacement in the future (I have now linked the URL for everything you need, so I'm sure you will solve the problem.), Apologies for were not specific enough before.