Altering every table in the database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Altering every table in the database

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

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

thanks for posting exactly what I did?
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

it seems the problem is i cant get the table names from the row_fetch_array
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

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