Location: PHPKode > scripts > MySQL/HTML-Client > mysqlhtml-client/modules/alter_table.inc.php
<?php
    if ($alter) {
        $fieldtypeselectname = $type_array[$fieldtypeselect];

        # BEGIN OF ALTER TABLE QUERY
        $query_text = "ALTER TABLE " . $table . " ";
        
        switch ($alter) {
            case "set comment":
                $query_text .= "COMMENT='" . $table_comment . "'";
                break;
            case "set type":
                $query_text .= "TYPE=" . $table_type;           
                break;
            case "rename":        
                $query_text .= "RENAME " . $newtablename;
                $table = $newtablename;
                break;
            case "add field":
                #Fieldname
                $query_text .= " ADD " . $fieldtypename;
                
                # Fieldtype
                $query_text .= " " . $fieldtypeselectname;
                
                # Fieldlength
                if ($fieldlen)
                    $query_text .= "(" . $fieldlen . ")";
                    
                # NOT_NULL  
                if ($not_null == "on")
                    $query_text .=  " NOT NULL";
                elseif ($not_null == "")
                    $query_text .=  " NULL";
            
                if ($afterfield && (strcmp($afterfield, "LAST")!=0)) {
                    if (strcmp($afterfield, "FIRST")==0)
                        $query_text .= " FIRST";
                    else 
                        $query_text .= " AFTER " . $afterfield;
                }
            
                # INDEX
                if ($index == "on")
                    $query_text .= ", ADD INDEX(" . $fieldtypename . ")";
                
                # PRIMARY KEY
                if ($primarykey == "on")
                    $query_text .= ", ADD PRIMARY KEY(" . $fieldtypename . ")";
        
                # UNIQUE KEY
                if ($uniquekey == "on")
                    $query_text .= ", ADD UNIQUE(" . $fieldtypename . ")";
                
                break;
            case "alter":       
                if ($dropfield) {
                    $query_text .= "DROP COLUMN " . $fieldtypenameold;
                    unset($field);
                } else {
                    # Fieldname
                    if (($fieldtypename != $fieldtypenameold)||($fieldtypeselectname != strtoupper($fieldtypeselectold)))
                        $query_text .= "CHANGE " . $fieldtypenameold . " " . $fieldtypename;
                    elseif (($fieldtypename == $fieldtypenameold)||($fieldtypeselectname != strtoupper($fieldtypeselectold)))
                        $query_text .= "MODIFY " . $fieldtypenameold;
            
                    # Fieldtype
                    $query_text .= " " . $fieldtypeselectname;
                    # Fieldlength
                    if ($fieldlen)
                        $query_text .= "(" . $fieldlen . ")";
            
                    # NOT_NULL
                    if ($not_null != $not_nullold) {
                        if ($not_null == "on" && $not_nullold == "")
                            $query_text .=  " NOT NULL";
                        elseif ($not_null == "" && $not_nullold == "on")
                            $query_text .=  " NULL";
                    } elseif ($not_null == $not_nullold) {
                        if ($not_null == "on")
                            $query_text .=  " NOT NULL";
                        elseif ($not_null == "")
                            $query_text .=  " NULL";
                    }
            
                    # AUTO_INCREMENT
                    if ($auto_increment != $auto_incrementold) {
                        if ($auto_increment == "on" && $auto_incrementold == "")
                            $query_text .=  " AUTO_INCREMENT";
                        elseif ($auto_increment == "" && $auto_incrementold == "on")
                            $query_text .=  " ";
                    } elseif ($auto_increment == $auto_incrementold) {
                        if ($auto_increment == "on")
                            $query_text .=  " AUTO_INCREMENT";
                        elseif ($auto_increment == "")
                            $query_text .=  " ";
                    }
            
                    # INDEX
                    if ($index != $indexold) {
                        if ($index == "on" && $indexold == "") {
                            $query_text .= ", ADD INDEX(" . $fieldtypename . ")";
                        } elseif ($index == "" && $indexold == "on") {
                            $query_text .= ", DROP INDEX " . $fieldtypename . "";
                        }
                    }
            
                    # PRIMARY KEY
                    if ($primarykey != $primarykeyold) {
                        if ($primarykey == "on" && $primarykeyold == "") {
                            $query_text .= ", ADD PRIMARY KEY(" . $fieldtypename . ")";
                        } elseif ($primarykey == "" && $primarykeyold == "on") {
                            $query_text .= ", DROP PRIMARY KEY";
                        }
                    }
            
                    # UNIQUE KEY
                    if ($uniquekey != $uniquekeyold) {
                        if ($uniquekey == "on" && $uniquekeyold == "") {
                            $query_text .= ", ADD UNIQUE(" . $fieldtypename . ")";
                        } 
                    }
                }
            break;
        } /* END OF switch($alter)  */
        # END OF ALTER TABLE QUERY
        echo "<table border=\"0\" width=\"750\">\n";
        echo "<tr>\n";
        echo "<th bgcolor=\"#DDDDDD\" valign=\"top\" align=\"left\" width=\"15%\"><b>Last&nbsp;query:&nbsp;</b></th>\n";
        @mysql_db_query($database, $query_text, $db_link) or mysql_client_error_msg($query_text, __FILE__, __LINE__);
        echo "<td bgcolor=\"#DDDDDD\"> " . $query_text . "</td>\n";
        echo "</tr>\n";
        echo "</table>\n";        
        echo "<br>\n"; 
    }
    
    $query_text = "SELECT * FROM " . $table . " LIMIT 0";
    $result = @mysql_db_query($database, $query_text, $db_link) or mysql_client_error_msg($query_text, __FILE__, __LINE__);
    $fieldcounts = mysql_num_fields($result);
    echo "<p class=\"white\">";
    echo "<b>SELECT FIELD:</b>&nbsp;";
    echo "<select name=\"field\" onChange=\"submit()\">\n";
    echo "<option value=\"new\"" . ((strcmp($field, "new") ==0 ) ? " selected" : "") . ">Add new field\n";
    for($i = 0; $i < $fieldcounts; $i++) {
        $flags = mysql_field_flags($result, $i);
        $fieldtype = mysql_fetch_field($result, $i);
        $fieldlen = mysql_fieldlen($result, $i);
        $fieldtypename = $fieldtype->name;
        $fieldtypetype = $fieldtype->type;
        if ($fieldtypetype=="string") $fieldtypetype="varchar";
        echo "<option value=\"" . $i . "\"" . (($field==$i && (strcmp($field, "new")!=0)) ? " selected" : "") . ">" . $fieldtypename . " [" . $fieldtypetype;
        if ($fieldlen) echo "(" . $fieldlen . ")";
        if ($flags) echo " " . $flags;
        echo "]\n";
        if (strcmp($field, "new")==0) {
            $allfields[$i] = $fieldtypename;
        }
    }
    echo "</select>\n";
    echo "</p>\n";
    echo "<table  border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\n";
    echo "<tr>" . 
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Fieldname</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Fieldtype</b></td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Fieldlength</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>NOT NULL</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>AUTO_INCREMENT</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Index</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Primary Key</b>&nbsp;</td>\n" .
            "<td class=\"blacksmall\" nowrap>&nbsp;<b>Unique Key</b>&nbsp;</td>\n";            
    echo "</tr>\n";
    
    if (strcmp($field, "new")==0) {
        echo "<tr bgcolor=\"#BBBBBB\">\n";
        echo "<td bgcolor=\"#789ABC\"><input type=\"text\" name=\"fieldtypename\"></td>";
        echo "<td>";
        echo "<select name=\"fieldtypeselect\">\n";
        for ($j=0; $j < count($type_array); $j++) {
            echo "<option value=\"" . $j . "\">" . $type_array[$j] . "\n";
        }
        echo "</select></td>\n";
        echo "<td bgcolor=\"#789ABC\"><input type=\"text\" size=\"10\" name=\"fieldlen\"></td>";
        echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"not_null\"></td>";
        echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"auto_increment\"></td>";
        echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"index\"></td>";
        echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"primarykey\"></td>";
        echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"uniquekey\"></td>";
        echo "</tr>";
        echo "<table>";
    } else {
        echo "<tr bgcolor=\"#BBBBBB\">\n\n";    
        if (!$field) $field=0;
        $flags = mysql_field_flags($result, $field);
        $fieldtype = mysql_fetch_field($result, $field);
        $fieldlen = mysql_fieldlen($result, $field);
        $fieldtypename = $fieldtype->name;
        $fieldtypetype = $fieldtype->type;
        if ($fieldtypetype=="string") $fieldtypetype="varchar";
        echo "<td bgcolor=\"#789ABC\"><input type=\"text\" name=\"fieldtypename\" value=\"" . $fieldtypename . "\" size=\"15\"></td>";
        echo "<td>";
        echo "<select name=\"fieldtypeselect\">\n";
        for ($j=0; $j < count($type_array); $j++) {
            echo "<option value=\"" . $j . "\"" . ((strtolower($type_array[$j]) == $fieldtypetype) ? " selected" : "") . ">" . $type_array[$j] . "\n";
        }
        echo "</select></td>\n";
    
        echo "<td bgcolor=\"#789ABC\"><input type=\"text\" size=\"10\" name=\"fieldlen\" value=\"" . $fieldlen . "\"></td>";
    
        if (strstr($flags, "not_null")) {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"not_null\" checked></td>";
        } else {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"not_null\"></td>";
        }
        
        if (strstr($flags, "auto_increment")) {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"auto_increment\" checked></td>";
        } else {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"auto_increment\"></td>";
        }
    
        if (strstr($flags, "multiple_key")) {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"index\" checked></td>";
        } else {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"index\"></td>";
        }
        
        if (strstr($flags, "primary_key")) {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"primarykey\" checked></td>";
        } else {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"primarykey\"></td>";
        }
        
        if (strstr($flags, "unique_key")) {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"uniquekey\" checked onClick=\"checkDropUnique(this)\"></td>";
        } else {
            echo "<td bgcolor=\"#789ABC\" align=\"center\"><input type=\"checkbox\"" . $style1 . " name=\"uniquekey\" onClick=\"checkDropUnique(this)\"></td>";
        }
        echo "</tr>\n";
        echo "</table>\n";
    
        # Saving old values
        echo "<input type=\"hidden\" name=\"fieldtypenameold\" value=\"" . $fieldtypename . "\">\n";
        echo "<input type=\"hidden\" name=\"fieldtypeselectold\" value=\"" . $fieldtypetype . "\">\n";
        echo "<input type=\"hidden\" name=\"fieldlenold\" value=\"" . $fieldlen . "\">\n";
    
        if (strstr($flags, "not_null")) {
            echo "<input type=\"hidden\" name=\"not_nullold\" value=\"on\">\n";
        } else {
            echo "<input type=\"hidden\" name=\"not_nullold\" value=\"\">\n";
        }
        if (strstr($flags, "auto_increment")) {
            echo "<input type=\"hidden\" name=\"auto_incrementold\" value=\"on\">\n";
        } else {
            echo "<input type=\"hidden\" name=\"auto_incrementold\" value=\"\">\n";
        }
        if (strstr($flags, "multiple_key")) {
            echo "<input type=\"hidden\" name=\"indexold\" value=\"on\">\n";
        } else {
            echo "<input type=\"hidden\" name=\"indexold\" value=\"\">\n";
        }
        if (strstr($flags, "primary_key")) {
            echo "<input type=\"hidden\" name=\"primarykeyold\" value=\"on\">\n";
        } else {
            echo "<input type=\"hidden\" name=\"primarykeyold\" value=\"\">\n";
        }
        if (strstr($flags, "unique_key")) {
            echo "<input type=\"hidden\" name=\"uniquekeyold\" value=\"on\">\n";
        } else {
            echo "<input type=\"hidden\" name=\"uniquekeyold\" value=\"\">\n";
        }
    
        # Saving old values
    }
    echo "<br>\n";

    if (strcmp($field, "new")==0) {     
        echo "&nbsp;<font class=\"white\"><b>POSITION:</b></font>&nbsp;<select name=\"afterfield\">\n";
        echo "<option value=\"LAST\">LAST\n";
        for ($i = 0; $i < sizeof($allfields); $i++)
            echo "<option value=\"" . $allfields[$i] . "\">AFTER " . $allfields[$i] . "\n";
        echo "<option value=\"FIRST\">FIRST\n";         
        echo "</select>";
        echo "&nbsp;<input type=\"submit\" class=\"button\" name=\"alter\" value=\"add field\">\n";
    } else {
        echo "<input type=\"submit\" class=\"button\" name=\"alter\" value=\"alter\">\n";
        echo "<input " . $style2 . " type=\"checkbox\" name=\"dropfield\"><font color=\"maroon\"><b>DROP COLUMN</b></font><br>\n";
    }
    
    echo "<br><table border=\"0\" bgcolor=\"#345678\">\n";
    echo "<tr><td class=\"whitesmall\">ALTER TABLE <b>" . $table . "</b> RENAME </td><td><input type=\"text\" name=\"newtablename\" size=\"10\"></td><td><input type=\"submit\" class=\"button\" name=\"alter\" value=\"rename\"></td></tr>\n"; 
    echo "<tr><td class=\"whitesmall\">ALTER TABLE <b>" . $table . "</b> TYPE=</td><td>";   
    reset($table_types);
    echo "<select name=\"table_type\">\n";
    while (list($key, $value) = each($table_types)) {
        echo "<option";
        
        echo ">" . $value . "\n";
    }
    echo "</select>\n";
    echo "</td>";
    echo "<td>";
    echo "<input type=\"submit\" class=\"button\" name=\"alter\" value=\"set type\">";
    echo "</td>";
    echo "</tr>\n"; 
    echo "<tr><td class=\"whitesmall\">ALTER TABLE <b>" . $table . "</b> COMMENT=</td><td><input type=\"text\" name=\"table_comment\" size=\"10\" maxlength=\"60\"></td><td><input type=\"submit\" class=\"button\" name=\"alter\" value=\"set comment\"></td></tr>";
    echo "</table>\n";      
    unset($query_text);
?>
Return current item: MySQL/HTML-Client