Create Table If It Does Not Exist (and The Same For Rows)

[kvnmck18]

I am trying to write some code that creates a table if it does not exist. So when I run a process it checks to see if the table exits: if table does not exist - it creates the table, if the does exist it - it then checks to make sure fields exist: if fields don't exists it adds them - once this is done it inserts the data.Option 1 - Table does not exist

1. Creates table
2. Checks to see if fields exist
3. alters the table to add fields (if needed)
4. inserts data

Option 2 - Table exists

1. Checks to see if fields exist
2. alters the table to add fields (if needed)
3. inserts data

How can I do this? From what I understand the "CREATE [TEMPORARY] TABLE [iF NOT EXISTS]" does not work entirely -- and at least for me has resulted with errors. AHHHHH!!!!!!!!!

[jeffman]

Great thing about MySql and PHP. If you query the commands to create a table or add fields, and any of that stuff already exists, the query does not overwrite the existing data. MySql does, however, stop execution at the first error (not PHP, just MySql). So go ahead and create your table. Then add your fields, one at a time. Then write your data. If you're checking for errors along the way, tell that the error-checker to ignore errors 1050 and 1060. If one of those errors comes up, the table won't be affected, your PHP script keeps executing, and your insert statements should execute just fine.

[kvnmck18]

So are you suggesting insert data... if error happens add a field... try again... then if that doesn't work, create table... and go back to insert the data?So use the errors as a way to create and amend tables?

[jeffman]

Close. I check for errors, but I dont use the result of that check to change the program flow. I just check to see if an error is fatal to the operation. Which is exactly my point. Run the routine from the top down and just keep going. Create the table. If the table already exists, just keep going. Add the fields. If the fields already exist, just keep going. Now insert your data. The way I read things, mysql_query() returns FALSE if there's a mysql error, instead of throwing a warning, exactly for this reason: so you can move past the trivial errors and get on with the job. Maybe I'm wrong. Somebody smarter should tell me. :)Here's a chunk of the script I used to verify that this would work:

function check ($r) {	if (!$r) {		$err = mysql_errno();		if ($err == 1050 || $err == 1060) {			return;		} else {			die ("Error: " . $err . ", " . mysql_error());		}	}}$Q = "CREATE TABLE IF NOT EXISTS myTable (name VARCHAR(24), email VARCHAR(24))";$result = mysql_query($Q);check ($result);$Q = "ALTER TABLE myTable ADD name VARCHAR(24);";$result = mysql_query($Q);check ($result);$Q = "ALTER TABLE myTable ADD email VARCHAR(24);";$result = mysql_query($Q);check ($result);$Q = "INSERT INTO myTable VALUES ('zam', 'zam@tom.com') ";$result = mysql_query($Q);check ($result);$Q = "SELECT * FROM myTable;";$result = mysql_query($Q);while ($info = mysql_fetch_assoc($result)) {	print_r ($info);	echo "<br>";}

Strictly speaking, the IF NOT EXISTS clause in the create table query should keep any duplication error from occuring. My research turns up no similar test for columns. You could (and maybe should) use SHOW COLUMNS to create a test if the column exists, but what a bother.

[kvnmck18]

THIS IS GREAT! Works perfectly - Thank you, Deirdre's Dad.A+ work. Thanks again.