当前位置:Linux教程 - Php - 根据数据库自动生成INSERT/UPDATE更新语句

根据数据库自动生成INSERT/UPDATE更新语句

在编写程序中,写添加和更新SQL语句是一个比较烦琐的过程,特别是在表结构比较复杂的时候
这个程序主要是根据数据表格式自动生成添加/更新的SQL语句,然后由程序员直接加入到程序中,方便编程

下面是文件,方法建立一个单独的文件,按照下面的说明建立相关文件,运行即可
index.php     索引文件
config.php    配置文件,转换get/post数据
addxxx.php    根据提交表格数据生成SQL语句
addxxx2.php    根据数据库表信息生成SQL语句,配置数据库在文件内部

@author bluemaple<bluemaple@xinhuanet.com>
----------------------------------------------------------
index.php文件
----------------------------------------------------------
自动生成模版
<br><br>
<a href=http://www.sheup.com/?addxxx.php>自动生成添加表格,并产生相关sql添加语句,生成UPDATE语句(简单版)</a>
<br><br>
<a href=http://www.sheup.com/?addxxx2.php>更数据表生成相关添加页面</a>
----------------------------------------------------------

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 "<form action=addxxx.php method=get onSubmit=\"if(n.value==''){alert('请输入数字');return false;}else return true;\">
                添加的表数目:<input type=text name=n value=1><br>
                <input type=hidden name=action value='c'>
                <input type=submit name=createSubmit value='生成'>
                </form>";
        exit();
    }
    
    if($action == "c")
    {
        echo "<form method=post action=addxxx.php>";
        echo "    添加到数据表:<input type=text name=t><br>
                方法(METHOD):<input type=text name=m value=post><br>
                提交表到(ACTION):<input type=text name=a value='<? echo \$HTTP_SERVER_VARS[PHP_SELF] ?>'><br><br>";
        echo "<table border=1>";
        echo "<tr><td>编号</td><td>说明</td><td>表名(TableName)</td><td>值(VALUE)</td></tr>";
        for($i = 1; $i<=$n; $i++)
        {
            echo "<tr>";
            echo "<td>$i</td>";
            echo "<td><input name=e$i type=text></td>";
            echo "<td><input name=n$i type=text></td>";
            echo "<td><input name=v$i type=text></td>";
            echo "</tr>";
        }
        echo "<tr><td colspan=3 align=center><input type=submit name=submit value='确 定'></td></tr>";
        echo "</table>";
        echo "<input type=hidden name=n value='$n'>";
        echo "<input type=hidden name=action value='a'>";
        echo "</form>";
    }
    
    if($action == "a")
    {
        echo "<h3>生成的插入SQL语句</h3>";
        if($submit)
        {
            if($t == "") $t = "TABLE";
            $tName = "";
            $tValue= "";
            
            // table ...
            for($i = 1; $i <= $n; $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 "<br><br>";
            $usql = "UPDATE $t SET ".$usqli." WHERE ***";
            echo $usql;
            
            echo "<pre>";
            echo "<b>生成表格</b> 点右键查看源文件";
            echo "<table width=\"700\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" bgcolor=\"\">
                <form action='$a' method='$m'>
                  <tr>
                    <td width=\"\"></td>
                    <td width=\"\"></td>
                  </tr>";
            
            for($i = 1; $i <= $n; $i++)
            {
                $etemp = "e".$i;
                $ntemp = "n".$i;
                $vtemp = "v".$i;
                echo "<tr>
                      <td>".$$etemp."</td>
                      <td><input type=text class=\"\" name=\"".$$ntemp."\" value=\"".$$vtemp."\"></td>
                      </tr>";
            }
            echo "<tr>
                    <td></td>
                    <td><input type=submit value='添加' class=\"\"><input type=reset value='重置' class=\"\"></td>
                  </tr>
                  </form>
                </table>";
                
            echo "</pre>";
        }
    }
?>
----------------------------------------------------------
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 "选择数据库<br>";
                    while ($row = mysql_fetch_object($db_list))
                {
                    echo "<a href=http://www.sheup.com/?addxxx2.php?action=d&db=".$row->Database.">".$row->Database ."</a><br>";
                }
            }
            
            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 "数据库<b>$db</b>列表<br>";
                while ($row = mysql_fetch_row($tresult))
                {
                    print "<a href=http://www.sheup.com/?addxxx2.php?db=$db&tname=$row[0]>$row[0]</a><br>";
                }
                
                if($tname != "")
                {
                    $fields = mysql_list_fields($db, $tname);
                    $n = mysql_num_fields($fields);
                    
                    echo "<br><b>$tname</b>表中:";
                    for ($i = 0; $i < $n; $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 .= "<tr>
                        <td>".$name."</td>
                        <td><input type=text class=\"\" name=\"".$name."\" value=\"\"></td>
                        </tr>\n";
                        
                        
                    }
                    
                    echo "生成表:<br><br>";
                    echo "INSERT INTO $tname ($tName) VALUES ($vName);";
                    
                    echo "<br><br>";
                    $usql = "UPDATE $tname SET ".$usqli." WHERE ***";
                    echo $usql;
                    
            echo "<pre>";
            echo "<b>生成表格</b> 点右键查看源文件<br>\n";
            echo "<table width=\"700\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\" bgcolor=\"\">
                <form action='$a' method='$m'>
                  <tr>
                    <td width=\"\"></td>
                    <td width=\"\"></td>
                  </tr>";

            echo "$table";

            echo "<tr>
                    <td></td>
                    <td><input type=submit value='添加' class=\"\"><input type=reset value='重置' class=\"\"></td>
                  </tr>
                  </form>
                </table>";
                
            echo "</pre>";                    
                      
                }
         &nbs