PHP 使用sprintf 拼接 sql语句

[php] view plain copy print?

1. <?php  
2. class BaseDAO{  
3.   
4.     //创建数据  
5.     public function create($table_name,$model){  
6.         $columns_k='';  
7.         $columns_v='';  
8.         foreach($model as $key=>$value){  
9.             $columns_k=empty($columns_k)?  
10.                 sprintf('`%s`',$key):  
11.                 sprintf('%s,`%s`',$columns_k,$key);  
12.             $columns_v=empty($columns_v)?  
13.                 sprintf("'%s'",$value):  
14.                 sprintf("%s,'%s'",$columns_v,$value);  
15.         }  
16.         $sqlcmd=sprintf("INSERT INTO `%s`(%s) VALUES(%s)",$table_name,  
17.             $columns_k,$columns_v);  
18.         echo $sqlcmd;  
19.     }  
20.   
21.     //修改数据  
22.     public function modify($table_name,$model,$primary){  
23.         $columns='';  
24.         $wheres='';  
25.         foreach($model as $key=>$value){  
26.             $columns=empty($columns)?  
27.                 sprintf("`%s`='%s'",$key,$value):  
28.                 sprintf("%s,`%s`='%s'",$columns,$key,$value);  
29.         }  
30.         $wheres=sprintf("`%s`='%s'",$primary['pk'],$primary['pv']);  
31.         $sqlcmd=sprintf("UPDATE `%s` SET %s WHERE %s",$table_name,$columns,$wheres);  
32.         echo $sqlcmd;  
33.     }  
34.   
35.     //删除数据  
36.     public function remove($table_name,$model){  
37.         $wheres='';  
38.         foreach($model as $key=>$value){  
39.             $wheres=empty($wheres)?  
40.                 sprintf("`%s`='%s'",$key,$value):  
41.                 sprintf("%s and `%s`='%s'",$wheres,$key,$value);  
42.         }  
43.         $sqlcmd=sprintf("DELETE FORM `%s` WHERE %s",  
44.             $table_name,$wheres);  
45.         echo $sqlcmd;  
46.     }  
47.   
48.     //查询数据  
49.     public function select($table_name,$conditons){  
50.         $wheres='';  
51.         foreach($conditons as $key=>$value){  
52.             $wheres=empty($wheres)?  
53.                 sprintf("`%s`='%s'",$key,$value):  
54.                 sprintf("%s and `%s`='%s'",$wheres,$key,$value);  
55.         }  
56.         $sqlcmd=sprintf("SELECT * FROM `%s` WHERE %s",$table_name,$wheres);  
57.         echo $sqlcmd;  
58.     }  
59.       
60.     //根据主键查找数据  
61.     public function findById($table_name,$primary){  
62.         $sqlcmd=sprintf("SELECT * FROM `%s` WHERE `%s`='%s' LIMIT 1",$table_name,$primary['pk'],$primary['pv']);  
63.         echo $sqlcmd;  
64.     }  
65. }  

<?phpclass BaseDAO{//创建数据public function create($table_name,$model){$columns_k='';$columns_v='';foreach($model as $key=>$value){$columns_k=empty($columns_k)?sprintf('`%s`',$key):sprintf('%s,`%s`',$columns_k,$key);$columns_v=empty($columns_v)?sprintf("'%s'",$value):sprintf("%s,'%s'",$columns_v,$value);}$sqlcmd=sprintf("INSERT INTO `%s`(%s) VALUES(%s)",$table_name,$columns_k,$columns_v);echo $sqlcmd;}//修改数据public function modify($table_name,$model,$primary){$columns='';$wheres='';foreach($model as $key=>$value){$columns=empty($columns)?sprintf("`%s`='%s'",$key,$value):sprintf("%s,`%s`='%s'",$columns,$key,$value);}$wheres=sprintf("`%s`='%s'",$primary['pk'],$primary['pv']);$sqlcmd=sprintf("UPDATE `%s` SET %s WHERE %s",$table_name,$columns,$wheres);echo $sqlcmd;}//删除数据public function remove($table_name,$model){$wheres='';foreach($model as $key=>$value){$wheres=empty($wheres)?sprintf("`%s`='%s'",$key,$value):sprintf("%s and `%s`='%s'",$wheres,$key,$value);}$sqlcmd=sprintf("DELETE FORM `%s` WHERE %s",$table_name,$wheres);echo $sqlcmd;}//查询数据public function select($table_name,$conditons){$wheres='';foreach($conditons as $key=>$value){$wheres=empty($wheres)?sprintf("`%s`='%s'",$key,$value):sprintf("%s and `%s`='%s'",$wheres,$key,$value);}$sqlcmd=sprintf("SELECT * FROM `%s` WHERE %s",$table_name,$wheres);echo $sqlcmd;}//根据主键查找数据public function findById($table_name,$primary){$sqlcmd=sprintf("SELECT * FROM `%s` WHERE `%s`='%s' LIMIT 1",$table_name,$primary['pk'],$primary['pv']);echo $sqlcmd;}}

二.调用实例

[php] view plain copy print?

1. <?php  
2. require_once("./BaseDAO.class.php");  
3. echo '<br/>增加<br/>';  
4. BaseDAO::create('cb_admin',array('username'=>'admin','password'=>md5('pwd')));  
5. echo '<br/>查询<br/>';  
6. BaseDAO::select('cb_admin',array('username'=>'admin','password'=>md5('pwd')));  
7. echo '<br/>修改<br/>';  
8. BaseDAO::modify('cb_admin',array('username'=>'admin','password'=>md5('pwd')),array('pk'=>'id','pv'=>1));  
9. echo '<br/>删除<br/>';  
10. BaseDAO::remove('cb_admin',array('username'=>'admin','password'=>md5('pwd')));  
11. echo '<br/>明细<br/>';  
12. BaseDAO::findById('cb_admin',array('pk'=>'id','pv'=>1));