当前位置：Gxlcms > 数据库问题 > mysqli

mysqli

时间：2021-07-01 10:21:17 帮助过：14人阅读

$mysqli= new mysqli(‘localhost‘, ‘root‘, ‘root‘, ‘test‘); 2 if (mysqli_connect_errno()) {printf("连接失败: %s\n", mysqli_connect_error());exit();}

 

技术分享图片 
  1. <span style="color: #008080;"> 1</span>     
  2. <span style="color: #008080;"> 2</span> <span style="color: #800080;">$city</span> = "宿迁2"<span style="color: #000000;">;
  3. </span><span style="color: #008080;"> 3</span> 
  4. <span style="color: #008080;"> 4</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> 创建准备好的语句 "SELECT District FROM City WHERE Name=?") </span><span style="color: #008000;">*/</span>
  5. <span style="color: #008080;"> 5</span> <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$stmt</span> = <span style="color: #800080;">$mysqli</span>->prepare("SELECT District FROM tree WHERE Name=?"<span style="color: #000000;">)) {
  6. </span><span style="color: #008080;"> 6</span> 
  7. <span style="color: #008080;"> 7</span>     <span style="color: #008000;">/*</span><span style="color: #008000;"> bind parameters for markers </span><span style="color: #008000;">*/</span>
  8. <span style="color: #008080;"> 8</span>     <span style="color: #800080;">$stmt</span>->bind_param("s", <span style="color: #800080;">$city</span><span style="color: #000000;">);
  9. </span><span style="color: #008080;"> 9</span> 
  10. <span style="color: #008080;">10</span>     <span style="color: #008000;">/*</span><span style="color: #008000;"> execute query </span><span style="color: #008000;">*/</span>
  11. <span style="color: #008080;">11</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">execute();
  12. </span><span style="color: #008080;">12</span> 
  13. <span style="color: #008080;">13</span>     <span style="color: #008000;">/*</span><span style="color: #008000;"> 绑定结果变量 </span><span style="color: #008000;">*/</span>
  14. <span style="color: #008080;">14</span>     <span style="color: #800080;">$stmt</span>->bind_result(<span style="color: #800080;">$id</span><span style="color: #000000;">);
  15. </span><span style="color: #008080;">15</span> 
  16. <span style="color: #008080;">16</span>     <span style="color: #008000;">/*</span><span style="color: #008000;"> fetch value </span><span style="color: #008000;">*/</span>
  17. <span style="color: #008080;">17</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">fetch();
  18. </span><span style="color: #008080;">18</span> 
  19. <span style="color: #008080;">19</span>     <span style="color: #008080;">printf</span>("%s 在区域 %s\n", <span style="color: #800080;">$city</span>, <span style="color: #800080;">$id</span><span style="color: #000000;">);
  20. </span><span style="color: #008080;">20</span> 
  21. <span style="color: #008080;">21</span>     <span style="color: #008000;">/*</span><span style="color: #008000;"> close statement </span><span style="color: #008000;">*/</span>
  22. <span style="color: #008080;">22</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">close();
  23. </span><span style="color: #008080;">23</span> <span style="color: #000000;">}    
  24. </span><span style="color: #008080;">24</span>     
  25. <span style="color: #008080;">25</span>
查询所在的行

 

技术分享图片 
  1. <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$mysqli</span>->multi_query(<span style="color: #800080;">$sql</span><span style="color: #000000;">))  
  2.     {
  3.         </span><span style="color: #0000ff;">do</span><span style="color: #000000;">{
  4.             </span><span style="color: #800080;">$res</span> = <span style="color: #800080;">$mysqli</span>-><span style="color: #000000;">store_result();  
  5.              </span><span style="color: #008080;">var_dump</span>(<span style="color: #800080;">$res</span>); <span style="color: #008000;">//</span><span style="color: #008000;">输出信息  </span>
  6.             <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$res</span><span style="color: #000000;"> instanceof mysqli_result)  
  7.             {  
  8.             </span><span style="color: #008000;">//</span><span style="color: #008000;">select 查询  </span>
  9.                 <span style="color: #0000ff;">while</span> (<span style="color: #800080;">$row</span> = <span style="color: #800080;">$res</span>-><span style="color: #000000;">fetch_assoc())  
  10.                 {  
  11.                 </span><span style="color: #008000;">//</span><span style="color: #008000;">...  </span>
  12. <span style="color: #000000;">                }  
  13.             }  
  14.             </span><span style="color: #0000ff;">else</span><span style="color: #000000;">  
  15.             {  
  16.                 </span><span style="color: #008000;">//</span><span style="color: #008000;">insert update delete查询  </span>
  17. <span style="color: #000000;">            }  
  18.   
  19.         } </span><span style="color: #0000ff;">while</span> (<span style="color: #800080;">$mysqli</span>->more_results() && <span style="color: #800080;">$mysqli</span>->next_result());  <span style="color: #008000;">//</span><span style="color: #008000;">调用next_result()之前必须调用more_result()  </span>
  20.     }
执行一个查询

 

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (<span style="color: #800080;">$stmt</span> = <span style="color: #800080;">$mysqli</span>->prepare(<span style="color: #800080;">$query</span><span style="color: #000000;">)) {
  2. </span><span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span>     <span style="color: #008000;">//</span><span style="color: #008000;"> 执行查询 /</span>
  4. <span style="color: #008080;"> 4</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">execute();
  5. </span><span style="color: #008080;"> 5</span> 
  6. <span style="color: #008080;"> 6</span>     <span style="color: #008000;">//</span><span style="color: #008000;"> 存储结果//</span>
  7. <span style="color: #008080;"> 7</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">store_result();
  8. </span><span style="color: #008080;"> 8</span> 
  9. <span style="color: #008080;"> 9</span>     <span style="color: #008080;">printf</span>("行数: %d.\n", <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">num_rows);
  10. </span><span style="color: #008080;">10</span> 
  11. <span style="color: #008080;">11</span>     <span style="color: #008000;">//</span><span style="color: #008000;"> 成功的结果 //</span>
  12. <span style="color: #008080;">12</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">free_result();
  13. </span><span style="color: #008080;">13</span> 
  14. <span style="color: #008080;">14</span>     <span style="color: #008000;">//</span><span style="color: #008000;"> 关闭语句 //</span>
  15. <span style="color: #008080;">15</span>     <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">close();
  16. </span><span style="color: #008080;">16</span> }

 

技术分享图片 
  1. <span style="color: #008080;"> 1</span>     <span style="color: #008000;">//</span><span style="color: #008000;"> $stmt=$this->mysqli->prepare("SELECT surname, name, user_id, last_m_own, last_m_str, role FROM users WHERE referer_id=(?)");</span>
  2. <span style="color: #008080;"> 2</span>     <span style="color: #800080;">$stmt</span>=<span style="color: #800080;">$this</span>->mysqli->prepare("select id,name,pid  from `tree`"<span style="color: #000000;">);
  3. </span><span style="color: #008080;"> 3</span>     <span style="color: #800080;">$stmt</span>->bind_param(‘i‘,<span style="color: #800080;">$referer_id</span><span style="color: #000000;">);
  4. </span><span style="color: #008080;"> 4</span>     <span style="color: #800080;">$stmt</span>->execute(); <span style="color: #008000;">//</span><span style="color: #008000;"> 执行查询 </span>
  5. <span style="color: #008080;"> 5</span>     <span style="color: #800080;">$stmt</span>->store_result();<span style="color: #008000;">//</span><span style="color: #008000;"> 存储结果</span>
  6. <span style="color: #008080;"> 6</span>     <span style="color: #800080;">$stmt</span>->bind_result(<span style="color: #800080;">$ans</span>[‘id‘], <span style="color: #800080;">$ans</span>[‘name‘], <span style="color: #800080;">$ans</span>[‘pid‘<span style="color: #000000;">]);
  7. </span><span style="color: #008080;"> 7</span>     <span style="color: #800080;">$j</span>=<span style="color: #800080;">$stmt</span>-><span style="color: #000000;">num_rows;
  8. </span><span style="color: #008080;"> 8</span>     <span style="color: #0000ff;">for</span> (<span style="color: #800080;">$i</span>=0;<span style="color: #800080;">$i</span><<span style="color: #800080;">$j</span>;<span style="color: #800080;">$i</span>++<span style="color: #000000;">){
  9. </span><span style="color: #008080;"> 9</span>         <span style="color: #800080;">$stmt</span>->data_seek(<span style="color: #800080;">$i</span><span style="color: #000000;">);
  10. </span><span style="color: #008080;">10</span>         <span style="color: #800080;">$stmt</span>-><span style="color: #000000;">fetch();
  11. </span><span style="color: #008080;">11</span>         <span style="color: #0000ff;">foreach</span> (<span style="color: #800080;">$ans</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$key</span>=><span style="color: #800080;">$value</span><span style="color: #000000;">){
  12. </span><span style="color: #008080;">12</span>             <span style="color: #800080;">$result</span>[<span style="color: #800080;">$i</span>][<span style="color: #800080;">$key</span>]=<span style="color: #800080;">$value</span><span style="color: #000000;">;
  13. </span><span style="color: #008080;">13</span>    <span style="color: #0000ff;">echo</span> <span style="color: #800080;">$result</span>[<span style="color: #800080;">$i</span><span style="color: #000000;">]  ;
  14. </span><span style="color: #008080;">14</span> 
  15. <span style="color: #008080;">15</span> <span style="color: #000000;">        }
  16. </span><span style="color: #008080;">16</span>     }
查询3

  

  1. <span style="color: #008080;">1</span> <span style="color: #008000;">/*</span><span style="color: #008000;"> 关闭连接 </span><span style="color: #008000;">*/</span>
  2. <span style="color: #008080;">2</span> <span style="color: #800080;">$mysqli</span>->close();

 

Php使用mysqli_result类处理结果集有以下几种方法

fetch_all() 抓取所有的结果行并且以关联数据，数值索引数组，或者两者皆有的方式返回结果集。
fetch_array() 以一个关联数组，数值索引数组，或者两者皆有的方式抓取一行结果。
fetch_object() 以对象返回结果集的当前行。
fetch_row() 以枚举数组方式返回一行结果
fetch_assoc() 以一个关联数组方式抓取一行结果。
fetch_field_direct() 以对象返回结果集中单字段的元数据。
fetch_field() 以对象返回结果集中的列信息。
fetch_fields() 以对象数组返回代表结果集中的列信息。

fetch_all (从结果集中取得所有行作为关联数组)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>   <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>   <span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>->fetch_all(MYSQLI_BOTH);<span style="color: #008000;">//</span><span style="color: #008000;">参数MYSQL_ASSOC、MYSQLI_NUM、MYSQLI_BOTH规定产生数组类型</span>
  4. <span style="color: #008080;">4</span>   <span style="color: #800080;">$n</span>=0<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span>   <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$n</span><<span style="color: #008080;">mysqli_num_rows</span>(<span style="color: #800080;">$result</span><span style="color: #000000;">)){
  6. </span><span style="color: #008080;">6</span>    <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["id"]."用户名：".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["name"]."密码：".<span style="color: #800080;">$row</span>[<span style="color: #800080;">$n</span>]["password"]."<br />"<span style="color: #000000;">;
  7. </span><span style="color: #008080;">7</span>    <span style="color: #800080;">$n</span>++<span style="color: #000000;">;
  8. </span><span style="color: #008080;">8</span>   }

 fetch_array (以一个关联数组，数值索引数组，或者两者皆有的方式抓取一行结果)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_array()){
  4. </span><span style="color: #008080;">4</span>             <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>["id"]."用户名：".<span style="color: #800080;">$row</span>[1]."密码：".<span style="color: #800080;">$row</span>["password"]."<br />"<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">        }
  6. </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">fetch_array方法不再有结果时返回返回NULL
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//其返回结果可以是关联数组也可以是数值数组索引,故$row["id"]、$row[1]都可以</span>

fetch_object (以对象返回结果集的当前行)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_object()){
  4. </span><span style="color: #008080;">4</span>             <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>->id."用户名：".<span style="color: #800080;">$row</span>->name."密码：".<span style="color: #800080;">$row</span>->password."<br />"<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">        }
  6. </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">如果无更多的行则返回NULL
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//返回的结果是对象，要以对象的方式调用</span>

fetch_row (以枚举数组方式返回一行结果)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_row()){
  4. </span><span style="color: #008080;">4</span>             <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>[0]."用户名：".<span style="color: #800080;">$row</span>[1]."密码：".<span style="color: #800080;">$row</span>[2]."<br />"<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">        }
  6. </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">无更多行时返回NULL
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以数值下标调用数组，a[0]正确、a["id"]则不</span>

fetch_assoc (以一个关联数组方式抓取一行结果)

  1. <span style="color: #008080;">1</span>   <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_assoc()){
  4. </span><span style="color: #008080;">4</span>             <span style="color: #0000ff;">echo</span> "ID:".<span style="color: #800080;">$row</span>["id"]."用户名：".<span style="color: #800080;">$row</span>["name"]."密码：".<span style="color: #800080;">$row</span>["password"]."<br />"<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">        }
  6. </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">无更多行时返回NULL
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以关联索引访问数组，a["id"]正确、a[0]则不</span>

fetch_field_direct (以对象返回结果集中单字段的元数据既单列的信息)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #800080;">$n</span>=0<span style="color: #000000;">;
  4. </span><span style="color: #008080;">4</span>         <span style="color: #0000ff;">while</span>(1<span style="color: #000000;">){
  5. </span><span style="color: #008080;">5</span>             <span style="color: #0000ff;">if</span>(!<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>->fetch_field_direct(<span style="color: #800080;">$n</span>++)) <span style="color: #0000ff;">break</span><span style="color: #000000;">;
  6. </span><span style="color: #008080;">6</span>             <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$row</span>->name."所在表：".<span style="color: #800080;">$row</span>->table."数据类型：".<span style="color: #800080;">$row</span>->type."<br />"<span style="color: #000000;">;
  7. </span><span style="color: #008080;">7</span> <span style="color: #000000;">        }
  8. </span><span style="color: #008080;">8</span> <span style="color: #008000;">//</span><span style="color: #008000;">fetch_field_direct（$n）只返回单个列,所以得不断调用该方法，没有该列时返回false</span>

fetch_field (以对象返回结果集中的列信息)

  1. <span style="color: #008080;">1</span>  <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #0000ff;">while</span>(<span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_field()){
  4. </span><span style="color: #008080;">4</span>             <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$row</span>->name."所在表：".<span style="color: #800080;">$row</span>->table."数据类型：".<span style="color: #800080;">$row</span>->type."<br />"<span style="color: #000000;">;
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">        }
  6. </span><span style="color: #008080;">6</span> <span style="color: #008000;">//</span><span style="color: #008000;">该方法检索所有的列
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//以对象方式返回列信息
  8. </span><span style="color: #008080;">8</span> <span style="color: #008000;">//返回对象属性如：name - 列名，table - 该列所在的表名，type - 该列的类型，等</span>

fetch_fields (以对象数组返回代表结果集中的列信息)

  1. <span style="color: #008080;">1</span>   <span style="color: #800080;">$sql</span>="select * from user"<span style="color: #000000;">;
  2. </span><span style="color: #008080;">2</span>         <span style="color: #800080;">$result</span>=<span style="color: #800080;">$link</span>->query(<span style="color: #800080;">$sql</span><span style="color: #000000;">);
  3. </span><span style="color: #008080;">3</span>         <span style="color: #800080;">$row</span>=<span style="color: #800080;">$result</span>-><span style="color: #000000;">fetch_fields();
  4. </span><span style="color: #008080;">4</span>         <span style="color: #0000ff;">foreach</span>(<span style="color: #800080;">$row</span> <span style="color: #0000ff;">as</span> <span style="color: #800080;">$val</span><span style="color: #000000;">){
  5. </span><span style="color: #008080;">5</span>             <span style="color: #0000ff;">echo</span> "列名:".<span style="color: #800080;">$val</span>->name."所在表：".<span style="color: #800080;">$val</span>->table."数据类型：".<span style="color: #800080;">$val</span>->type."<br />"<span style="color: #000000;">;
  6. </span><span style="color: #008080;">6</span> <span style="color: #000000;">        }
  7. </span><span style="color: #008080;">7</span> <span style="color: #008000;">//</span><span style="color: #008000;">该方法功能与目的fetch_field一样
  8. </span><span style="color: #008080;">8</span> <span style="color: #008000;">//不一样的是该方法返回一个对象数组(如：echo $row[0]->name;输出第一列的名字），而不是一次检索一列</span>

另：mysqli_result类还有其他方法

field_tell() 返回字段指针的位置
data_seek() 调整结果指针到结果集中的一个任意行
num_fields() 返回结果集中的字段数（列数）
field_seek() 调整字段指针到特定的字段开始位置
free()  释放与某个结果集相关的内存
fetch_lengths() 返回结果集中当前行的列长度
num_rows() 返回结果集中的行数

 

mysqli

标签：变量   cut   sed   statement   mysq   use   语句   event   fetch   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！