Option to add column(s) and/or create a table in MySQL if the table already exists?

Question

I am trying to run a script to automate upgrading of some db tables but some systems won't have the table exist at all. How to best write, in MySQL, something like this:

1. I have an existing table with two columns, address and city. I want to add column version OR create a new table for systems that dont have the table at all, so:

2. CREATE table 'hello' ( address text NOT NULL,
   city tinytext NOT NULL, version tinytext NOT NULL, )

3. However, IF 'hello' exists but does not have column version, preserve table but add new column?

4. In 'version' column, add the value 'old' for all the preserved data, but add 'new' for all the new entries I will populate the db with.

5. Futureproof this so I can always add columns, checking in iteration as I upgrade different versions of the software?

EDIT: Some PHP code on the actual upgrade script would be helpful; imagine I have one "activation" function that should check for table, create it or otherwise modify it...

Answer 1

Answering your comment for how to check if the table exists if you can't or don't want to use CREATE TABLE IF NOT EXISTS tablename:

<?php

$db_name    = 'mydb';
$table_name = 'table';

mysql_connect("localhost", "mysql_user", "mysql_password");
$result = mysql_list_tables($db_name);
$num_rows = mysql_num_rows($result);

for ($i = 0; $i < $num_rows; $i++) {
  if (mysql_tablename($result, $i) == $table_name) {
    // the specified table exists, do what you need to
    break;
  }
}

?>

Answer 2

Check if table exists: http://forums.mysql.com/read.php?101,112346,112672#msg-112672

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename';

Check if row exists in a table: Check if a row exists, otherwise insert

I'm not sure what you mean by future proof? These are pretty general SQL queries so it should be pretty portable. the only difference is the table check is mysql specific but changing that to another database shouldn't be a big deal.

Answer 3

Use ALTER table in SQL, in your php script.

Ref : http://www.w3schools.com/sql/sql_alter.asp

Best,