WEB开发笔记 www.chhua.com 每日练习 Mysqli预处理语句的写法

至于为什么要使用MYSQLI的预处理语句,我不想再多过多的解释,请参考这里(http://www.chhua.com/web-note1623)。

今天之所以,要写这么一个代码范例,主要也是在WEB开发群(63417830)里曾有一位同学问过我,关于预处理语句的使用,当时我比较忙,也没有来得及回答,今天写的这段代码,希望他能看到。

Mysqli预处理语句插入代码演示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
<span style="color: #000000;font-weight: bold"><?php</span>
 
<span style="color: #000088">$mysqli</span><span style="color: #339933">=</span><span style="color: #000000;font-weight: bold">new</span> mysqli<span style="color: #009900">(</span><span style="color: #0000ff">"localhost"</span><span style="color: #339933">,</span><span style="color: #0000ff">"root"</span><span style="color: #339933">,</span><span style="color: #0000ff">""</span><span style="color: #339933">,</span><span style="color: #0000ff">"test"</span><span style="color: #009900">)</span><span style="color: #339933">;</span>
<span style="color: #000088">$mysqli</span><span style="color: #339933">-></span><span style="color: #004000">query</span><span style="color: #009900">(</span><span style="color: #0000ff">"SET NAMES 'GBK'"</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//设置为中文编码</span>
 
<span style="color: #666666;font-style: italic">//$stmt=$mysqli->stmt_init();//返回的就是一个预处理类对象</span>
 
<span style="color: #666666;font-style: italic">//准备一条语句</span>
<span style="color: #666666;font-style: italic">//$sql="INSERT INTO `test` ('name','age','dpc') VALUES(?,?,?)";//准备一条语句</span>
<span style="color: #666666;font-style: italic">//$stmt->prepare($sql);</span>
 
<span style="color: #666666;font-style: italic">//其实 MYSQLI 本身就有prepare()方法,所以,以上的部分可以改为:</span>
 
<span style="color: #666666;font-style: italic">//准备一条语句</span>
<span style="color: #000088">$sql</span><span style="color: #339933">=</span><span style="color: #0000ff">"INSERT INTO `test`(`name`,`age`,`dpc`) VALUES(?,?,?)"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//准备一条语句</span>
 
<span style="color: #000088">$stmt</span><span style="color: #339933">=</span><span style="color: #000088">$mysqli</span><span style="color: #339933">-></span><span style="color: #004000">prepare</span><span style="color: #009900">(</span><span style="color: #000088">$sql</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//直接返回了 stmt对象</span>
 
<span style="color: #666666;font-style: italic">//按占位符绑定参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">bind_param</span><span style="color: #009900">(</span><span style="color: #0000ff">"sis"</span><span style="color: #339933">,</span><span style="color: #000088">$name</span><span style="color: #339933">,</span><span style="color: #000088">$age</span><span style="color: #339933">,</span><span style="color: #000088">$dpc</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">// “sis”指的是参数的数据类型 s:字符串,i:整型,d:双精度,b:大二进制</span>
 
<span style="color: #666666;font-style: italic">//重复插入数据</span>
<span style="color: #000088">$name</span><span style="color: #339933">=</span><span style="color: #0000ff">"陈华1"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$age</span><span style="color: #339933">=</span><span style="color: #0000ff">"25"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$dpc</span><span style="color: #339933">=</span><span style="color: #0000ff">"WEB开发笔记撰写者,网址是www.chhua.com"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">execute</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//执行</span>
 
<span style="color: #000088">$name</span><span style="color: #339933">=</span><span style="color: #0000ff">"陈华2"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$age</span><span style="color: #339933">=</span><span style="color: #0000ff">"25"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$dpc</span><span style="color: #339933">=</span><span style="color: #0000ff">"WEB开发笔记撰写者,网址是www.chhua.com"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">execute</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//执行</span>
 
<span style="color: #000088">$name</span><span style="color: #339933">=</span><span style="color: #0000ff">"陈华3"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$age</span><span style="color: #339933">=</span><span style="color: #0000ff">"25"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$dpc</span><span style="color: #339933">=</span><span style="color: #0000ff">"WEB开发笔记撰写者,网址是www.chhua.com"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">execute</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//执行</span>
 
<span style="color: #000088">$name</span><span style="color: #339933">=</span><span style="color: #0000ff">"陈华4"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$age</span><span style="color: #339933">=</span><span style="color: #0000ff">"25"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$dpc</span><span style="color: #339933">=</span><span style="color: #0000ff">"WEB开发笔记撰写者,网址是www.chhua.com"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//填写参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">execute</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//执行</span>
 
<span style="color: #b1b100">echo</span> <span style="color: #0000ff">"最后插入的ID:"</span><span style="color: #339933">.</span><span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">insert_id</span><span style="color: #339933">.</span><span style="color: #0000ff">"<br>"</span><span style="color: #339933">;</span>
<span style="color: #b1b100">echo</span> <span style="color: #0000ff">"最后一条语句影响了行数:"</span><span style="color: #339933">.</span><span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">affected_rows</span><span style="color: #339933">;</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">close</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//关闭预处理对象</span>
<span style="color: #000000;font-weight: bold">?></span>

Mysqli预处理语句查询代码演示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
<span style="color: #000000;font-weight: bold"><?php</span>
 
<span style="color: #000088">$mysqli</span><span style="color: #339933">=</span><span style="color: #000000;font-weight: bold">new</span> mysqli<span style="color: #009900">(</span><span style="color: #0000ff">"localhost"</span><span style="color: #339933">,</span><span style="color: #0000ff">"root"</span><span style="color: #339933">,</span><span style="color: #0000ff">""</span><span style="color: #339933">,</span><span style="color: #0000ff">"test"</span><span style="color: #009900">)</span><span style="color: #339933">;</span>
<span style="color: #000088">$mysqli</span><span style="color: #339933">-></span><span style="color: #004000">query</span><span style="color: #009900">(</span><span style="color: #0000ff">"SET NAMES 'GBK'"</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//设置为中文编码</span>
 
<span style="color: #666666;font-style: italic">//准备一条语句</span>
<span style="color: #000088">$sql</span><span style="color: #339933">=</span><span style="color: #0000ff">"SELECT * FROM `test` WHERE `id`>?"</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//准备一条语句</span>
 
<span style="color: #000088">$stmt</span><span style="color: #339933">=</span><span style="color: #000088">$mysqli</span><span style="color: #339933">-></span><span style="color: #004000">prepare</span><span style="color: #009900">(</span><span style="color: #000088">$sql</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//直接返回了 stmt对象</span>
 
<span style="color: #666666;font-style: italic">//按占位符绑定参数</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">bind_param</span><span style="color: #009900">(</span><span style="color: #0000ff">"i"</span><span style="color: #339933">,</span><span style="color: #000088">$id</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">// “i”指的是参数的数据类型 s:字符串,i:整型,d:双精度,b:大二进制</span>
<span style="color: #000088">$id</span><span style="color: #339933">=</span><span style="color: #cc66cc">5</span><span style="color: #339933">;</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">execute</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//执行</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">bind_result</span><span style="color: #009900">(</span><span style="color: #000088">$id</span><span style="color: #339933">,</span><span style="color: #000088">$name</span><span style="color: #339933">,</span><span style="color: #000088">$age</span><span style="color: #339933">,</span><span style="color: #000088">$dpc</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//绑定结果注意,必须和查询语句中所查询的段名称相对应</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">fetch</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//这个方法是一条一条的获取结果集信息</span>
<span style="color: #b1b100">while</span> <span style="color: #009900">(</span><span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">fetch</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #009900">)</span><span style="color: #009900">{</span>
	<span style="color: #b1b100">echo</span> <span style="color: #000088">$name</span><span style="color: #339933">.</span><span style="color: #0000ff">"----"</span><span style="color: #339933">.</span><span style="color: #000088">$age</span><span style="color: #339933">.</span><span style="color: #0000ff">"----"</span><span style="color: #339933">.</span><span style="color: #000088">$dpc</span><span style="color: #339933">.</span><span style="color: #0000ff">"<Br>"</span><span style="color: #339933">;</span>
<span style="color: #009900">}</span>
<span style="color: #000088">$stmt</span><span style="color: #339933">-></span><span style="color: #004000">close</span><span style="color: #009900">(</span><span style="color: #009900">)</span><span style="color: #339933">;</span><span style="color: #666666;font-style: italic">//关闭预处理对象</span>
<span style="color: #000000;font-weight: bold">?></span>

好了,以上就是预处理的基本用法,如果还想了解更多关于MYSQLI扩展,还需要你去查看手册。