Page 1 of 1
Altering every table in the database
Posted: Fri Jun 30, 2006 7:43 pm
by psychotomus
how can I alter every table in a database. I have about 100+ tables and I need to add a new field all the tables.
Posted: Fri Jun 30, 2006 10:16 pm
by psychotomus
i got this so far
Code: Select all
$result = mysql_query( "SHOW TABLES" ) or die(mysql_error());//
while($row=mysql_fetch_array($result) )
{
mysql_query( "ALTER TABLE " . $row['Name'] . ' ADD forum_name VARCHAR(20)' ) or die(mysql_error());//
}
with this error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(20)' at line 1
Posted: Fri Jun 30, 2006 10:30 pm
by Benjamin
You only update the entire table once....
Code: Select all
ALTER TABLE `table_name` ADD `forum_name` VARCHAR( 20 ) NOT NULL ;
Have a look at
http://www.phpmyadmin.net/home_page/index.php
Posted: Fri Jun 30, 2006 10:45 pm
by psychotomus
thanks for posting exactly what I did?
Posted: Fri Jun 30, 2006 10:46 pm
by psychotomus
it seems the problem is i cant get the table names from the row_fetch_array
Posted: Fri Jun 30, 2006 10:49 pm
by Benjamin
I'm sorry I didn't read your question correctly.
Try echo'ing the queries and post it so we can figure out what the problem is.
Posted: Fri Jun 30, 2006 11:04 pm
by psychotomus
tried echoing row['Name']. Just displays blank. I can't figure out what what name is stored inside of row[] that will store the Name of the table.
Posted: Fri Jun 30, 2006 11:13 pm
by bdlang
There is no field 'Name' to reference in $row['Name']. You need to use mysql_fetch_row() to grab the data as a numeric indexed array, and refer to each table name as
$row[0].
Code: Select all
$database= 'test'; // define your database here, or through GET or POST
$sql= "SHOW TABLES FROM {$database}";
$resultset= mysql_query($sql) OR die(mysql_error());
while ( $row= mysql_fetch_row($resultset) ) {
$alter= "ALTER TABLE `{$row[0]}` ADD `forum_name` VARCHAR(20)";
$altered= mysql_query($alter) OR die("Error with table `{$row[0]}`:<br />" .mysql_error());
mysql_free_result($altered);
}
Please note I don't advocate using a PHP script to run through dozens of calls to mysql_query(), but if you gotta do it and only do it once, I suppose it's ok.
Afterthought, you might want to specify
where the new column should be placed, e.g. AFTER `column`.