mysql> create table user(
-> id int primary key auto_increment,
-> name varchar(32) not null,
-> password varchar(64) not null,
-> email varchar(128) not null,
-> age tinyint unsigned not null
-> );
mysql> insert into user (name,password,email,age) values("gjp",md5(123456),'gjp@
sohu.com',24);
mysql> insert into user (name,password,email,age) values("郭盧",md5(123456),'郭
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149535xxQw.png"></a>
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149535OzRU.png"></a>
Mysql用戶端的限制,隻能接受gbk碼,utf8不支援,資料庫是支援的
<?php
$conn=mysql_connect("localhost","root","123456");
if(!$conn)
{
die("出錯了".mysql_errno());
}
mysql_select_db("test",$conn) or die(mysql_errno());
mysql_query("set names utf8");
$sql="insert into user (name,password,email,age) values('zhangsan',md5('123'),'[email protected]',34)";
$res=mysql_query($sql,$conn);
if(!$res)
echo "操作失敗";
if (mysql_affected_rows($conn)>0)
echo "操作成功!";
}else{
echo "沒有受影響的行數!";
mysql_close($conn); //要不要無所謂
?>
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_13841495368IVF.png"></a>
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_13841495367rJp.png"></a>
Sql語句換成删除:
$sql="delete from user where id=4";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149537pdwt.png"></a>
$sql="update user set age=26 where name='lzw'";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149538jn4B.png"></a>
将上面的檔案,封裝成類,提高複用性!
兩個檔案:
index.php
1. <?php
2. require_once 'Sqltool.php';
3. $sql="insert into user(name,password,email,age) values('lisi',md5('123'),'[email protected]',36)";
4. $sqlTool=new Sqltool();
5. $res=$sqlTool->execute_dml($sql);
6. if($res==0){
7. echo "失敗";
8. }else if($res==1){
9. echo "success";
10. }else if($res==2){
11. echo "沒有受影響的行數";
12. }
13. ?>
Sqltool.php
class Sqltool
private $conn;
private $host="localhost";
private $user="root";
private $password="123456";
private $db="test";
function Sqltool()
{
$this->conn=mysql_connect($this->host,$this->user,$this->password);
if(!$this->conn)
die("fail".mysql_error());
mysql_select_db($this->db,$this->conn);
mysql_query("set names utf8");
}
//dql 針對select
public function execute_dql($sql)
$res=mysql_query($sql,$this->conn)or die(mysql_error());
return $res;
// dml語句是針對update delete insert 指令,傳回值為true false
public function execute_dml($sql)
echo $sql;
$b=mysql_query($sql,$this->conn);
if(!$b)
{
return 0;
}else {
if(mysql_affected_rows($this->conn)>0)
return 1;
}else{
return 2;
}
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149539ZbdB.png"></a>
指令改為$sql="delete from user where id=3";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149540cXwl.png"></a>
//DQL語句擷取的是結果集,執行這的時候,要将dml語句先注釋掉
$sql="select * from user";
$sqlTool=new Sqltool();
$res=$sqlTool->execute_dql($sql);
while($row=mysql_fetch_row($res))
foreach ($row as $key=>$val)
echo "--$val";
echo "<br/>";
mysql_free_result($res);
執行結果如下:
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149542Y0nd.png"></a>
MYSQli的講解2!
在php.ini 中開啟
extension=php_mysqli.dll
例1:使用面向對象的方式
//header("Content-type: text/html; charset=utf-8");
//1.建立mysql對象
$msi=new mysqli("localhost","root","123456","test");
//驗證是否ok
if($msi->cononnect_error){
die("連接配接失敗".$msi->connect_error);
}else {
echo "連接配接ok"."</br>";
//2.操作資料庫(發送sql指令)
$sql="select * from user ";
$res=$msi->query($sql);
/* echo "</br>";
print "<pre>";
var_dump($res);
print"</pre>"; */
//3.處理結果
while($row=$res->fetch_row()){
foreach ($row as $key=>$val){
//4.關閉資源
//釋放記憶體;
$res->free();
//關閉連結
$msi->close();
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149543B6mU.png"></a>
由于上面少了這個$msi->query("set names utf8");是以出現漢字的亂碼
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_13841495440Anq.png"></a>
正常了!
例2:用面向過程的方法
header("Content-type: text/html; charset=utf-8");
//1.得到mysqli連接配接
$msi=mysqli_connect("localhost","root","123456","test");
if(!$msi){
die("連接配接失敗".mysqli_connect_errno($msi));
//2.向資料庫發送sql語句(ddl dml dql)
$res=mysqli_query($msi,$sql);
//var_dump($res);
//3.處理得到的結果
//循環取出結果集
while($row=mysqli_fetch_row($res)){
foreach($row as $key=>$val){
mysqli_free_result($res);
mysqli_close($msi);
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149545ca8e.png"></a>
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149547rM9Y.png"></a>
如何區分這幾個?
通過上面的程式修改示範:
var_dump($row);
print "</pre>";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149549DAkb.png"></a>
上面是以下标
while($row=mysqli_fetch_assoc($res)){
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149551H054.png"></a>
上面是以字段名,如id
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149552flYb.png"></a>
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_13841495538BM5.png"></a>
例3:
$msi->query("set names utf8");
$sql="insert into user (name,password,email,age) values('李想',md5('aaaa'),'[email protected]',18)";
if(!$res){
echo "操作失敗".$msi->error;
//影響多少行記錄
if($msi->affected_rows>0){
echo "執行ok";
echo "沒有受影響的行數";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149553IpQr.png"></a>
執行了2次,添加了2條
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149555i5dt.png"></a>
把sql語句改為:(不存在的id)
$sql="update user set name='haha' where id=11";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149555CpCK.png"></a>
$sql="update user set name='haha' where id=9";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149557sqEV.png"></a>
$sql="delete from user where name='haha'";
增删改都使用了,這裡我們來封裝成工具類
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_13841495579HxM.png"></a>
Mysqli封裝為工具類sqlHelper:
class sqlHelper{
private $mysqli;
private static $host="localhost";
private static $user="root";
private static $pwd="123456";
private static $db="test";
//下面這個函數__construct()其實是兩個下劃線,下面由于我寫了一個,是以無法自動調用
該函數,需要手動調,如果寫成兩個_,建立對象時,則會自動調!
public function _construct(){
$this->mysqli=new mysqli(self::$host,self::$user,self::$pwd,self::$db);
if($this->mysqli->connect_error){
die("連接配接失敗".$this->mysqli->connect_error);
echo "success"; //為了調試
//設定通路資料庫的字元集,保證php是以utf8的方式來操作我們的mysql資料庫
$this->mysqli->query("set names utf8");
public function execute_dql($sql){
$res=$this->mysqli->query($sql) or die("操作dql".$this->mysqli->error);
return $res;
public function execute_dml($sql){
echo $sql; //為了調試
$res=$this->mysqli->query($sql) or die("操作
dql".$this->mysqli->error);
echo $res;//為了調試
if($this->mysqli->affected_rows>0){
使用工具類,實作其功能:
require_once 'sqlHelper.php';
//header("Content-type: text/html;charset=utf-8");
//建立sqlHelper對象
$sqlhelper=new sqlHelper();
$sql="insert into user(name,password,email,age) values('盧偉da',md5('aaaa'),'[email protected]','8')";
$sqlhelper->_construct();
$res=$sqlhelper->execute_dml($sql);
if($res==0){
echo "失敗";
if($res==1){
echo "恭喜,成功!";
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149558j4Cu.png"></a>
操作資料庫成功如下:
<a href="http://guojiping.blog.51cto.com/attachment/201311/11/5635432_1384149559OUDz.png"></a>
本文轉自 gjp0731 51CTO博http://blog.51cto.com/guojiping/1323187客,原文連結: