PHP&MySQL: prohození sloupců v tabulce

Občas nastane situace, kdy potřebujeme použít funkci v databázi, kterou databáze standardně neumožňuje. Příkladem toho může být například prohození dvou sloupců v tabulce.

Situace, kdy potřebujete prohodit dva sloupce v databázi, nenastane denně, ale může se stát, že nastane. Tuto funkci standardně MySQL nenabízí, nebo jsem ji alespoň nenašel.

V takové situaci si musíte pomoci sami a danou funkci si doprogramovat.

Nebudu rozebírat strukturu databáze, a ani to, zda by nešla taková databáze lépe navrhnout. Stalo se to, že tuto funkci v databázi potřebuji.

Postup pro prohození sloupců jsem zvolil:

překontrolování struktury
vytvoření pomocného sloupce na místě, kam chci nový sloupce přesunout
překopírování dat z prvního sloupce do pomocného sloupce
smazání prvního sloupce
přejmenování pomocného sloupce na jméno prvního sloupce

Třídu, kterou si naprogramujeme, bude korektně umožňovat prohození pouze dvou sloupců vedle sebe. V praxi by jsme mohli prohazovat i sloupce, které nejsou hned vedle sebe, ale v konečném výsledku by se stejně prohodil první sloupec hned za druhý. Každopádně by nebyl problém třídu upravit i na jiný způsob prohození.

Vstupní parametry v konstruktoru budou název tabulky, název prvního sloupce a název druhého sloupce.

Kontrola struktury

Jelikož se jedná o operaci, kdy budeme měnit strukturu tabulky a budeme pracovat s daty je nutné tabulku před samotnou operací překontrolovat. Musíme zkontrolovat, zda obsahuje námi zadané názvy sloupců a navíc, pokud již budeme zjišťovat strukturu tabulky si zjistíme typ prvního sloupce, abychom mohli vytvořit pomocný sloupec stejného typu.

Ve všech metodách budeme předpokládat, že existují globální proměnné, uložené v konstruktoru:

function __construct( $table_name, $column_first, $column_second ){
    set_time_limit( 500 );
    
    //mysql connect / parent construct
    parent::__construct();
    $this->table_name = self::_add_slashes( $table_name );
    $this->column_first = self::_add_slashes( $column_first );
    $this->column_second = self::_add_slashes( $column_second );
}

function __construct( $table_name, $column_first, $column_second ){

set_time_limit( 500 );

//mysql connect / parent construct

parent::__construct();

$this->table_name = self::_add_slashes( $table_name );

$this->column_first = self::_add_slashes( $column_first );

$this->column_second = self::_add_slashes( $column_second );

}

Takže provedeme kontrolu:

function _exist_names(){
    $sql = "SHOW COLUMNS FROM `" . $this->table_name . "`";
    $result = mysql_query( $sql, $this->link );   
    
    if( mysql_affected_rows( $this->link ) < 2 )
        return 0;

    $first = NULL;
    while( $row = mysql_fetch_array( $result) ){
        $columns[] = $row[ 0 ];
        
        //save types of columns
        if( $this->column_first == $row[ 0 ] ) 
            $this->column_first_type = $row[ 1 ];
        else if( $this->column_second == $row[ 0 ] )
            $this->column_second_type = $row[ 1 ];
        if( $first != NULL )
            continue;
        
        //Variable $first is first column
        if( $row[ 0 ] == $this->column_first )
            $first = $this->column_first;
        else if( $row[ 0 ] == $this->column_second )
            $first = $this->column_second;
    }
    mysql_free_result( $result );
    
    //If first column isn't first column. 
    if( $first == $this->column_second ){
           $this->column_second = $this->column_first;
           $this->column_first = $first;
           $type = NULL;
           $this->column_first_type = $type;
           $this->column_first_type = $this->column_second_type;
           $this->column_second_type = $type;
    }
    
    if( !in_array( $this->column_first , $columns ) )
        return 0;
    if( !in_array( $this->column_second, $columns ) )
        return 0; 
    
    return 1;
}

function _exist_names(){

$sql = "SHOW COLUMNS FROM `" . $this->table_name . "`";

$result = mysql_query( $sql, $this->link );

if( mysql_affected_rows( $this->link ) < 2 )

return 0;

$first = NULL;

while( $row = mysql_fetch_array( $result) ){

$columns[] = $row[ 0 ];

//save types of columns

if( $this->column_first == $row[ 0 ] )

$this->column_first_type = $row[ 1 ];

else if( $this->column_second == $row[ 0 ] )

$this->column_second_type = $row[ 1 ];

if( $first != NULL )

continue;

//Variable $first is first column

if( $row[ 0 ] == $this->column_first )

$first = $this->column_first;

else if( $row[ 0 ] == $this->column_second )

$first = $this->column_second;

}

mysql_free_result( $result );

//If first column isn't first column.

if( $first == $this->column_second ){

$this->column_second = $this->column_first;

$this->column_first = $first;

$type = NULL;

$this->column_first_type = $type;

$this->column_first_type = $this->column_second_type;

$this->column_second_type = $type;

}

if( !in_array( $this->column_first , $columns ) )

return 0;

if( !in_array( $this->column_second, $columns ) )

return 0;

return 1;

}

Vytvoření pomocného sloupce

Při kontrole tabulky jsme si zjistily typ prvního sloupce a uložili do proměnné. Nyní můžeme jednoduše vytvořit pomocný sloupec, který umístíme za sloupec, který je při přesouvání v pořadí druhý. Leží vpravo:

function _create_temp_column(){
    //add temp collumn
    $sql = "ALTER TABLE `" . $this->table_name .  "` 
            ADD COLUMN `temp` " . strtoupper( $this->column_first_type ) . " 
            AFTER `" . $this->column_second . "`;";
    
    return self::_sql( $sql );
}

function _create_temp_column(){

//add temp collumn

$sql = "ALTER TABLE `" . $this->table_name . "`

ADD COLUMN `temp` " . strtoupper( $this->column_first_type ) . "

AFTER `" . $this->column_second . "`;";

return self::_sql( $sql );

}

Překopírování dat

Pokud je vytvořený pomocný sloupec, tak je nutné sem překopírovat data z prvního sloupce, abychom mohli první sloupec smazat. Toto uděláme jednoduše jedním sql dotazem:

function _set_data(){
    $sql = "UPDATE " . $this->table_name . " SET temp = " . $this->column_first . " ";
    return self::_sql( $sql );
}

function _set_data(){

$sql = "UPDATE " . $this->table_name . " SET temp = " . $this->column_first . " ";

return self::_sql( $sql );

}

Smazání prvního sloupce a přejmenování pomocného

Pokud je vše hotovo, stačí už jenom smazat první sloupec a přejmenovat pomocný sloupec jménem prvního:

function _delete_column(){
    $sql = "ALTER TABLE " . $this->table_name . " 
            DROP `" . $this->column_first . "`";
            
    return self::_sql( $sql );
}

function _rename_column(){
    $sql = "ALTER TABLE `" . $this->table_name . "` 
            CHANGE `temp` `" . $this->column_first . "` 
            " . strtoupper( $this->column_first_type) . " DEFAULT NULL ";   
            
    mysql_query( $sql, $this->link);
    return ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );
}

function _delete_column(){

$sql = "ALTER TABLE " . $this->table_name . "

DROP `" . $this->column_first . "`";

return self::_sql( $sql );

}

function _rename_column(){

$sql = "ALTER TABLE `" . $this->table_name . "`

CHANGE `temp` `" . $this->column_first . "`

" . strtoupper( $this->column_first_type) . " DEFAULT NULL ";

mysql_query( $sql, $this->link);

return ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );

}

Celou třídu můžeme doplnit o zamykání tabulek, nebo jiné zabezpečení.
Nakonec opět celý kód:

class change_the_order_of_the_columns extends connect{
    
    public $table_name = NULL;
    
    public $column_first = NULL;
    public $column_second = NULL;
    public $column_first_type = NULL;
    public $column_second_type = NULL;
    
    public $operation = FALSE;
    
    function __construct( $table_name, $column_first, $column_second ){
        set_time_limit( 500 );
        
        //mysql connect / parent construct
        parent::__construct();
        $this->table_name = self::_add_slashes( $table_name );
        $this->column_first = self::_add_slashes( $column_first );
        $this->column_second = self::_add_slashes( $column_second );
        
        if( self::_exist_names() == 0 )//|| self::_lock_table() == 0 )
            return 0;
            
        if( self::_create_temp_column() == 0 )
            return 0;
            
        if( self::_set_data() == 0 )
            return 0;
            
        if( self::_delete_column() == 0 )
            return 0;
            
        if( self::_rename_column() == 0 )
            return 0;

        $this->operation = TRUE;
        //self::_unlock_table();
    }
    
    function _exist_names(){
        $sql = "SHOW COLUMNS FROM `" . $this->table_name . "`";
        $result = mysql_query( $sql, $this->link );   
        
        if( mysql_affected_rows( $this->link ) < 2 )
            return 0;

        $first = NULL;
        while( $row = mysql_fetch_array( $result) ){
            $columns[] = $row[ 0 ];
            
            //save types of columns
            if( $this->column_first == $row[ 0 ] ) 
                $this->column_first_type = $row[ 1 ];
            else if( $this->column_second == $row[ 0 ] )
                $this->column_second_type = $row[ 1 ];
            if( $first != NULL )
                continue;
            
            //Variable $first is first column
            if( $row[ 0 ] == $this->column_first )
                $first = $this->column_first;
            else if( $row[ 0 ] == $this->column_second )
                $first = $this->column_second;
        }
        mysql_free_result( $result );
        
        //If first column isn't first column. 
        if( $first == $this->column_second ){
               $this->column_second = $this->column_first;
               $this->column_first = $first;
               $type = NULL;
               $this->column_first_type = $type;
               $this->column_first_type = $this->column_second_type;
               $this->column_second_type = $type;
        }
        
        if( !in_array( $this->column_first , $columns ) )
            return 0;
        if( !in_array( $this->column_second, $columns ) )
            return 0; 
        
        return 1;
    }
    
    function _delete_column(){
        $sql = "ALTER TABLE " . $this->table_name . " 
                DROP `" . $this->column_first . "`";
                
        return self::_sql( $sql );
    }
    
    function _create_temp_column(){
        //add temp collumn
        $sql = "ALTER TABLE `" . $this->table_name .  "` 
                ADD COLUMN `temp` " . strtoupper( $this->column_first_type ) . " 
                AFTER `" . $this->column_second . "`;";
        
        return self::_sql( $sql );
    }
    
function _rename_column(){
    $sql = "ALTER TABLE `" . $this->table_name . "` 
            CHANGE `temp` `" . $this->column_first . "` 
            " . strtoupper( $this->column_first_type) . " DEFAULT NULL ";   
            
    mysql_query( $sql, $this->link);
    return ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );
}
    
    function _add_slashes( & $value ){
        //add slashes
        $value = trim( $value );
        return ( get_magic_quotes_gpc() == 1 ? $value  : addslashes( $value ) );
    }
    
    function _set_data(){
        $sql = "UPDATE " . $this->table_name . " SET temp = " . $this->column_first . " ";
        return self::_sql( $sql );
    }
    
    function _lock_table(){
        //lock table for read
        $sql = 'LOCK TABLES `' . $this->table_name . '` READ';   
        return self::_sql( $sql );
    }
    
    function _unlock_table(){
        $sql = "UNLOCK TABLES;";
        mysql_query( $sql, $this->link );
        return 1;   
    }
    
    function _sql( $sql = NULL ){
        if( $sql == NULL )
            return 0;
        
        mysql_query( $sql, $this->link );
        return  ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );   
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

class change_the_order_of_the_columns extends connect{

public $table_name = NULL;

public $column_first = NULL;

public $column_second = NULL;

public $column_first_type = NULL;

public $column_second_type = NULL;

public $operation = FALSE;

function __construct( $table_name, $column_first, $column_second ){

set_time_limit( 500 );

//mysql connect / parent construct

parent::__construct();

$this->table_name = self::_add_slashes( $table_name );

$this->column_first = self::_add_slashes( $column_first );

$this->column_second = self::_add_slashes( $column_second );

if( self::_exist_names() == 0 )//|| self::_lock_table() == 0 )

return 0;

if( self::_create_temp_column() == 0 )

return 0;

if( self::_set_data() == 0 )

return 0;

if( self::_delete_column() == 0 )

return 0;

if( self::_rename_column() == 0 )

return 0;

$this->operation = TRUE;

//self::_unlock_table();

}

function _exist_names(){

$sql = "SHOW COLUMNS FROM `" . $this->table_name . "`";

$result = mysql_query( $sql, $this->link );

if( mysql_affected_rows( $this->link ) < 2 )

return 0;

$first = NULL;

while( $row = mysql_fetch_array( $result) ){

$columns[] = $row[ 0 ];

//save types of columns

if( $this->column_first == $row[ 0 ] )

$this->column_first_type = $row[ 1 ];

else if( $this->column_second == $row[ 0 ] )

$this->column_second_type = $row[ 1 ];

if( $first != NULL )

continue;

//Variable $first is first column

if( $row[ 0 ] == $this->column_first )

$first = $this->column_first;

else if( $row[ 0 ] == $this->column_second )

$first = $this->column_second;

}

mysql_free_result( $result );

//If first column isn't first column.

if( $first == $this->column_second ){

$this->column_second = $this->column_first;

$this->column_first = $first;

$type = NULL;

$this->column_first_type = $type;

$this->column_first_type = $this->column_second_type;

$this->column_second_type = $type;

}

if( !in_array( $this->column_first , $columns ) )

return 0;

if( !in_array( $this->column_second, $columns ) )

return 0;

return 1;

}

function _delete_column(){

$sql = "ALTER TABLE " . $this->table_name . "

DROP `" . $this->column_first . "`";

return self::_sql( $sql );

}

function _create_temp_column(){

//add temp collumn

$sql = "ALTER TABLE `" . $this->table_name . "`

ADD COLUMN `temp` " . strtoupper( $this->column_first_type ) . "

AFTER `" . $this->column_second . "`;";

return self::_sql( $sql );

}

function _rename_column(){

$sql = "ALTER TABLE `" . $this->table_name . "`

CHANGE `temp` `" . $this->column_first . "`

" . strtoupper( $this->column_first_type) . " DEFAULT NULL ";

mysql_query( $sql, $this->link);

return ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );

}

function _add_slashes( & $value ){

//add slashes

$value = trim( $value );

return ( get_magic_quotes_gpc() == 1 ? $value : addslashes( $value ) );

}

function _set_data(){

$sql = "UPDATE " . $this->table_name . " SET temp = " . $this->column_first . " ";

return self::_sql( $sql );

}

function _lock_table(){

//lock table for read

$sql = 'LOCK TABLES `' . $this->table_name . '` READ';

return self::_sql( $sql );

}

function _unlock_table(){

$sql = "UNLOCK TABLES;";

mysql_query( $sql, $this->link );

return 1;

}

function _sql( $sql = NULL ){

if( $sql == NULL )

return 0;

mysql_query( $sql, $this->link );

return ( mysql_affected_rows( $this->link ) < 0 ? 0 : 1 );

}

Comments are closed for this page

M&.M

26/03/2009

alter table `tabulka` modify column `sloupec1` varchar(150) after `sloupec2`;

varchar(150) je nutne nahradit typem sloupce1
🙂

zaachi

26/03/2009

Diky,
proc delat neco jednoduse, kdyz to jde slozite.
Stejne se divim, ze me tohle nenapadlo.

jaja

26/03/2009

tak tohle je krutec:-) doporucuju pouzit prikaz alter, viz prvni prispevek.