根据数据库自动生成INSERT/UPDATE更新语句
- WBOYOriginal
- 2016-06-21 09:09:121185Durchsuche
数据|数据库|语句
在编写程序中，写添加和更新SQL语句是一个比较烦琐的过程，特别是在表结构比较复杂的时候
这个程序主要是根据数据表格式自动生成添加/更新的SQL语句，然后由程序员直接加入到程序中，方便编程
下面是文件，方法建立一个单独的文件，按照下面的说明建立相关文件，运行即可
index.php 索引文件
config.php 配置文件，转换get/post数据
addxxx.php 根据提交表格数据生成SQL语句
addxxx2.php 根据数据库表信息生成SQL语句，配置数据库在文件内部
@author bluemaple
----------------------------------------------------------
index.php文件
----------------------------------------------------------
自动生成模版
自动生成添加表格，并产生相关sql添加语句，生成UPDATE语句（简单版）
更数据表生成相关添加页面
----------------------------------------------------------
config.php文件
----------------------------------------------------------
/* 转换get和post数据 */
if (!defined('PMA_GRAB_GLOBALS_INCLUDED'))
{
define('PMA_GRAB_GLOBALS_INCLUDED', 1);
if (!empty($_GET))
{
extract($_GET);
}
else if (!empty($HTTP_GET_VARS))
{
extract($HTTP_GET_VARS);
} // end if
if (!empty($_POST))
{
extract($_POST);
}
else if (!empty($HTTP_POST_VARS))
{
extract($HTTP_POST_VARS);
} // end if
if (!empty($_FILES))
{
while (list($name, $value) = each($_FILES))
{
$$name = $value['tmp_name'];
}
}
else if (!empty($HTTP_POST_FILES))
{
while (list($name, $value) = each($HTTP_POST_FILES))
{
$$name = $value['tmp_name'];
}
} // end if
}
?>
----------------------------------------------------------
addxxx.php
----------------------------------------------------------
include("config.php");
if($action == "")
{
echo "";
exit();
}
if($action == "c")
{
echo "";
}
if($action == "a")
{
echo "生成的插入SQL语句
";
if($submit)
{
if($t == "") $t = "TABLE";
$tName = "";
$tValue= "";
// table ...
for($i = 1; $i {
$temp = "n".$i;
$tName .= $$temp;
if($i != $n)
{
$tName .= ", ";
}
$tempi = "n".$i;
$tValue .= "'\$".$$tempi."'";
if($i != $n)
{
$tValue .= ", ";
}
$usqli .= $$temp."='\$".$$tempi."'";
if($i != $n)
{
$usqli .= ", ";
}
}
$sql = "INSERT INTO $t ($tName) VALUES ($tValue);";
echo $sql;
echo "
";
$usql = "UPDATE $t SET ".$usqli." WHERE ***";
echo $usql;
echo "";<br> echo "<b>生成表格</b> 点右键查看源文件";<br> echo "
";
echo "";
}
}
?>
----------------------------------------------------------
addxxx2.php
----------------------------------------------------------
include("config.php");
if($action == "") $action = "d";
$mysqlServer = "localhost";
$mysqlPort = 3306;
$mysqlUser = "root";
$mysqlPwd = "";
if($action == 'd')
{
$mysql_id = mysql_pconnect($mysqlServer.":".$mysqlPort, $mysqlUser, $mysqlPwd);
if($db == "")
{
$db_list = mysql_list_dbs();
echo "选择数据库
";
while ($row = mysql_fetch_object($db_list))
{
echo "Database.">".$row->Database ."
";
}
}
if($db != "")
{
mysql_select_db($db);
$tresult = mysql_list_tables($db);
if (!$tresult)
{
echo "DB Error, could not list tables\n";
echo 'MySQL Error: ' . mysql_error();
exit;
}
echo "数据库$db列表
";
while ($row = mysql_fetch_row($tresult))
{
print "$row[0]
";
}
if($tname != "")
{
$fields = mysql_list_fields($db, $tname);
$n = mysql_num_fields($fields);
echo "
$tname表中：";
for ($i = 0; $i {
$name = mysql_field_name($fields, $i);
echo $name." ";
$tName .= $name;
$vName .= "'\$".$name."'";
$usqli .= $name."='\$".$name."'";
if($i != $n-1)
{
$tName .= ", ";
$vName .= ", ";
$usqli .=" and ";
}
//create table...
$table .= "
\n";
".$name."
}
echo "生成表：
";
echo "INSERT INTO $tname ($tName) VALUES ($vName);";
echo "
";
$usql = "UPDATE $tname SET ".$usqli." WHERE ***";
echo $usql;
echo "";<br> echo "<b>生成表格</b> 点右键查看源文件<br>\n";<br> echo "
";
echo "";
}
}
}
?>