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`.