<?php
$mysqli=@new mysqli("localhost", "root", "123456", "xsphpdb");
if(mysqli_connect_errno()){
echo "连接数据库失败:".mysqli_connect_error();
$mysqli=null;
exit;
}
echo $mysqli->character_set_name()."<br>";
echo $mysqli->get_client_info()."<br>";
echo $mysqli->host_info."<br>";
echo $mysqli->server_info."<br>";
echo $mysqli->server_version."<br>";
$mysqli->close()
--------------------------------------------------------------------------------------------------
<?php
$mysqli=@new mysqli("localhost", "root", "123456", "xsphpdb");
if(mysqli_connect_errno()){
echo "连接数据库失败:".mysqli_connect_error();
$mysqli=null;
exit;
}
//select语句(结果集), 非select语句,会影响行数
$sql="insert into shops(name,price,num,desn) values('hello','34.56','22','good123')";
$result=$mysqli->query($sql);
if(!$result){
echo "SQL语句有误<br>";
echo "ERROR:".$mysqli->errno."|".$mysqli->error;
exit;
}
echo $mysqli->affected_rows;
$mysqli->close();
--------------------------------------------------------------------------------------------------
<?php
$mysqli=@new mysqli("localhost", "root", "123456", "xsphpdb");
if(mysqli_connect_errno()){
echo "连接数据库失败:".mysqli_connect_error();
$mysqli=null;
exit;
}
//select语句(结果集), 非select语句,会影响行数
$sql="insert into shops(name,price,num,desn) values('hello','34.56','22','good123')";
//$sql="delete from shops where id > '80'";
$result=$mysqli->query($sql);
if(!$result){
echo "SQL语句有误<br>";
echo "ERROR:".$mysqli->errno."|".$mysqli->error;
exit;
}
if($mysqli->affected_rows > 0){
echo "有行数被影响<br>";
}
echo "最后自动增长的ID:".$mysqli->insert_id;
$mysqli->close();
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
if(mysqli_connect_errno()){
echo "错误:".mysqli_connect_error();
exit;
}
//执行select语句,返回来的就是结果集(对象)
$sql="select id,name from shops where id <50";
$result=$mysqli->query($sql);
$rows=$result->num_rows;
$cols=$result->field_count;
echo "表中{$rows}行,{$cols}列<br>";
$mysqli->close();
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
if(mysqli_connect_errno()){
echo "错误:".mysqli_connect_error();
exit;
}
//执行select语句,返回来的就是结果集(对象)
$sql="select id cid, name shopname, price shopprice, num shopnum, desn shopdesn from shops";
$result=$mysqli->query($sql);
$rows=$result->num_rows;
$cols=$result->field_count;
echo "表中{$rows}行,{$cols}列<br>";
//记录信息
echo '<table border=1 align="center" width=900>';
echo '<tr>';
// $result->field_seek(2);
while($field=$result->fetch_field()){
echo '<th>'.$result->current_field.'_['.$field->orgname.']'.$field->name.'('.$field->max_length.')</th>';
}
echo '</tr>';
// $result->data_seek(50);
while($row=$result->fetch_assoc()){
echo '<tr>';
foreach($row as $col){
echo '<td>'.$col.'</td>';
}
echo '</tr>';
}
echo '</table>';
$result->free();
$mysqli->close();
--------------------------------------------------------------------------------------------------
mysqli分页类
<?php
class Page {
private $total; //数据表中总记录数
private $listRows; //每页显示行数
private $limit;
private $uri;
private $pageNum; //页数
private $config=array('header'=>"个记录", "prev"=>"上一页", "next"=>"下一页", "first"=>"首 页", "last"=>"尾 页");
private $listNum=8;
public function __construct($total, $listRows=10, $pa=""){
$this->total=$total;
$this->listRows=$listRows;
$this->uri=$this->getUri($pa);
$this->page=!empty($_GET["page"]) ? $_GET["page"] : 1;
$this->pageNum=ceil($this->total/$this->listRows);
$this->limit=$this->setLimit();
}
private function setLimit(){
return "Limit ".($this->page-1)*$this->listRows.", {$this->listRows}";
}
private function getUri($pa){
$url=$_SERVER["REQUEST_URI"].(strpos($_SERVER["REQUEST_URI"], '?')?'':"?").$pa;
echo $url;
$parse=parse_url($url);
if(isset($parse["query"])){
parse_str($parse['query'],$params);
unset($params["page"]);
$url=$parse['path'].'?'.http_build_query($params);
}
return $url;
}
private function __get($args){
if($args=="limit")
return $this->limit;
else
return null;
}
private function start(){
if($this->total==0)
return 0;
else
return ($this->page-1)*$this->listRows+1;
}
private function end(){
return min($this->page*$this->listRows,$this->total);
}
private function first(){
if($this->page==1)
$html.='';
else
$html.=" <a href='{$this->uri}&page=1'>{$this->config["first"]}</a> ";
return $html;
}
private function prev(){
if($this->page==1)
$html.='';
else
$html.=" <a href='{$this->uri}&page=".($this->page-1)."'>{$this->config["prev"]}</a> ";
return $html;
}
private function pageList(){
$linkPage="";
$inum=floor($this->listNum/2);
for($i=$inum; $i>=1; $i--){
$page=$this->page-$i;
if($page<1)
continue;
$linkPage.=" <a href='{$this->uri}&page={$page}'>{$page}</a> ";
}
$linkPage.=" {$this->page} ";
for($i=1; $i<=$inum; $i++){
$page=$this->page+$i;
if($page<=$this->pageNum)
$linkPage.=" <a href='{$this->uri}&page={$page}'>{$page}</a> ";
else
break;
}
return $linkPage;
}
private function next(){
if($this->page==$this->pageNum)
$html.='';
else
$html.=" <a href='{$this->uri}&page=".($this->page+1)."'>{$this->config["next"]}</a> ";
return $html;
}
private function last(){
if($this->page==$this->pageNum)
$html.='';
else
$html.=" <a href='{$this->uri}&page=".($this->pageNum)."'>{$this->config["last"]}</a> ";
return $html;
}
private function goPage(){
return ' <input type="text" οnkeydοwn="javascript:if(event.keyCode==13){var page=(this.value>'.$this->pageNum.')?'.$this->pageNum.':this.value;location=\''.$this->uri.'&page=\'+page+\'\'}" value="'.$this->page.'" style="width:25px"><input type="button" value="GO" οnclick="javascript:var page=(this.previousSibling.value>'.$this->pageNum.')?'.$this->pageNum.':this.previousSibling.value;location=\''.$this->uri.'&page=\'+page+\'\'"> ';
}
function fpage($display=array(0,1,2,3,4,5,6,7,8)){
$html[0]=" 共有<b>{$this->total}</b>{$this->config["header"]} ";
$html[1]=" 每页显示<b>".($this->end()-$this->start()+1)."</b>条,本页<b>{$this->start()}-{$this->end()}</b>条 ";
$html[2]=" <b>{$this->page}/{$this->pageNum}</b>页 ";
$html[3]=$this->first();
$html[4]=$this->prev();
$html[5]=$this->pageList();
$html[6]=$this->next();
$html[7]=$this->last();
$html[8]=$this->goPage();
$fpage='';
foreach($display as $index){
$fpage.=$html[$index];
}
return $fpage;
}
}
;
--------------------------------------------------------------------------------------------------
mysqli扩展完成事务处理和一次执行多条SQL语句
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
//没有结果集insert update delete
$sqls="insert into shops(name, price, num,desn) values('XSPHP', '79', '100000', 'very good for PHP');";
$sqls.="update shops set name='LAMP' where id > 40;";
$sqls.="delete from shops where id < 30";
echo $sqls."<br>";
if($mysqli->multi_query($sqls)){
echo "多条语句执行成功!<br>";
echo "最后插入的ID:".$mysqli->insert_id."<br>";
// echo "影响的行数为:".$mysqli->affected_rows;
}else{
echo "ERROR:".$mysqli->errno."--".$mysqli->error;
}
$mysqli->close()
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
//有结果集多个select语句
$sqls="select current_user();";
$sqls.="desc shops;";
$sqls.="select * from shops;";
$sqls.="select current_date()";
if($mysqli->multi_query($sqls)){
echo "执行成功!<br>";
do{
$result=$mysqli->store_result();
echo '<table align="center" border="1" width='.(100*$result->field_count).'>';
echo '<tr>';
while($field=$result->fetch_field()){
echo '<th>'.$field->name.'</th>';
}
echo '</tr>';
while($row=$result->fetch_assoc()){
echo '<tr>';
foreach($row as $col){
echo '<td>'.$col.'</td>';
}
echo '</tr>';
}
echo '</table>';
if($mysqli->more_results()){
echo '<p>--<p>--<p>';
}
}while($mysqli->next_result());
}else{
echo "ERROR:".$mysqli->errno."--".$mysqli->error;
}
$mysqli->close();
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
//事务处理
$mysqli->autocommit(0);
$error=true;
$price=50;
$sql="update zh set ye=ye-{$price} where name='zhangsan'";
$result=$mysqli->query($sql);
if(!$result){
$error=false;
echo "从张三转出失败<br>";
}else{
if($mysqli->affected_rows==0){
$error=false;
echo "张三的钱没有变化";
}else{
echo "从张三账号中转出成功!<br>";
}
}
$sql="update zh set ye=ye+{$price} where name='lisi1'";
$result=$mysqli->query($sql);
if(!$result){
$error=false;
echo "从李四转入失败<br>";
}else{
if($mysqli->affected_rows==0){
$error=false;
echo "李四的钱没有变化";
}else{
echo "向李四账号中转入成功!<br>";
}
}
if($error){
echo "转账成功!";
$mysqli->commit();
}else{
echo "转账失败!";
$mysqli->rollback();
}
$mysqli->autocommit(1);
$mysqli->close();
--------------------------------------------------------------------------------------------------
使用PHP的mysqli扩展中预处理语句
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
//准备好一条语句放到服务器中,插入语句
$sql="update shops set name=?, price=?, num=?, desn=? where id=?";
$stmt=$mysqli->prepare($sql);
//给占位符号每个?号传值(绑定参数) i d s b
$stmt->bind_param("sdisi", $name, $price, $num, $desn,$id);
$name="zhangsan1";
$price=56.781;
$num=661;
$desn="hello good1";
$id=100;
//执行
$stmt->execute();
$name="lisi1";
$price=56.781;
$num=661;
$desn="aaaaaaaa1";
$id=101;
//执行
$stmt->execute();
$name="wangwu1";
$price=56.781;
$num=961;
$desn="bbbbbbbbb1";
$id=102;
//执行
$stmt->execute();
$name="zhaoliu111";
$price=56.7811;
$num=6118;
$desn="cccccccccccc111";
$id=103;
//执行
$stmt->execute();
echo "最后ID".$stmt->insert_id."<br>";
echo "影响了".$stmt->affected_rows."行<br>";
$stmt->close();
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb");
$stmt=$mysqli->prepare("select id, name, price, num, desn from shops where id>?");
$stmt->bind_param("i", $id);
$stmt->bind_result($id, $name, $price, $num, $desn);
$id=99;
$stmt->execute();
$stmt->store_result(); //一次性将结果都取过来 重要
//字段信息
$result=$stmt->result_metadata();
while($field=$result->fetch_field()){
echo $field->name."--";
}
echo '<br>';
//记录信息
//$stmt->data_seek(2);
while($stmt->fetch()){
echo "$id--$name---$price---$num---$desn <br>";
}
echo "记录总数:".$stmt->num_rows;
$stmt->free_result();
$stmt->close();
--------------------------------------------------------------------------------------------------
视图使用
--------------------------------------------------------------------------------------------------
<?php
$mysqli=new mysqli("localhost", "root", "123456", "xsphpdb2");
$sql="select * from stuc order by phpc";
$result=$mysqli->query($sql);
echo '<table align="center" border="1" width="800">';
echo '<caption><h1>mysql view demo</h1></caption>';
while($row=$result->fetch_assoc()){
echo '<tr>';
foreach($row as $col){
echo '<td>'.$col.'</td>';
}
echo '</tr>';
}
echo '</table>';
$mysqli->close();
--------------------------------------------------------------------------------------------------