php mysqli扩展库的使用
数据库结构
1.连接数据库
$hostname = 'localhost'; //主机名
$dbname = 'yian'; //数据库名
$username = 'root'; //用户名
$psw = 'root'; //用户密码
$charset = 'utf_8'; //mysql编码
$mysqli =new mysqli($hostname,$username,$psw,$dbname);
name
$mysqli->set_charset($charset);
$selectdb = $mysqli->select_db($dbname);
$sql = "select,
agefrom
test01";
test01
$insertsql = "insert into(
name,
tel) values('test',999)";
//执行一条sql语句
$res = $mysqli->query($sql);
$res = $mysqli->query($insertsql);
//返回插入操作后的id
$num = $mysqli->insert_id;
echo $num;exit;
/*
* 输出
* 6
* */
//返回受影响的条数
$num = $mysqli->affected_rows;
echo $num;exit;
/*
* 输出
* 5
//返回索引数组
$out = $res->fetch_row();
/*输出:
查询出第一条记录
Array
(
[0] => A
[1] => 21
)*/
while(list($tel,$age)=$res->fetch_row()){
echo $tel.' age is '.$age."\n";
}
/*
* 输出:
A age is 21
B age is 15
C age is 21
D age is 14
E age is 26
* */
/返回关联数组
$out = $res->fetch_assoc();
print_r($out);exit;
/*
* 输出
* 输出第一条关联数组
* Array
(
[name] => A
[age] => 21
)
*/
while($row = $res->fetch_assoc()){
echo $row['name'].' age is '.$row['age']."\n";
}
/*
* 输出
A age is 21
B age is 15
C age is 21
D age is 14
E age is 26
* */
`//返回对象
out=
res->fetch_object();
var_dump($out);exit;
/*
* 输出
object(stdClass)#3 (2) {
[“name”]=>
string(1) “A”
[“age”]=>
string(2) “21”
}
*/
while(
row=
res->fetch_object()){
echo
row−>name.′ageis′.
row->age.”\n”;
}
/*
* 输出:
A age is 21
B age is 15
C age is 21
D age is 14
E age is 26
*/
//使用 mysqli_stmt 类
/*
* mysql4.1版本开始提供一种预处理(prepared statement)的机制,它可以见整个命令向MYSQL服务器发送一次,
* 以后只有参数变化,MYSQL只需要对命令做一次的分析就够了,
* 大大减少需要传输的数据量,还提高了命令的处理效率(注,在不需要连接时应该立刻关闭close())
* 使用预准备语句可提高重复使用语句的性能,
* 在PHP中,使用prepare()方法来进行预准备语句查询,
* 使用execute()方法来执行预准备语句。PHP有两种预准备语句:一种是绑定结果,另一种是绑定参数。
* */
//1预备sql命令
//$sql = “select ?,?,?, form test01
where gender
=1 “;
$sql = “select ?,?,? from test01
where gender
=1 “;
//$sql = “select name
,age
,tel
from test01
where gender
=? “;
stmt=
mysqli->prepare(
sql);
name = ‘name’;
age=‘age′;
tel = ‘tel’;
//2绑定参数
stmt−>bindparam(′sss′,
name,
age,
tel);
//
stmt−>bindparam(′s′,
gender);
//$gender = 1;
//执行
out=
stmt->execute();
//2绑定结果
stmt−>bindresult(
sname,
sage,
stel);
while(
stmt−>fetch())echo$sname.′ageis′.$sage.′telis‘.$stel.”\n”;//关闭预处理的sql
stmt->close();
//关闭数据库
$mysqli->close();`