PHP高级 MYSQLi 第37天

2012-11-16 14:39:04 0  category: 第二阶段php

数据库连接


一, 连接数据库

二, 判断错误

三, 设置字符集

四, 选择数据库

五, 准备SQL语句

六, 发送

七, 判断是否有错或者是否受影响行,如果是查询数据,则在后返回结果集

八, 关闭数据库连接





开启mysqli模块:

将其分为三个类:mysqli  mysqli预处理类   mysqli的结果集处理类



错误判断有几个:

Mysqli_connect_errno()

Mysqli_connect_error()

Mysqli_errno()

Mysqli_error()




遍历数据的常用方法有四个:

对象->fetch_assoc()

对象->fetch_array()

对象->fetch_object()

对象->fetch_row()



$result->fetch_array(MYSQLI_NUM)

对象->fetch_row()

$result->fetch_array(MYSQLI_BOTH) 就是自己,默认值就为它,可以不传

$result->fetch_array(MYSQLI_ASSOC)  

对象->fetch_assoc();




预处理语句分为四步:

1, 初使化预处理对象并准备SQL语句

2, 绑定参数

3, 参数赋值

4, 执行





stmt.php


<?php

$conn=new mysqli('localhost','root','phpha','mo');

if(mysqli_connect_errno()){
echo '连接失败'.mysqli_connect_error();
exit;
}

$conn->set_charset('utf8');

//预处理,并不是先准备SQL语句,而是需要先初使化预处理对象

//查,怎么办啊?

$stmt=$conn->prepare('select * from 3ser where id<?');

$stmt->bind_param('i',$id);

$id=10;

$stmt->execute();

$stmt->bind_result($id,$username,$password,$createtime);

while($stmt->fetch()){
echo $id.'---------'.$username.'------'.$password.'-------'.$createtime.'<br />';
}

/*
$stmt=$conn->prepare('delete from 37demo_user where id in(?)');

$stmt->bind_param('i',$id);

for($i=16;$i<20;$i++){

$id=$i;

$stmt->execute();
}

$stmt=$conn->prepare('update ser set username=? where id=?');

$stmt->bind_param('si',$username,$id);

$username='谢谢啊';

$id=19;

$stmt->execute();

$username='你啊';

$id=18;

$stmt->execute();

$stmt=$conn->prepare('insert into 37demo_user(username,password,createtime) values(?,?,?)');

$stmt->bind_param('ssi',$username,$password,$time);

$username='了';

$password='有?';

$time=time();

$stmt->execute();

echo '受影响行为'.$stmt->affected_rows.'<br>';

echo '插入的ID为'.$stmt->insert_id.'<br>';

$username='冬天的水';

$password='找个';

$time=strtotime('-4 day');

$stmt->execute();
echo '受影响行为'.$stmt->affected_rows.'<br>';

echo '插入的ID为'.$stmt->insert_id.'<br>';
*/
?>

form.html


<form action="mysqli.php" method="post">

<input type="text" name="username" />

<input type="password" name="password" />

<input type="submit" value="提交" />

</form>



mysqli.php


<?php
//连接

$conn=new mysqli('localhost','root','php_haha','mo');

//错误判断,你在php5.3.x 可以直接用对象的形式来判断错误,但是你写的代码,不能向下兼容。5.2.x,无法用对象的形式来判断错误

if(mysqli_connect_errno()){
echo 'mysql连接失败,错误原因为'.mysqli_connect_error();

exit;
}

$conn->set_charset('utf8');


//如果没有选库,或者需要切换到其它库,
//$conn->select_db('3bssbs');

//无结果集的
//有结果集的


$username=trim($_POST['username']);

$password=md5(md5(trim($_POST['password'])).'云');

$time=$_SERVER['REQUEST_TIME'];


$sql="insert into 37demo_user(username,password,createtime) values('{$username}','{$password}','{$time}')";


//$sql="delete from ser where id>1000";


//$sql="update 3r set username='柔',password='is me' where id=10";
$result=$conn->query($sql);

if($result&&$conn->affected_rows){

echo '操作成功';

echo '插入的这个ID为'.$conn->insert_id;
}else{
echo '操作失败';
}
$conn->close();
?>


index.php


<?php

$conn=new mysqli('localhost','root','php_ha','xmo');


if(mysqli_connect_errno()){
echo '连接失败'.mysqli_connect_error();
exit;
}

$conn->set_charset('utf8');

$sql="select * from 3o_user order by id desc";

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

$t=$conn->server_version;

var_dump($t);

while($field=$result->fetch_field()){
echo '字段名'.$field->name.'<br />';

}
if($result&&$conn->affected_rows){
echo '<table width="800" border="1">';
while($row=$result->fetch_object()){
echo '<tr>';
echo '<td>'.$row->id.'</td>';
echo '<td>'.$row->username.'</td>';
echo '<td>'.$row->createtime.'</td>';
echo '</tr>';
}
echo '</table>';
}else{
echo '没有结果';
}
?>


mysqli 事务处理



<form action="sw.php" method="post">

转给谁:<input type="text" name="who" /><br />

帐号:<input type="text" name="whoaccount" /><br>

转出帐号:<input type="text" name="myaccount" /><br>

金额:<input type="text" name="je" />

<input type="submit" value="转帐">

</form>


sw.php


<?php
$conn=new mysqli('localhost','root','php_haha','3mo');

$conn->set_charset('utf8');



$conn->autocommit(false);

$flag=true;

$who=$_POST['who'];
$whoaccount=$_POST['whoaccount'];

$je=$_POST['je'];

$myaccount=$_POST['myaccount'];


$sql="update ye set je=je-$je where id=$myaccount";

echo $sql.'<br>';

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

if(!$result|!$conn->affected_rows){
$flag=false;
}

$sql="update ye set je=je+$je where id=$whoaccount and name='{$who}'";

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

if($result&&$conn->affected_rows&&$flag){
$conn->commit();

echo '转帐成功';
}else{
$conn->rollback();

echo '转帐失败';
}
?>



mysqli.php


<?php
$conn=new mysqli('localhost','root','php_aha','3mo');

$conn->set_charset('utf8');

$conn->autocommit(false);

$flag=true;

$sql="update ye set je=je-20000000 where id=1";

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

if(!$result|!$conn->affected_rows){

$flag=false;
}

$sql="update ye set je=je+20000000 where id=2";

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

if($flag&&$conn->affected_rows){
$conn->commit();

echo '转帐成功';
}else{
$conn->rollback();

echo '转帐失败';
}
?>