PHP mysqli 扩展库(面向对象/数据库操作封装/事务控制/预编译)

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){} 取出每一个数据