利用mysqli 大略流程

设置PHP.ini 配置文件

extension=php_mysqli.dll

php预定义类comphpPHP 预界说类 Mysqli 常用代码聚集 Ruby

//创建连接

$conn = new mysqli($servername, $username, $password, $dbname);

//检测连接

if ($conn->connect_error)

{

die(\"大众Connection failed: \"大众 . $conn->connect_error);

}

//获取数据库返回值

if($stmt=$con->prepare(\"大众SELECT username,password FROM member WHERE username=? AND password=?\"大众))

{

$stmt->bind_param(\"大众ss\"大众,$username,$password);

$stmt->execute();

$stmt->bind_result($U,$P);

$HasData=false;

while($stmt->fetch())

{

$HasData=true;

echo \"大众username->\公众.$U.\公众 password->\"大众.$P.\"大众<br>\"大众;

}

}

//关闭连接

$conn->close();

//常用语法

INSERT INTO [表名] ([字段1],[字段2]) VALUES ([值1],[值2]);

SELECT FROM [表名] WHERE [表达式] ORDER BY [字段1],[ASC/DESC 升/降] [字段2],[ASC/DESC 升/降];

DELETE FROM [表名] WHERE [表达式];

UPDATE [表名] SET [字段1]=[值1],[字段2]=[值2] WHERE [表达式];

获取总行数:SELECT COUNT() FROM [表]

//中文乱码

//针对页面

header(\"大众Content-Type:text/html;charset=utf-8\"大众);

//针对数据库

mysqli->query(\公众SET CHARACTER SET 'utf8'\"大众);//读库

mysqli->query(\"大众SET NAMES 'utf8'\"大众);//写库

以下是一些实例

连接mysqli

建立MYSQL配置文件 config.ini.php

config.ini.php 内容如下:

<?php

$dbhost =\公众locallhost\公众;

$dbuser = \"大众hehehe\"大众;

$dbpwd = \"大众123456\"大众;

$dbname = \"大众Student\"大众;

$charName = \公众gbk2312\"大众; 设置查询字符集gbk,gbk2312,utf-8

?>

(用mysqli链接MYSQL数据库)

requery_once(\"大众config.ini.php\"大众);

$mysqliObj = new mysqli($dbhost,$dbuser,$dbpwd,$dbname);

if(mysqli_connect_errno()){

echo \公众连接失落败\"大众.mysqli_connect_error();

exit();

}

$mysqliObj->query(\公众set name $charName\"大众);

(其他操作)

查询

(单条查询)

$sql = \"大众drop table if exists user;\公众;

$mysqliObj->query($sql);

(多条查询)

$musqliObj->multip_query($sql)

返回实行$sql受影响的行数()

if($mysqliObj->query($sql))

echo $mysqliObj->affected_rows;

insert 插入时,返回插入的id (很有用)

$num = $mysqliObj->insert_id;

三种处理查询结果

$sql = \公众select from user\"大众;

$result = $mysqli->query($sql);

(1)fetch_row() 返回索引数组

fetch_row()

while(list($id,$name,$pwd)=$result->fetch_row()){

echo \"大众id: \"大众.$id.\"大众 name:\公众.$name.\"大众 pwd:\"大众.$pwd.\公众<br>\"大众;

}

(2)fetch_assoc() 返回关联数组

fetch_assoc()

while ($row = $result->fetch_assoc()){

echo \"大众id:\"大众.$row[\"大众userId\"大众].\"大众 name:\"大众.$row[\"大众userName\"大众].\"大众 pwd:\"大众.$row[\公众password\"大众].\"大众<br>\公众;

}

(3)fetch_object()返回工具

while($row = $result->fetch_object()){

echo \公众id:\"大众.$row->userId.\公众 name:\"大众.$row->uerName.\"大众 pwd:\"大众.$row->password.\公众<br>\公众;

}

关联数组,便是嵌套的数组 如:

<?php

$data = array(

array('name' => 'John Smith', 'home' => '555-555-5555',

'cell' => '666-555-5555', 'email' => 'john@myexample.com'),

array('name' => 'Jack Jones', 'home' => '777-555-5555',

'cell' => '888-555-5555', 'email' => 'jack@myexample.com'),

array('name' => 'Jane Munson', 'home' => '000-555-5555',

'cell' => '123456', 'email' => 'jane@myexample.com')

);

?>

利用 mysqli_stmt 类

mysql4.1版本开始供应一种预处理(prepared statement)的机制,它可以见全体命令向MYSQL做事器发送一次,往后只有参数变革,MYSQL只须要对命令做一次的剖析就够了,大大减少须要传输的数据量,还提高了命令的处理效率(注,在不须要连接时该当急速关闭close())

步骤:

1,预备SQL命令

$sql = \公众insert into user(name,pwd) values(?,?)\"大众;

$stms = $mysqli->prepare($sql);

2.绑天命据

$stms->bind_param('ss',$name,$pwd); (把稳'ss':其要与后面的变量对应($name,$pwd)

i 所有inerger类型

d double 或float 类型

s 所有其他类型包括字符串

b 二进制(blob,二进制字节串)

)

$name = \"大众huang\公众;

$pwd = \公众123456\公众;

3.实行

$stms->execute();

[4.在绑定实行另一组数据

$name = \"大众he\"大众;

$pwd = \公众666666\公众;

$stms->execute();

]

5.关闭

$stmt->close();

$mysqli->close();

(其他有用参数)

$num = $stmt->affected_rows; 受影响的行数

$id = $stmt->insert_id; 当是插入命令时,返回插入的行id(自动)

事务处理

在默认情形下,MYSQL因此自动提交(autocommit)模式运行,这就意味着所实行的每一个语句都将立即写入数据库中,但如果利用事务安全的表格类型,是不肯望自动提交的行为的

事务处理

当实行多条MYSQL命令时,当然希望当个中一条命令出错时,所有的命令都不实行,返回实行命令之前的状态

这就用到事务了

大略利用事务流程

1.写好SQL命令

$sql1 = \"大众insert user(name) values('huang','123456')\"大众;

$sql2 = \公众update account set number = number+1\"大众;

2.关闭MYSQL事务处理的自动提交模式

$mysqli->cutocommit(0);

3.试实行命令

$success = true;

$res1 = $mysqli->query($sql1);

if(!$res1 or $mysqli->affected_rows !=1){

$success = false;

}

$res2 = $mysqli->query($sql2);

if(!$res2 or $mysqli->affected_rows !=1){

$success = false;

}

4.查看实行情形,都成功实行,有失落败回滚初态

if($success ){

$mysqli->commit();

echo \"大众实行成功\公众;

}else{

$mysqli->rollback();

echo \"大众实行失落败\公众;

}

5.规复MYSQL事务处理的自动提交模式

$mysqli->cutocommit(1);

$mysqli->close();

mysql 目前只有InnDB和BDB两种数据包类型才支持事务

InnoDB最快

(创建InnDB类型表)

create table user(

id int(10) not null auto_increment,

name varchar(50) not null,

pwd varchar(50) not null,

primary key(id)

)type=InnoDB