一,sql语句及各单元分离解释:
1.创建数据表:CREAT TABLE IF NOT EXISTS table_name("");
$sqlStr = "CREATE TABLE IF NOT EXISTS php_tb1( "
."php_id INT NOT NULL AUTO_INCREMENT, "
. "php_title VARCHAR(100) NOT NULL, "
."php_author VARCHAR(40) NOT NULL, "
."submission_date DATE, "
."PRIMARY KEY ( php_id ));";
2.删除数据表:DROP TABLE table_name;
$sql = "DROP TABLE php_tb1";
3.查询表中数据:SELECT xxx,yyy,zzz FROM table_name;
$sqlSecet = "SELECT php_id,php_title,php_author FROM php_tbl";
$retval = mysqli_query($content,$sqlSecet);
if($retval){
$strObj = mysqli_fetch_array($retval,MYSQLI_ASSOC);
echo "<ul>
<li>ID:$strObj[php_id]</li>
<li>title:$strObj[php_title]</li>
<li>Author:$strObj[php_author]</li>
</ul>
";
}else{
echo "查询数据表失败".mysqli_error($content);
}
4.改动表中数据:UPDATE table_name SET xxx = new,yyy = new,zzz = new WHERE php_id = 1//php_id是主键,在主键为1的数据行中修改xxx,yyy,zzz
$sql = "UPDATE php_tbl SET php_author = '张三',php_title = '亚洲'WHERE php_id = 1";
if($retObj = mysqli_query($content,$sql)){
echo "更新数据成功!";
}else{
echo "更新失败".mysqli_error($content);
}
二,下面是完整的封装函数API:
tips:将这个文件包含到需要使用的文件中,数据库的名字,地址,端口等修改成自己的,调用时写对函数名字,参数为正确的sql语句。查询数据表函数会返回给调用处一个查询内容的数组,所以前台调用时要用一个变量接收返回值。可以用var_dump()打印一下看看。数据库连接和断开连接分别在构造函数和析构函数,不用单独调用!
<?php header("content-type:text/html;charset=utf-8");
/*
* 操作数据库类
* */
class Mysql{
private $hostName = "localhost";
private $usrName = "root";
private $passWord = "root";
private $port = "3306";
private $dbname = "flyingman";
private $content;//连接句柄
private $retval;//查询数据库句柄
//连接数据库构造函数
public function __construct()
{
$this->content = mysqli_connect($this->hostName,$this->usrName,$this->passWord,$this->dbname,$this->port);
if (!$this->content){
echo "<h2>服务器有点小问题,错误码:0001</h2>";
die();
}
}
//创建数据表函数
public function creatTable($sqlStr){
if (!mysqli_query($this->content,$sqlStr)){
echo "<h2>服务器有点忙,请稍后再试,错误码:0002</h2> " ;
}
}
//插入数据表
public function insertTable($sqlStr){
if (!mysqli_query($this->content,$sqlStr)){
echo "<h2>服务器有点忙,请稍后再试,错误码:0003</h2>";
}
}
//更新数据表
public function updateTable($sqlStr){
if (!mysqli_query($this->content,$sqlStr)){
echo "<h2>服务器有点忙,请稍后再试,错误码:0004</h2>";
}
}
//查询数据表内容
public function selectTable($sqlStr){
$this->retval = mysqli_query($this->content,$sqlStr);
if($this->retval) {
///返回一个关联数组
return (mysqli_fetch_array($this->retval,MYSQLI_ASSOC));
}else{
echo "<h2>服务器有点忙,请稍后再试,错误码:0006</h2>";
}
}
//删除数据表
public function dropTable($sqlStr){
if (!mysqli_query($this->content,$sqlStr)){
echo "<h2>服务器有点忙,请稍后再试,错误码:0005</h2>";
}
}
//断开连接析构函数
public function __destruct()
{
mysqli_close($this->content);
}
}