1、mysqli面向过程
针对成功的 SELECT、SHOW、DESCRIBE 或 EXPLAIN 查询,将返回一个 mysqli_result 对象。针对其他成功的查询,将返回 TRUE。如果失败,则返回 FALSE。
mysqli查询操作:
header('content-type:text/html;charset=utf-8'); date_default_timezone_set('PRC'); // 设置中国时区 //1.连接数据库 mysql_connect(数据库地址,用户名,密码); $link=mysqli_connect('localhost','root','','test') or die('Connect Error:'.mysqli_connect_errno().":".mysqli_connect_error()); //2.设置编码方式 mysqli_set_charset($link,'UTF8'); //3.执行SQL查询 $sql="SELECT * FROM users"; $result=mysqli_query($link,$sql); if($result && mysqli_num_rows($result)>0){ while($row=mysqli_fetch_array($result, MYSQLI_ASSOC)){ //print_r($row); $rows[]=$row; } } var_dump($rows); //释放结果集 mysqli_free_result($result); mysqli_close($link);
mysqli插入单条数据操作:
header('content-type:text/html;charset=utf-8'); //1.连接 $link=mysqli_connect('localhost','root','','test') or die('Connect Error:'.mysqli_connect_errno().":".mysqli_connect_error()); //2.设置编码方式 mysqli_set_charset($link,'UTF8'); //3.执行SQL查询 $sql="INSERT users(name,tel,sex) VALUES('冷暖自知一抹茶ck','18210890833','男');"; $res=mysqli_query($link, $sql); if($res){ echo 'AUTO_INCREMENT:'.mysqli_insert_id($link)."<br/>"; echo 'AFFECTED ROWS:'.mysqli_affected_rows($link); }else{ echo 'ERROR:'; echo mysqli_errno($link).':'.mysqli_error($link); } //4.关闭连接 mysqli_close($link);
mysqli插入多条数据操作:
header('content-type:text/html;charset=utf-8'); //1.连接 $link=mysqli_connect('localhost','root','','test') or die('Connect Error:'.mysqli_connect_errno().":".mysqli_connect_error()); //2.设置编码方式 mysqli_set_charset($link,'UTF8'); //3.执行SQL查询 $sql="UPDATE users SET sex='女' WHERE id=1;"; $sql.="DELETE FROM users WHERE id=11"; $res=mysqli_multi_query($link, $sql); //执行多条语句查询 var_dump($res); //4.关闭连接 mysqli_close($link);
mysqli预处理语句:
header('content-type:text/html;charset=utf-8'); //1.连接 $link=mysqli_connect('localhost','root','','test') or die('Connect Error:'.mysqli_connect_errno().":".mysqli_connect_error()); //2.设置编码方式 mysqli_set_charset($link,'UTF8'); //3.执行SQL查询 $sql="INSERT users(name,tel,sex) VALUES(?,?,?);"; $stmt=mysqli_prepare($link, $sql); mysqli_stmt_bind_param($stmt, 'sss',$username,$tel,$sex); $username='冷暖自知一抹茶ck2'; $tel='15188305549'; $sex='女'; $res=mysqli_stmt_execute($stmt); var_dump($res); mysqli_close($link);
2、mysqli面向对象:
mysqli查询数据:
$mysqli=new mysqli('localhost','root','','test'); if($mysqli->errno){ die('Connect Error'.$mysqli->error); } $mysqli->set_charset('UTF8'); $username='冷暖自知一抹茶ck'; $username=$mysqli->escape_string($username); $sql="SELECT * FROM users WHERE name='{$username}'"; $mysqli_result=$mysqli->query($sql); if($mysqli_result && $num = $mysqli_result->num_rows>0){ $row=$mysqli_result->fetch_assoc(); var_dump($row); } $mysqli_result->close(); $mysqli->close();
mysqli预处理语句:
header('content-type:text/html;charset=utf-8'); $mysqli=new mysqli('localhost','root','','test'); if($mysqli->errno){ die('Connect Error'.$mysqli->error); } $mysqli->set_charset('UTF8'); $username = "冷暖自知一抹茶ck"; $sql="SELECT * FROM users WHERE name = ? "; $mysqli_stmt=$mysqli->prepare($sql); //准备预处理语句 $mysqli_stmt->bind_param('s',$username); //绑定参数 s,i,d if($mysqli_stmt->execute()){ //执行预处理语句 $mysqli_stmt->store_result(); if($mysqli_stmt->num_rows>0){ echo '登陆成功'; }else{ echo '登陆失败'; } } //释放结果集 $mysqli_stmt->free_result(); //关闭预处理语句 $mysqli_stmt->close(); $mysqli->close(); header('content-type:text/html;charset=utf-8'); date_default_timezone_set('PRC'); //设置中国时区 $mysqli=new mysqli('localhost','root','','test'); if($mysqli->errno){ die('Connect Error'.$mysqli->error); } $mysqli->set_charset('UTF8'); $username = "冷暖自知一抹茶ck"; $sql="SELECT * FROM users WHERE name = ? "; $mysqli_stmt=$mysqli->prepare($sql); //准备预处理语句 $mysqli_stmt->bind_param('s',$username); //绑定参数 s,i,d if($mysqli_stmt->execute()){ //执行预处理语句 $mysqli_stmt->bind_result($id,$username,$tel,$sex); //bind_result():绑定结果集中的值到变量 //遍历结果集 while($row = $mysqli_stmt->fetch()){ echo '编号:'.$id; echo '用户名:'.$username; echo '电话: '.$tel; echo '性别:'.$sex; } } //释放结果集 $mysqli_stmt->free_result(); //关闭预处理语句 $mysqli_stmt->close(); $mysqli->close();
3、mysqli事务
header('content-type:text/html;charset=utf-8'); $mysqli=new mysqli('localhost','root','','test'); if($mysqli->errno){ die('Connect Error'.$mysqli->error); } $mysqli->set_charset('UTF8'); $mysqli->autocommit(FALSE); $sql="UPDATE users SET sex='男' WHERE id='1'"; $res=$mysqli->query($sql); $res_affect=$mysqli->affected_rows; $sql1="UPDATE users SET name='冷暖自知一抹茶ck4' WHERE id=2"; $res1=$mysqli->query($sql1); $res1_affect=$mysqli->affected_rows; var_dump($res); var_dump($res1); if($res && $res_affect > 0 && $res1 && $res1_affect>0){ $mysqli->commit(); echo '更新成功'; $mysqli->autocommit(TRUE); }else{ $mysqli->rollback(); echo '更新失败'; } $mysqli->close();
4、mysqli--tip:
$mysqli = new mysqli('localhost','root','root'); //建立到MySQL数据库的连接 $mysqli->select_db('test'); //打开指定的数据库 $mysql = new mysqli(); $mysqli ->connect('localhost','root','root'); $mysqli->select_db('test'); header('content-type:text/html;charset=utf-8'); //1.建立到MySQL的连接 $mysqli = @new mysqli('localhost','root','root','test'); //建立连接的同时打开指定数据库 if($mysqli->connect_errno){ //$mysqli->connect_errno:得到连接产生的错误编号 die('Connect Error:'.$mysqli->connect_error()); //$mysqli->connect_error:得到连接产生的错误信息 } //2.设置默认的客户端编码方式utf8 $mysqli->set_charset('utf8'); //3.执行SQL查询 $sql=<< CREATE TABLE IF NOT EXISTS mysqli( id TINYINT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL ); EOF; $res = $mysqli->query($sql); //执行单条SQL语句,只能执行一条SQL语句 var_dump($res); /* SELECT/DESC/DESCRIBE/SHOW/EXPLAIN执行成功返回mysqli_result对象,执行失败返回false 对于其它SQL语句的执行,执行成功返回true,否则返回false */ //4、关闭连接 $mysqli->close();//关闭连接 //针对多条SQL语句的查询 //$sql="INSERT user(username,password,age) VALUES('imooc3','imooc3',32);"; //$sql.="UPDATE1 user SET age=5 WHERE id=28;"; //$sql.="DELETE FROM user WHERE id=25;"; //use_result()/store_result():获取第一条查询产生的结果集 //more_results():检测是否有更多的结果集 //next_result():将结果集指针向下移动一位 //if($mysqli->multi_query($sql)){ // do{ // if($mysqli_result=$mysqli->store_result()){ // $rows[]=$mysqli_result->fetch_all(MYSQLI_ASSOC); // } // }while($mysqli->more_results() && $mysqli->next_result()); //}else{ // echo $mysqli->error; //} //print_r($rows); $mysqli->connect_errno;//得到连接产生的错误编号 $mysqli->connect_error;//得到连接产生的错误信息 $mysqli->errno;//得到上一步操作产生的错误号 $mysqli->error;//得到上一步操作产生的错误信息 $mysqli->insert_id;//得到上一插入操作产生的 auto_increment 的值 $mysqli->affected_rows;//得到上一步操作产生的受影响的记录条数,affected_rows值为3种:1. 受影响的记录条数; 2. -1,代表SQL语句有问题 ;3. 0,代表没有受影响记录的条数 $mysqli_result=$mysqli->query($sql); $mysqli_result->num_rows; //获取结果集中所有记录,默认返回的是二维的(索引+索引的形式) $rows = $mysqli_result->fetch_all(MYSQLI_NUM|MYSQLI_ASSOC| MYSQLI_BOTH); //可选参数 MYSQLI_NUM、MYSQLI_ASSOC、 MYSQLI_BOTH $row=$mysqli_result->fetch_row(); //取得结果集中一条记录作为索引数组返回 $row=$mysqli_result->fetch_assoc(); //取得结果集中的一条记录作为关联数组返回 $row=$mysqli_result->fetch_array(); $row=$mysqli_result->fetch_array(MYSQLI_ASSOC); $row=$mysqli_result->fetch_object(); //移动结果集内部指针 $mysqli_result->data_seek(0); $row=$mysqli_result->fetch_assoc(); print_r($row); while($row = $mysqli_result->fetch_assoc()){ print_r($row); } '客户端的信息:'.$mysqli->client_info; '客户端的版本:'.$mysqli->client_version; '服务器端信息:'.$mysqli->server_info; '服务器版本:'.$mysqli->server_version;
本文为崔凯原创文章,转载无需和我联系,但请注明来自冷暖自知一抹茶ckhttp://www.cksite.cn