Quick way to compare state of MySQL table to xml file

[ali.f]

I am looking for a quick way to modify or add to a MySQL table based off a simple comparison of say the string it would take to build that table. For example, if I have a table in MySQL already with some columns with specific attributes, is there a way to query MySQL to get the string that would create exactly that table. Let's say I have a table that has two columns, one is an int called id that is auto_increment and PRIMARY KEY and the other is name and is a varchar(40). Assume the table already exists and is called "users." Can I query MySQL to get the string "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40));" This will allow me to quickly compare it to an xml file that I have that will represent what I really want in the database. The reason I am doing this is so that if I decide to modify my database, I simply have to change an xml file and the php code will fix the database the next time that script runs. For example, lets say at another point in time I decide I want to start tracking lastnames so I would change my XML file and add a column called "lastname" and then the next time my php code executed it would look at what it would take to make the table called "users" and see that I am missing a column, so it would add the column "lastname" to "users." Another example of where this would be nice is that if I deploy my website to another webserver I wouldn't have to generate the database on that server because the php code could "rebuild" itself from the xml files.Instead I am having to do silly comparisons... My PHP function that Checks the database\[code\]function checkForTable($tablename) { //First line in the simplexml for php, load the file $parser=simplexml_load_file('tables.xml'); //Loop through the different "tables" to find the one //that has the attribute of name equal to the passed in //variable. foreach($parser->table as $atable) { if($atable["name"]==$tablename) $thetable = $atable; } $doesTableExist = false; $query = "SHOW TABLES"; $results = mysql_query($query,$_SESSION['db']) or die(mysql_error()); while($row = mysql_fetch_array($results)) { if($row[0] == $tablename) $doesTableExist = true; } if(!$doesTableExist) { $query = 'CREATE TABLE '.$thetable["name"].'('; $prefix = ""; foreach($thetable->column as $column) { $query = $query.$prefix.$column['name'].' '.$column['type'].' '.$column['extra']; $prefix = ","; } $query = $query.')'; $result = mysql_query($query,$_SESSION['db']) or die(mysql_error()); } else { $currentTable = array(); $query = 'SHOW COLUMNS from '.$thetable['name']; $results = mysql_query($query, $_SESSION['db']) or die(mysql_error()); //Dump the results into the array while($row=mysql_fetch_array($results)) { array_push($currentTable,$row); } //Loop through the xml file foreach($thetable->column as $column) { $foundcolumn = false; //Init this variable to false and set it to true if you find a match foreach($currentTable as $row) //Loop over the columns for the table that we are testing { if($column['name'] == $row['Field']) //If we find a match, remember the row that we matched! { $foundcolumn = true; $matchingRow = $row; } } if(!$foundcolumn) //If we didn't find matching column from the xml file { $query2 = 'ALTER TABLE '.$tablename.' ADD '.$column['name'].' '.$column['type'].' '.$column['extra']; echo $query2; mysql_query($query2, $_SESSION['db']) or die(mysql_error()); } else { //The column was found, so do we need to update the column in anyway? } } } }\[/code\]My XML file that the PHP script reads to rebuild the database\[code\]<database> <table name="users"> <column name="id" type="INT" extra="AUTO_INCREMENT PRIMARY KEY"/> <column name="username" type="VARCHAR(32)" /> <column name="password" type="VARCHAR(256)" /> <column name="creationDate" type="DATETIME" /> <column name="validationCode" type="VARCHAR(5)" /> <column name="loginAttempts" type="INT" /> <column name="lastLoginAttempt" type="DATETIME" /> <column name="accountStatusCode" type="INT" /> <column name="test" type="INT" extra=""/> </table> <table name="userinformation"> <column name="userid" type="INT" extra="PRIMARY KEY"/> <column name="birthday" type="DATETIME"/> </table></database>\[/code\]