PHP基础----MySQL数据库操作

<?php

/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
  header("Content-Type: text/html; charset=utf-8");  //设置编码
  header( "Expires: -1" );    //立即过期
$dbhost="127.0.0.1:3306";
$dbuser="root";
$dbpwd="";
$dbname="test";
$charset="UTF8";   //UTF8不是UTF-8


//连接mysql数据库
$mysqliObj = new mysqli($dbhost,$dbuser,$dbpwd,$dbname);
if($mysqliObj->connect_error){
 die('Error : ('. $mysqliObj->connect_errno .') '. $mysqliObj->connect_error);
}
//指定编码
$mysqliObj->query("set names $charset");



/**
 * 查询操作,处理结果方式fetch_row()   fetch_assoc()  fetch_object() fetch_array()
 */
$query_result=$mysqliObj->query("select id,username,address from user");
echo "查询结果总行数： ".$query_result->num_rows."<br>";
//查询结果处理1  fetch_row()  返回索引数组
//echo "fetch_row()处理查询结果<br>";
//print "<table border='0'>";
// while(list($id,$username,$address)=$query_result->fetch_row()){
//     print "<tr>";
//     print "<td>".$id."</td>";
//     print "<td>".$username."</td>";
//     print "<td>".$address."</td>";
//     print "</tr>";
// }
// print "</table>";
 

 //查询结果处理2  fetch_assoc()    返回关联数组
// echo "fetch_assoc()处理查询结果<br>";
// print "<table border='0'>";
// while($row=$query_result->fetch_assoc()){
//     print "<tr>";
//     print "<td>".$row["id"]."</td>";
//     print "<td>".$row["username"]."</td>";
//     print "<td>".$row["address"]."</td>";
//     print "</tr>";
// }
// print "</table>";

 
 //查询结果处理3  fetch_object()  返回对象
 echo "fetch_object()处理查询结果<br>";
 print "<table border='0'>";

 while($row=$query_result->fetch_object()){
     print "<tr>";
     print "<td>".$row->id."</td>";
     print "<td>".$row->username."</td>";
     print "<td>".$row->address."</td>";
     print "</tr>";
 }
 print "</table>";
 
 //释放资源
 $query_result->free();
 
 
 /**
  * 预处理
  */
 $id="1";
 //create a prepared statement
 $statement=$mysqliObj->prepare("select id,username,address from user where id=?");
 //bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
 $statement->bind_param("s", $id);
 //execute query
 $statement->execute();
 //bind result variables
$statement->bind_result($id, $username, $address);
$result=$statement->get_result();
 echo "<br>预处理查询结果".$result->num_rows."<br>";
 print "<table border='0'>";

 while($row=$result->fetch_object()){
     print "<tr>";
     print "<td>".$row->id."</td>";
     print "<td>".$row->username."</td>";
     print "<td>".$row->address."</td>";
     print "</tr>";
 }
 print "</table>";
//
$statement->close();
 
echo "<br>";
/**
 * 插入数据
 */
$add_id="".$mysqliObj->real_escape_string('8')."";
$add_username="".$mysqliObj->real_escape_string('abc')."";
$add_address="".$mysqliObj->real_escape_string('住址8')."";
//普通插入,需要单引号
$insert_row=$mysqliObj->query("INSERT INTO user(id,username,address) VALUES ('$add_id','$add_username','$add_address')");
if($insert_row){
    echo "Success!".$mysqliObj->insert_id;
}else{
    echo "Error:(".$mysqliObj->errno.")".$mysqliObj->error;
}

/**
 * 插入（预处理方式）
 */
//$add_id2="".$mysqliObj->real_escape_string('7')."";
//$add_username2="".$mysqliObj->real_escape_string('abc')."";
//$add_address2="".$mysqliObj->real_escape_string('住址7')."";
//$insert_sql_pre="INSERT INTO user(id,username,address) VALUES (?,?,?)";
//$statement_insert=$mysqliObj->prepare($insert_sql_pre);
//$statement_insert->bind_param("sss", $add_id2,$add_username2,$add_address2);
//if($statement_insert->execute()){
//    echo "Success! Prepare insert.";
//}else{
//       echo "Error:(".$mysqliObj->errno.")".$mysqliObj->error;
//}
//$statement_insert->close();

/**
 * 更新和删除操作与保存操作类似
 */

 //关闭数据库连接
 $mysqliObj->close();