PHP生成SQL基本操作增删查改拼接代码
1 <?php2 function create_sql_string($curdb){
3 $conn=mysql_connect('localhost','root','ubt');
4 mysql_select_db('information_schema',$conn);
5
6 //查询当前数据库所有表的表名
7 $sqlcmd=sprintf("SELECT `TABLE_NAME` FROM `TABLES` WHERE `TABLE_SCHEMA`='%s'",$curdb);
8
9 $res_tablename=mysql_query($sqlcmd);
10 $f=fopen('cbc.sql','w+');
11 while($tablename=mysql_fetch_assoc($res_tablename)){
12 //查询当前表中所有字段信息
13 $sqlcmd=sprintf("SELECT * FROM `COLUMNS` WHERE `TABLE_SCHEMA`='%s' AND `TABLE_NAME`='%s'",
14 $curdb,$tablename['TABLE_NAME']);
15 $res=mysql_query($sqlcmd);
16 $i=0;
17 $primay_key="";
18 $primay_val="";
19 $insert_key="";
20 $insert_fmt="";
21 $insert_val="";
22 $update_val="";
23 while ($record=mysql_fetch_assoc($res)){
24 if($record['COLUMN_KEY']=='PRI'){
25 $primay_key=($record['NUMERIC_PRECISION']=="")?
("`".$record['COLUMN_NAME']."`='%s'"):
("`".$record['COLUMN_NAME']."`=%s");
26 $primay_val="\$_GET['".$record['COLUMN_NAME']."']";
27
28 } else {
29
30 if ($i>0) {
31 $insert_key.=',';
32 $insert_fmt.=',';
33 $insert_val.=',';
34 $update_val.=',';
35 }
36 $insert_key.='`'.$record['COLUMN_NAME'].'`';
37
38 //字符串型的字段和数值型的字段分别处理
39 if ($record['NUMERIC_PRECISION']=="") {
40 $insert_fmt.="'%s'";
41 $insert_val.=("\$_POST['".$record['COLUMN_NAME']."']");
42 $update_val.=("`".$record['COLUMN_NAME']."`='%s'");
43 } else {
44 $insert_fmt.="%s";
45 $insert_val.=("\$_POST['".$record['COLUMN_NAME']."']");
46 $update_val.=("`".$record['COLUMN_NAME']."`=%s");
47 }
48
49 ++$i;
50 }
51 }
52
53 /*
54 **增加记录
55 **sprintf("INSERT INTO `TABLE_NAME`(`COLUMN_NAME1`,`COLUMN_NAME1` ...) VALUES('%s','%s' ...)",
56 ** $POST['COLUMN_NAME1'],$POST['COLUMN_NAME2'],...);
57 **
58 **修改记录
59 **sprintf("UPDATE `TABLE_NAME` SET `COLUMN_NAME1`='%s',`COLUMN_NAME2`='%s' ... WHERE `id`=$s",
60 ** $POST['COLUMN_NAME1'],$POST['COLUMN_NAME2'], ... ,
61 ** ID);
62 **
63 **删除记录
64 **sprintf("DELETE FROM `TABLE_NAME` WHERE `id`=%s",
65 ** ID);
66 **
67 **查找记录
68 **sprintf("SELECT `COLUMN_NAME1`,`COLUMN_NAME2`, ... FROM `TABLE_NAME` WHERE `id`=%s",
69 ** ID);
70 */
71
72 $sqlcmd_insert=sprintf("INSERT INTO `%s`(%s) VALUES(%s)",
$tablename['TABLE_NAME'],$insert_key,$insert_fmt);
73 $sqlcmd_update=sprintf("UPDATE `%s` SET %s WHERE %s",
$tablename['TABLE_NAME'],$update_val,$primay_key);
74 $sqlcmd_delete=sprintf("DELETE FROM `%s` WHERE %s",
$tablename['TABLE_NAME'],$primay_key);
75 $sqlcmd_select=sprintf("SELECT %s FROM `%s` WHERE %s",
$insert_key,$tablename['TABLE_NAME'],$primay_key);
76 fprintf($f, "/*添加记录*/\r\n\$sqlcmd=sprintf(\"%s\",%s);\r\n",
$sqlcmd_insert,$insert_val);
77 fprintf($f, "/*修改记录*/\r\n\$sqlcmd=sprintf(\"%s\",%s);\r\n",
$sqlcmd_update,$primay_val);
78 fprintf($f, "/*删除记录*/\r\n\$sqlcmd=sprintf(\"%s\",%s);\r\n",
$sqlcmd_delete,$primay_val);
79 fprintf($f, "/*查找记录*/\r\n\$sqlcmd=sprintf(\"%s\",%s);\r\n",
$sqlcmd_select,$primay_val);
80 fwrite($f, "\r\n");
81 }
82 fclose($f);
83 mysql_close();
84 }
85 ?>
页:
[1]