PHP mysqli 扩展库(面向对象/数据库操作封装/事务控制/预编译)
- WBOYOriginal
- 2016-06-06 19:36:061378browse
1、和mysql 扩展 库的区别： （1 安全性、稳定性更高 （2 提供了 面向 对象 和 面向 过程两种风格 2、php.ini 中的 extension=php_mysqli.dll 解除封印 3、 面向 对象 ：查询列表 1 ? php 2 3 // mysqli 操作 数据（ 面向 对象 风格） 4 5 # 1、创建Mysql 对
1、和mysql扩展库的区别：
（1 安全性、稳定性更高
（2 提供了面向对象和面向过程两种风格
2、php.ini 中的 extension=php_mysqli.dll 解除封印
3、面向对象：查询列表
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli <strong>操作</strong>数据（<strong>面向</strong><strong>对象</strong>风格）</span>
<span> 4</span>
<span> 5</span> <span>#</span><span>1、创建Mysql<strong>对象</strong></span>
<span> 6</span>
<span> 7</span> <span>$mysqli</span>=<span>new</span> MySQLi("127.0.0.1","root","daomul","test"<span>);
</span><span> 8</span> <span>if</span>(!<span>$mysqli</span><span>)
</span><span> 9</span> <span> {
</span><span>10</span> <span>die</span>("连接失败！".<span>$mysqli</span>-><span>connect_error);
</span><span>11</span> <span> }
</span><span>12</span>
<span>13</span> <span>#</span><span>2、<strong>操作</strong><strong>数据库</strong></span>
<span>14</span>
<span>15</span> <span>$sql</span>="select * from user1"<span>;
</span><span>16</span> <span>$res</span>=<span>$mysqli</span>->query(<span>$sql</span><span>);
</span><span>17</span> <span>#</span><span>3、处理结果</span>
<span>18</span>
<span>19</span> <span>while</span>(<span>$row</span>=<span>$res</span>-><span>fetch_row())
</span><span>20</span> <span> {
</span><span>21</span> <span>foreach</span>(<span>$row</span> <span>as</span> <span>$key</span>=> <span>$val</span><span>)
</span><span>22</span> <span> {
</span><span>23</span> <span>echo</span> "-- <span>$val</span>"<span>;
</span><span>24</span> <span> }
</span><span>25</span> <span>echo</span> "<br>"<span>;
</span><span>26</span> <span> }
</span><span>27</span> <span>#</span><span>4、关闭资源</span>
<span>28</span> <span>$res</span>->free();<span>//</span><span>释放内存</span>
<span>29</span> <span>$mysqli</span>->close();<span>//</span><span>关闭连接</span>
<span>30</span>
<span>31</span> ?>
4、面向对象：封装类后实现
4.1 Sqliconnect.class.php
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>Class</span><span> Sqliconnect
</span><span> 4</span> <span> {
</span><span> 5</span> <span>private</span> <span>$mysqli</span><span>;
</span><span> 6</span> <span>private</span> <span>static</span> <span>$host</span>="127.0.0.1"<span>;
</span><span> 7</span> <span>private</span> <span>static</span> <span>$root</span>="root"<span>;
</span><span> 8</span> <span>private</span> <span>static</span> <span>$password</span>="daomul"<span>;
</span><span> 9</span> <span>private</span> <span>static</span> <span>$db</span>="test"<span>;
</span><span>10</span>
<span>11</span> <span>function</span><span> __construct()
</span><span>12</span> <span> {
</span><span>13</span> <span>$this</span>->mysqli=<span>new</span> MySQLi(self::<span>$host</span>,self::<span>$root</span>,self::<span>$password</span>,self::<span>$db</span><span>);
</span><span>14</span> <span>if</span>(!<span>$this</span>-><span>mysqli)
</span><span>15</span> <span> {
</span><span>16</span> <span>die</span>("<strong>数据库</strong>连接失败！".<span>$this</span>->mysqli-><span>connect_error);
</span><span>17</span> <span> }
</span><span>18</span>
<span>19</span> <span>$this</span>->mysqli->query("set names utf8"<span>);
</span><span>20</span> <span> }
</span><span>21</span>
<span>22</span> <span>//</span><span>查询<strong>操作</strong></span>
<span>23</span> <span>public</span> <span>function</span> excute_dql(<span>$sql</span><span>)
</span><span>24</span> <span> {
</span><span>25</span> <span>$res</span>=<span>$this</span>->mysqli->query(<span>$sql</span>) or <span>die</span>("数据查询失败".<span>$this</span>->mysqli-><span>error);
</span><span>26</span> <span>return</span> <span>$res</span><span>;
</span><span>27</span>
<span>28</span> <span> }
</span><span>29</span>
<span>30</span> <span>//</span><span>增删改<strong>操作</strong></span>
<span>31</span> <span>public</span> <span>function</span> excute_dml(<span>$sql</span><span>)
</span><span>32</span> <span> {
</span><span>33</span> <span>$res</span>=<span>$this</span>->mysqli->query(<span>$sql</span>) or <span>die</span>("数据<strong>操作</strong>失败".<span>$this</span>->mysqli-><span>error);
</span><span>34</span> <span>if</span>(!<span>$res</span><span>)
</span><span>35</span> <span> {
</span><span>36</span> <span>echo</span> "数据<strong>操作</strong>失败"<span>;
</span><span>37</span> <span> }
</span><span>38</span> <span>else</span>
<span>39</span> <span> {
</span><span>40</span> <span>if</span>(<span>$this</span>->mysqli->affected_rows>0<span>)
</span><span>41</span> <span> {
</span><span>42</span> <span>echo</span> "<strong>操作</strong>成功！"<span>;
</span><span>43</span> <span> }
</span><span>44</span> <span>else</span>
<span>45</span> <span> {
</span><span>46</span> <span>echo</span> "0行数据受影响！"<span>;
</span><span>47</span> <span> }
</span><span>48</span> <span> }
</span><span>49</span> <span> }
</span><span>50</span>
<span>51</span> <span> }
</span><span>52</span> ?>
4.2 调用页面startsqli.php
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli <strong>操作</strong>数据（<strong>面向</strong><strong>对象</strong>风格）</span>
<span> 4</span>
<span> 5</span>
<span> 6</span> <span>require_once</span> "Sqliconnect.class.php"<span>;
</span><span> 7</span>
<span> 8</span> <span>$Sqliconnect</span>=<span>new</span><span> Sqliconnect();
</span><span> 9</span>
<span>10</span> <span>//</span><span>$sql="insert into user1(name,password,email,age) values('帝都',md5('gg'),'sd@sohu.com',23)";
</span><span>11</span> <span> //$sql="delete from user1 where id=11";
</span><span>12</span> <span> //$res=$Sqliconnect->excute_dml($sql);</span>
<span>13</span>
<span>14</span> <span>$sql</span>="select name from user1;"<span>;
</span><span>15</span> <span>$res</span>=<span>$Sqliconnect</span>->excute_dql(<span>$sql</span><span>);
</span><span>16</span> <span>while</span>(<span>$row</span>=<span>$)
</span><span>17</span>
<span>18</span> <span>$res</span>-><span>free();
</span><span>19</span> ?>
5、同时执行多条数据库语句 multiQuery.php
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli <strong>操作</strong>数据（<strong>面向</strong><strong>对象</strong>风格）</span>
<span> 4</span>
<span> 5</span> <span>#</span><span>1、创建Mysql<strong>对象</strong></span>
<span> 6</span>
<span> 7</span> <span>$mysqli</span>=<span>new</span> MySQLi("127.0.0.1","root","daomul","test"<span>);
</span><span> 8</span> <span>if</span>(!<span>$mysqli</span><span>)
</span><span> 9</span> <span> {
</span><span>10</span> <span>die</span>("连接失败！".<span>$mysqli</span>-><span>connect_error);
</span><span>11</span> <span> }
</span><span>12</span>
<span>13</span> <span>#</span><span>2、<strong>操作</strong><strong>数据库</strong></span>
<span>14</span>
<span>15</span> <span>$sqls</span>="select * from user1;"<span>;
</span><span>16</span> <span>$sqls</span>.="select * from user1"<span>;
</span><span>17</span>
<span>18</span> <span>#</span><span>3、处理结果</span>
<span>19</span>
<span>20</span> <span>if</span>(<span>$res</span>=<span>$mysqli</span>->multi_query(<span>$sqls</span><span>))
</span><span>21</span> <span> {
</span><span>22</span> <span>echo</span> "211"<span>;
</span><span>23</span> <span>do</span>
<span>24</span> <span> {
</span><span>25</span> <span>//</span><span>从mysqli连续取出第一个结果集</span>
<span>26</span> <span>$result</span>=<span>$mysqli</span>-><span>store_result();
</span><span>27</span>
<span>28</span> <span>//</span><span>显示mysqli result<strong>对象</strong></span>
<span>29</span> <span>while</span>(<span>$row</span>=<span>$result</span>-><span>fetch_row())
</span><span>30</span> <span> {
</span><span>31</span> <span>foreach</span>(<span>$row</span> <span>as</span> <span>$key</span>=> <span>$val</span><span>)
</span><span>32</span> <span> {
</span><span>33</span> <span>echo</span> "-- <span>$val</span>"<span>;
</span><span>34</span> <span> }
</span><span>35</span> <span>echo</span> "<br>"<span>;
</span><span>36</span> <span> }
</span><span>37</span>
<span>38</span> <span>$result</span>->free();<span>//</span><span>及时释放当前结果集，并进入下一结果集
</span><span>39</span>
<span>40</span> <span> //判断是否有下一个结果集</span>
<span>41</span> <span>if</span>(!<span>$mysqli</span>-><span>more_results())
</span><span>42</span> <span> {
</span><span>43</span> <span>break</span><span>;
</span><span>44</span> <span> }
</span><span>45</span> <span>echo</span> "<br>************新的结果集**************"<span>;
</span><span>46</span>
<span>47</span> }<span>while</span>(<span>$mysqli</span>-><span>next_result());
</span><span>48</span> <span> }
</span><span>49</span>
<span>50</span> <span>#</span><span>4、关闭资源</span>
<span>51</span> <span>$mysqli</span>->close();<span>//</span><span>关闭连接 </span>
<span>52</span>
<span>53</span>
<span>54</span> ?>
6、事务控制
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli <strong>操作</strong>数据（<strong>面向</strong><strong>对象</strong>风格）
</span><span> 4</span>
<span> 5</span>
<span> 6</span> <span> // <strong>数据库</strong> ：create table account(id int primary key,balance float);</span>
<span> 7</span>
<span> 8</span> <span>$mysqli</span>=<span>new</span> MySQLi("127.0.0.1","root","daomul","test"<span>);
</span><span> 9</span> <span>if</span>(!<span>$mysqli</span><span>)
</span><span>10</span> <span> {
</span><span>11</span> <span>die</span>("<strong>数据库</strong>连接失败！".<span>$mysqli</span>-><span>connect_error);
</span><span>12</span> <span> }
</span><span>13</span> <span>//</span><span>将提交设为false</span>
<span>14</span> <span>$mysqli</span>->autocommit(<span>false</span><span>);
</span><span>15</span>
<span>16</span> <span>$sql1</span>="update account set balance=balance+1 where id=1;";<span>//</span><span>没错的语句</span>
<span>17</span> <span>$sql2</span>="update accounterror2 set balance=balance-1 where id=2";<span>//</span><span>有错的语句</span>
<span>18</span>
<span>19</span> <span>$res1</span>=<span>$mysqli</span>->query(<span>$sql1</span><span>);
</span><span>20</span> <span>$res2</span>=<span>$mysqli</span>->query(<span>$sql2</span><span>);
</span><span>21</span>
<span>22</span> <span>if</span>(!<span>$res1</span>||!<span>$res2</span><span>)
</span><span>23</span> <span> {
</span><span>24</span> <span>//</span><span>回滚：其中一个不成功即回滚不提交</span>
<span>25</span> <span>echo</span> "有错，回滚，请重新提交！"<span>;
</span><span>26</span> <span>$mysqli</span>->rollback();<span>//</span><span>die("<strong>操作</strong>失败！".$mysqli->error);</span>
<span>27</span> <span> }
</span><span>28</span> <span>else</span>
<span>29</span> <span> {
</span><span>30</span> <span>//</span><span>所有均成功则提交</span>
<span>31</span> <span>echo</span> "所有提交成功！"<span>;
</span><span>32</span> <span>$mysqli</span>-><span>commit();
</span><span>33</span> <span> }
</span><span>34</span>
<span>35</span> <span>$mysqli</span>-><span>close();
</span><span>36</span> <span>/*</span>
<span>37</span> <span> 1、 start transaction; 开启<strong>事务</strong>
</span><span>38</span> <span> 2、svaepoint a; 做保存点
</span><span>39</span> <span> 3、执行<strong>操作</strong>1;
</span><span>40</span> <span> 4、 svaepoint b;
</span><span>41</span> <span> 5、执行<strong>操作</strong>2;
</span><span>42</span> <span> ...
</span><span>43</span> <span> 6、rollback to a/b; 回滚或者是提交
</span><span>44</span> <span> 7、commit
</span><span>45</span>
<span>46</span> <span> <strong>事务</strong><strong>控制</strong>特点acid 原子性/一致性/隔离性/持久性
</span><span>47</span> <span>*/</span>
<span>48</span> ?>
7、预处理技术
主要在连接和编译过程精简，还可以SQL防止注入
7.1 预编译插入多个数据
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli 预<strong>编译</strong>演示</span>
<span> 4</span>
<span> 5</span> <span>#</span><span>1、创建mysqli<strong>对象</strong></span>
<span> 6</span> <span>$mysqli</span>=<span>new</span> MySQLi("127.0.0.1","root","daomul","test"<span>);
</span><span> 7</span> <span>if</span>(!<span>$mysqli</span><span>)
</span><span> 8</span> <span> {
</span><span> 9</span> <span>die</span>("<strong>数据库</strong>连接失败！".<span>$mysqli</span>-><span>connect_error);
</span><span>10</span> <span> }
</span><span>11</span>
<span>12</span> <span>#</span><span>2、创建预<strong>编译</strong><strong>对象</strong></span>
<span>13</span> <span>$sql</span>="insert into user1(name,password,email,age) values(?,?,?,?);";<span>//</span><span>暂时不赋值，用问号代替</span>
<span>14</span> <span>$stmt</span>=<span>$mysqli</span>->prepare(<span>$sql</span>) or <span>die</span>(<span>$mysqli</span>-><span>error);
</span><span>15</span>
<span>16</span> <span>/*</span><span>*******************************可重复执行时需要的代码start********************************</span><span>*/</span>
<span>17</span> <span>#</span><span>3、绑定参数</span>
<span>18</span> <span>$name</span>='小明5'<span>;
</span><span>19</span> <span>$password</span>='34f'<span>;
</span><span>20</span> <span>$email</span>='ssd@qq.com'<span>;
</span><span>21</span> <span>$age</span>='1'<span>;
</span><span>22</span>
<span>23</span> <span>#</span><span>4、参数赋值（第一个参数指代参数的类型缩写，string-s,int-i,double-d,bool-b</span>
<span>24</span> <span>$stmt</span>->bind_param("sssi",<span>$name</span>,<span>$password</span>,<span>$email</span>,<span>$age</span><span>);
</span><span>25</span>
<span>26</span> <span>#</span><span>5、执行代码（返回布尔类型）</span>
<span>27</span> <span>$flag</span>=<span>$stmt</span>-><span>execute();
</span><span>28</span>
<span>29</span> <span>/*</span><span>*******************************可重复执行时需要的代码 end***********************************</span><span>*/</span>
<span>30</span>
<span>31</span> <span>#</span><span>6、结果以及释放</span>
<span>32</span>
<span>33</span> <span>if</span>(!<span>$flag</span><span>)
</span><span>34</span> <span> {
</span><span>35</span> <span>die</span>("<strong>操作</strong>失败".<span>$stmt</span>-><span>error);
</span><span>36</span> <span> }
</span><span>37</span> <span>else</span>
<span>38</span> <span> {
</span><span>39</span> <span>echo</span> "<strong>操作</strong>成功！"<span>;
</span><span>40</span> <span> }
</span><span>41</span>
<span>42</span> <span>$mysqli</span>-><span>close();
</span><span>43</span>
<span>44</span>
<span>45</span> ?>
7.2 预编译查询多个数据
<span> 1</span> <span>php
</span><span> 2</span>
<span> 3</span> <span>//</span><span>mysqli 预<strong>编译</strong>演示</span>
<span> 4</span>
<span> 5</span> <span>#</span><span>1、创建mysqli<strong>对象</strong></span>
<span> 6</span> <span>$mysqli</span>=<span>new</span> MySQLi("127.0.0.1","root","daomul","test"<span>);
</span><span> 7</span> <span>if</span>(!<span>$mysqli</span><span>)
</span><span> 8</span> <span> {
</span><span> 9</span> <span>die</span>("<strong>数据库</strong>连接失败！".<span>$mysqli</span>-><span>connect_error);
</span><span>10</span> <span> }
</span><span>11</span>
<span>12</span> <span>/*</span><span>*******************************可重复执行时需要的代码 start******************************</span><span>*/</span>
<span>13</span>
<span>14</span> <span>#</span><span>2、创建预<strong>编译</strong><strong>对象</strong></span>
<span>15</span> <span>$sql</span>="select id,name,email from user1 where id>?;";<span>//</span><span>id,name,email和后面的结果集bind_result()对应</span>
<span>16</span> <span>$stmt</span>=<span>$mysqli</span>->prepare(<span>$sql</span>) or <span>die</span>(<span>$mysqli</span>-><span>error);
</span><span>17</span>
<span>18</span> <span>#</span><span>3、绑定参数</span>
<span>19</span> <span>$id</span>=5<span>;
</span><span>20</span>
<span>21</span> <span>#</span><span>4、参数赋值（第一个参数指代参数的类型缩写，string-s,int-i,double-d,bool-b</span>
<span>22</span> <span>$stmt</span>->bind_param("i",<span>$id</span>);<span>//</span><span>绑定参数</span>
<span>23</span> <span>$stmt</span>->bind_result(<span>$id</span>,<span>$name</span>,<span>$email</span>);<span>//</span><span>绑定结果集</span>
<span>24</span>
<span>25</span> <span>#</span><span>5、执行代码（返回布尔类型）</span>
<span>26</span> <span>$stmt</span>-><span>execute();
</span><span>27</span>
<span>28</span> <span>#</span><span>6、取出结果集显示</span>
<span>29</span> <span>while</span>(<span>$stmt</span>-><span>fetch())
</span><span>30</span> <span> {
</span><span>31</span> <span>echo</span> "<br><span>$id</span>--<span>$name</span>--<span>$email</span>"<span>;
</span><span>32</span> <span> }
</span><span>33</span>
<span>34</span> <span>/*</span><span>*******************************可重复执行时需要的代码 end******************************</span><span>*/</span>
<span>35</span>
<span>36</span> <span>#</span><span>7、结果以及释放
</span><span>37</span>
<span>38</span> <span> //释放结果</span>
<span>39</span> <span>$stmt</span>-><span>free_result();
</span><span>40</span> <span>//</span><span>关闭预<strong>编译</strong>语句</span>
<span>41</span> <span>$stmt</span>-><span>close();
</span><span>42</span> <span>//</span><span>关闭<strong>数据库</strong>连接</span>
<span>43</span> <span>$mysqli</span>-><span>close();
</span><span>44</span>
<span>45</span>
<span>46</span> ?>
8、其他函数
(1 获取行数和列数 num_rows field_count
(2 获取结果集的一列 :表头 例如
$result=$mysqli->query();
$result->fetch_field();
(3 取出数据
$row=$result->fetch_row(); //获得每一行数据
再通过 foreach($row as $val){} 取出每一个数据
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP调用接口返回的一直为空Next article：php调用java的.class文件