Code: Select all
<?php
/*
alter_table.php v1.0 by Kristoffe Brodeur. ©2010 All Rights Reserved.
02-18-2010
07-07-2010 added blob and char to type in field alteration
12-26-2013 mysqli version with top menu as well (branzinophilly admin menu and css)
*/
//
if(isset($_GET['id'])){$id=$_GET['id'];}else{$id=0;}
$to_root="../../";
require '_info.php';
//
if(isset($_GET['db'])){$db=$_GET['db'];}
require $to_root.'php/menu.php';
$connect=mysqli_connect($server,$user,$pass,$db);
$findTables="SHOW TABLES FROM $db";
$tablesQ=mysqli_query($connect,$findTables);
//
if(!$tablesQ)
{echo "Error, could not list tables <span class='error'>".mysqli_error($connect)."</span>";}
$index=-1;
$tables=array();
$listTables="";
//
while($row=mysqli_fetch_row($tablesQ))
{
$index++;
$tables[$index]=$row[0];
$listTables.="<li><a href='alter_table.php?db=$db&id=$index'>".$row[0]."</a></li>";
//
if($index==$id)
{
$selTable=$row[0];
}
}
$listCols="
<tr>
<td width='120px'><b>Field</b></td>
<td width='120px'><b>Type</b></td>
<td><b>Length</b></td>
</tr>";
//-----show selected table cols
//changes to mysqli output a number not a character list, so this is needed
$tArrStr=
"0,DECIMAL
1,TINYINT
2,SMALLINT
3,INTEGER
4,FLOAT
5,DOUBLE
7,TIMESTAMP
8,BIGINT
9,MEDIUMINT
10,DATE
11,TIME
12,DATETIME
13,YEAR
14,DATE
16,BIT
246,DECIMAL
247,ENUM
248,SET
249,TINYBLOB
250,MEDIUMBLOB
251,LONGBLOB
252,BLOB
253,VARCHAR
254,CHAR
255,GEOMETRY";
$tmpArr=preg_split("/[\n]/",$tArrStr);
$lenT=count($tmpArr);
$tArr=array();
//echo "data types total [$lenT]<br />";
//
for($a=0;$a<$lenT;$a++)
{
$tmpArr2=explode(",",$tmpArr[$a]);
//echo "[".$tmpArr2[0]."]".$tmpArr2[1]."<br />";
$n=intval($tmpArr2[0]);//otherwise above would have the newline character in it or a space and be a textual array not numeric
$tArr[$n]=$tmpArr2[1];
}
$typeOptionStr="";
//fill in a drop down for the field type add area
for($a=0;$a<255;$a++)
{
//array_key_exists($a,$test)
//
if(isset($tArr[$a]))
{
$typeOptionStr.=
"<option>".$tArr[$a]."</option>";
//echo "[$a] ".$tArr[$a]."<br />";
}
}
//----------
$qStr="SELECT * FROM ".$tables[$id];
$q1=mysqli_query($connect,$qStr);
$qFields=mysqli_fetch_fields($q1);
$lenF=count($qFields);
//
for($a=0;$a<$lenF;$a++)
{
$_name=$qFields[$a]->name;
$_type=$qFields[$a]->type;
$_len=$qFields[$a]->length;
$listCols.="<tr>
<td><input onclick='build_query(this)' fID='$_name' fTYPE='$_type' fLEN='$_len' type='radio' name='field'>$_name</td>
<td>".$tArr[$_type]."</td>
<td>$_len</td>
</tr>";
//echo $_name."-----".$_type."*".$tArr[$_type]."*<br />";
}
mysqli_close($connect);
?>
<html>
<head>
<link type="text/css" rel="stylesheet" href="<?php echo $to_root;?>css/admin/init_db.css" />
<link type="text/css" rel="stylesheet" href="<?php echo $to_root;?>css/admin/about.css" />
<link type="text/css" rel="stylesheet" href="<?php echo $to_root;?>css/admin/add_ourwork.css" />
<link type='text/css' rel='stylesheet' href='<?php echo $to_root;?>css/admin/admin_menu.css' />
</head>
<body>
<div class="wrap">
<?php require $to_root."mS/widgets/admin_menu.php";?>
<div class="existingServices">
<fieldset>
<legend>DB [<?php echo $db;?>] Existing Tables</legend>
<ul><?php echo $listTables;?></ul>
</fieldset>
</div>
<div class="addServiceForm">
<form class="" id="alterTable" method="POST" action="save_alterTable.php?db=<?php echo $db;?>">cpu
<fieldset>
<legend>Fields | <span class="ok"><?php echo $selTable;?></span></legend>
<table class="mysqlCols"><?php echo $listCols;?></table>
</fieldset>
<fieldset>
<legend>Add New Field</legend><br />
<table class="mysqlCols">
<tr>
<td width='120px'><b>Field Name</b></td>
<td width='120px'><b>Type</b></td>
<td><b>Length</b></td>
</tr>
<tr>
<td><input onchange="addField()" type="text" id="newName" size="12" value="0"></td>
<td>
<select onchange="addField()" id="newType">
<?php echo $typeOptionStr;?>
</select>
</td>
<td><input onchange="addField()" type="text" id="newLen" size="4" value="0"></td>
</tr>
</table>
<hr />
<img onclick="addField()" src="<?php echo $to_root;?>iconz/add_16px.png" />
</fieldset>
<fieldset>
<legend>MySQL Field Alteration Query</legend>
<hr /><input type="text" name="queryF" id="queryF" value="*" size="85">
<br /><hr />
<input type="hidden" name="id" value="<?php echo $id;?>">
<input type="submit" value="SAVE CHANGE">
</fieldset>
</form>
</div>
</div>
</body>
<SCRIPT LANGUAGE="JavaScript" type="text/javascript">
//-----
function getE(sName)
{
var pnt=document.getElementById(sName);
return pnt;
}
//-----
function addField()
{
var queryF=getE('queryF');
var newName=getE('newName');
var newLen=getE('newLen');
var newType=getE('newType');
var num=newType.selectedIndex;
queryF.value="ALTER table <?php echo $selTable;?> ADD COLUMN "+newName.value+" "+newType.options[num].innerHTML+" ("+newLen.value+")";
}
//-----
function build_query(sObj)
{
var queryF=getE('queryF');
queryF.value="ALTER table <?php echo $selTable;?> MODIFY "+sObj.getAttribute('fID')+" VARCHAR("+sObj.getAttribute('fLEN')+")";
}
</SCRIPT>
</html>