Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- alter table datatable_??
- add column ID int auto_increment not null first,
- add primary key (ID);
- SELECT CONCAT('ALTER TABLE ',
- table_schema,
- '.',
- table_name,
- ' ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;') AS ddl
- INTO OUTFILE '/tmp/alter_table.sql'
- FROM information_schema.tables
- WHERE table_schema = 'db_name'
- AND table_type = 'base table';
- . /tmp/alter_table.sql
- select concat('alter table ',table_name,' add column ID int auto_increment not null primary key first;')
- from information_schema.tables
- where table_schema = 'db_name' and table_type = 'base table';
- DELIMITER $$
- USE `db_name`$$
- DROP PROCEDURE IF EXISTS `alter_test_1`$$
- CREATE DEFINER=`db_name`@`10.%` PROCEDURE `alter_test_1`()
- BEGIN
- DECLARE v_finished INTEGER DEFAULT 0;
- DECLARE v_table VARCHAR(100) DEFAULT "";
- DECLARE stmt VARCHAR(500) DEFAULT "";
- DECLARE column_cursor CURSOR FOR
- SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';
- DECLARE CONTINUE HANDLER
- FOR NOT FOUND SET v_finished = 1;
- OPEN column_cursor;
- alter_tables: LOOP
- FETCH column_cursor INTO v_table;
- IF v_finished = 1 THEN
- LEAVE alter_tables;
- END IF;
- SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,' ADD COLUMN id INT AUTO_INCREMENT NOT NULL;');
- PREPARE stmt FROM @prepstmt;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
- END LOOP alter_tables;
- CLOSE column_cursor;
- END$$
- DELIMITER ;
- <?php
- // ====Variable ============
- $sServerHost="localhost";
- $sLocalHost="localhost";
- $sServerUser="root";
- $sLocalUser="root";
- $sServerPassword="";
- $sLocalPassword="";
- $sLocalDatabaseName="test";
- $sServerDatabaseName="test";
- $Add_columnname="session_language";
- // ====db connection============
- $conn = @mysql_connect($sServerUser,$sServerUser,$sServerPassword);
- if(!$conn) {
- $conn = mysql_connect($sLocalHost,$sLocalUser,$sLocalPassword);
- }
- $db=mysql_select_db($sServerDatabaseName);
- if(!$db){
- $db=mysql_select_db($sLocalDatabaseName);
- }
- // =====basic function liberary============
- class Model
- {
- function find_query_all($pred = '', $params = array(), $select = '*')
- {
- global $db;
- $sql = $pred;
- $rows = array();
- $rows =$this->getAll($sql, $params);
- $this->_check_error($rows);
- if(count($rows)>0){
- $this->_row_count=count($rows);
- }
- else
- $this->_row_count=0;
- return $rows;
- }
- //====== fetch rows from db and return array of row object
- function getAll($sqlQuery='',$params= array())
- {
- $msg="";
- $this->_sqlQuery="";
- $sQuery=$this->MakeSQLQuery($sqlQuery,$params);
- $this->_sqlQuery=$sQuery;
- $res=mysql_query($sQuery) or die(mysql_error());
- $results = array();
- if(mysql_num_rows($res)>0){
- while ($row = mysql_fetch_object($res)) {
- $results[] = $row;
- }
- }
- return $results;
- }
- function MakeSQLQuery($str='',$params= array())
- {
- foreach($params as $k=>$v){
- $params[$k]=$this->_quote($params[$k]);
- }
- $str=$this->replace_different("?",$params,$str);
- return $str;
- }
- function replace_different($search,$replace,$string) {
- $occs = substr_count($string,$search);
- $last = 0;
- $cur = 0;
- $data = '';
- for ($i=0;$i<$occs;$i++) {
- $find = strpos($string,$search,$last);
- $data .= substr($string,$last,$find-$last).$replace[$cur];
- $last = $find+strlen($search);
- if (++$cur == count($replace)) {
- $cur = 0;
- }
- }
- return $data.substr($string,$last);
- }
- // check for a database error
- function _check_error($obj, $msg = 'Database Error')
- {
- if (!$this->isError($obj))
- {
- $this->raise_error($msg . ': ' .$obj->getMessage(). mysql_error());
- }
- return ;
- }
- function isError($value)
- {
- if(isset($value))
- return true;
- else
- return false;
- }
- }
- // main operation from here
- //====define object of class========
- $tbl_col = new Model;
- $sqlquery_col="SELECT table_name FROM information_schema.tables where table_schema='".$sLocalDatabaseName."'";
- $res_tab_col=$tbl_col->find_query_all($sqlquery_col);
- foreach($res_tab_col as $r=>$v){
- $res=mysql_query("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '".$sLocalDatabaseName."' AND TABLE_NAME ='".$v->table_name."' and COLUMN_NAME!='".$Add_columnname."' ORDER BY ORDINAL_POSITION DESC LIMIT 1");
- if(mysql_num_rows($res)>0){
- while ($row = mysql_fetch_object($res)) {
- $last_column= $row->COLUMN_NAME;
- $alter_query="ALTER TABLE `".$v->table_name."` ADD `".$Add_columnname."` INT NOT NULL DEFAULT '1' AFTER `".$last_column."` ";
- mysql_query($alter_query);
- }
- }
- }
- ?>
Add Comment
Please, Sign In to add comment