Altering every table in the database
Moderator: General Moderators
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Altering every table in the database
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.
Last edited by psychotomus on Fri Jun 30, 2006 10:47 pm, edited 1 time in total.
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
i got this so far
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
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
You only update the entire table once....
Have a look at
http://www.phpmyadmin.net/home_page/index.php
Code: Select all
ALTER TABLE `table_name` ADD `forum_name` VARCHAR( 20 ) NOT NULL ;http://www.phpmyadmin.net/home_page/index.php
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
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].
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`.
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);
}Afterthought, you might want to specify where the new column should be placed, e.g. AFTER `column`.