[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

cleate colum in every table mysql

Posted on 2011-10-26
7
Medium Priority
?
214 Views
Last Modified: 2012-05-12
how can  i insert colum timestampA(type timestamp)    into all table on database ph can I do one time I must do by hand table in each table
0
Comment
Question by:teera
7 Comments
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 668 total points
ID: 37036491
Hello teera,

You should find your answer from following link

http://stackoverflow.com/questions/5706740/adding-column-to-all-tables-in-a-mysql-database-unknown-table-names

Thank You.

Amar
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37036967
@mysql_query("ALTER TABLE `YourTableName` ADD `timestampA` timestamp") or die(mysql_error());
0
 
LVL 10

Assisted Solution

by:ukerandi
ukerandi earned 664 total points
ID: 37037046
this will find all the database file and Add another field for each one. this is good example

<?php
$dbname = 'mysql_dbname';

if (!mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
    echo 'Could not connect to mysql';
    exit;
}

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);

if (!$result) {
    echo "DB Error, could not list tables\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}
$i=1;
while ($row = mysql_fetch_row($result)) {
   

@mysql_query("ALTER TABLE `$row[$i]` ADD `timestampA` timestamp") or die(mysql_error()); 

$i++;
}

mysql_free_result($result);
?>

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:ukerandi
ID: 37037067
sorry , $i should be initialized,not $i=1
$i=0;
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37038510
You can use ALTER TABLE, however you cannot use the script posted at ID:37037046 because it does not work.  It's always a good idea to test the code that is posted here at EE.  Some of our experts do not test their code before they post it, and you may get yourself in trouble if you rely on untested code.  Back up your data base before you perform any such operation!
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 668 total points
ID: 37038608
This will generate the correct query statements (or at least will find the table names).  Add your own information at line 6 and see what it gives you for output.  Once you are satisfied with the look of the queries, you can back up your data base and try adding the TIMESTAMP columns by inserting the appropriate call to mysql_query().

Best of luck with your project, ~Ray
<?php // RAY_temp_teera.php
error_reporting(E_ALL);
echo "<pre>";

// INSERT YOUR LIVE DATABASE CREDENTIALS HERE
require_once('YOUR_DB_CREDENTIALS.php');

// CONNECT TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word")) 
{
    $errmsg	= mysql_errno() . ' ' . mysql_error();
    echo "<br/>! db_connection ";
    echo "<br/> $errmsg <br/>";
}

$sql = "SHOW TABLES FROM $db_name";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res))
{
    $sql = "ALTER TABLE `$row[0]` ADD `timestampA` TIMESTAMP";
    echo PHP_EOL . $sql;
}

Open in new window

0
 

Author Closing Comment

by:teera
ID: 37080850
thank you
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 19 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question