PHP7原生MySQL数据库操作实现代码

php7中mysql的连接与使用与PHP5中大不相同

PHP5中mysql_connect()等函数大多被PHP7中类的成员函数所代替。PHP5中连接mysql是过程性的，而PHP7中强调了类的使用与面向对象的方法

$user = new mysqli();

//Connect to mysql

$user->connect("localhost", "root", "q721@Ms67", "Student");

mysql的连接被mysqli类所替代

//Search in mysql

$query = 'SELECT * FROM users';

//Use a variable to save result

$result = $user->query($query);

查询语句也变成了类的成员函数

连接到 MySQL服务器

mysqli_connect(host, username, password [,dbname] [,port]);

username：用户名。默认值是服务器进程所有者的用户名；

password：密码。

dbname：数据库名称。

port：MySQL服务器的端口号，默认为3306。

<?php

$mysqli = new mysqli("localhost", "root", "password", "testgame");

if(!$mysqli) {

echo"database error";

}else{

echo"php env successful";

}

$mysqli->close();

localhost 意思是本地主机，如果你是在自己电脑上的话，这项就不用改了

root是mysql的用户名，如果你是默认的没有修改，也不用管，直接copy

password 这事mysql的密码，如果你没有试着的话，直接写空的就行，""这样哦

代码二

<?php

$link = mysqli_connect(

'localhost', /

'root', /

'lucax_database'); /

if (!$link) {

printf("Can't connect to MySQL Server. Errorcode: %s ", mysqli_connect_error());

exit;

}else

echo '数据库连接上了！';

mysqli_close($link);

代码三

<?php

$servername = "localhost";

$username = "root";

$password = "root";

$dbname = "myDB";

// 创建连接

$conn = new mysqli($servername, $username, $password,$dbname);

// 检测连接

if ($conn->connect_error) {

die("连接失败: " . $conn->connect_error);

}

echo "连接成功";

$conn->close();

选择当前数据库

mysqli_select_db(mysqliLink, database)

mysqli_set_charset(mysqliLink, charset)

mysqli_query(mysqliLink , queryStr)

link是创建的活动的数据库连接；

mysqli_fetch_array ( mysqliResult [, resultType] )

mysqli_fetch_all(mysqliResult [, resultType ])

mysqli_num_rows(mysqliResult)

array mysqli_fetch_assoc(mysqliResult)

mysqli_affected_rows ( mysqliLink )

mysqli_free_result(mysqliResult)

mysqli_connect_error()

php7创建数据库：

方法一:

$sql = "CREATE DATABASE myDB3";

if (mysqli_query($conn, $sql)) {

echo "数据库创建成功";

} else {

echo "Error creating database: " . mysqli_error($conn);

}

方法二:

// 检测连接

if ($conn->connect_error) {

　　die("连接失败: " . $conn->connect_error);

}

echo "连接成功";

$sql = "CREATE DATABASE myDB2";

if ($conn->query($sql) === TRUE) {

　　echo "数据库创建成功";

} else {

　　echo "Error creating database: " . $conn->error;

}

创建表的方法：

方法一:

// 使用 sql 创建数据表

$sql = "CREATE TABLE MyGuests (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email VARCHAR(50),

reg_date TIMESTAMP

)";

if (mysqli_query($conn, $sql)) {

echo "数据表 MyGuests 创建成功";

} else {

echo "创建数据表错误: " . mysqli_error($conn);

}

方法二:

// 使用 sql 创建数据表

$sql = "CREATE TABLE MyGuests2 (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL,

email VARCHAR(50),

reg_date TIMESTAMP

)";

if ($conn->query($sql) === TRUE) {

echo "Table MyGuests created successfully";

} else {

echo "创建数据表错误: " . $conn->error;

}

插入数据的方法:

方法一:

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {

echo "新记录插入成功";

} else {

echo "Error: " . $sql . " " . mysqli_error($conn);

}

方法二:

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {

echo "新记录插入成功";

} else {

echo "Error: " . $sql . " " . $conn->error;

}

插入多条数据的方法:

方法一:

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('John', 'Doe', 'john@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('Mary', 'Moe', 'mary@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('Julie', 'Dooley', 'julie@example.com')";

if (mysqli_multi_query($conn, $sql)) {

echo "新记录插入成功";

} else {

echo "Error: " . $sql . " " . mysqli_error($conn);

}

方法二：

$sql = "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('22', 'Doe', 'john@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('22', 'Moe', 'mary@example.com');";

$sql .= "INSERT INTO MyGuests (firstname, lastname, email)

VALUES ('22', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {

echo "新记录插入成功";

} else {

echo "Error: " . $sql . " " . $conn->error;

}

查询数据库的方法：

方法一:

$sql = "SELECT id, firstname, lastname FROM MyGuests";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

// 输出数据

while($row = mysqli_fetch_assoc($result)) {

echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " ";

}

} else {

echo "0 结果";

}

方法二：

$sql = "SELECT id, firstname, lastname FROM MyGuests";

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

if ($result->num_rows > 0) {

// 输出数据

while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. " ";

}

} else {

echo "0 结果";

}

看到这里小编再为大家分享一个整理好的类库

<?php

/**

!defined('ROOTNAME') && exit('FORBIDDEN');

class db_mysql{

var $query_num = 0;

var $link;

function __construct($dbhost, $dbuser, $dbpw, $dbname) {

$this->connect($dbhost, $dbuser, $dbpw, $dbname);

}

function connect($dbhost, $dbuser, $dbpw, $dbname) {

// global $dbcharset;

if(!$this->link = @mysqli_connect($dbhost, $dbuser, $dbpw, $dbname)) {

$this->halt("Can not connect to MySQL server");

}

if($this->server_info() > '5.0'){

mysqli_query($this->link, "SET sql_mode=''");

}

if($dbname) {

if (!@mysqli_select_db($dbname, $this->link)){

$this->halt('Cannot use database '.$dbname);

}

}*/

}

function select_db($dbname) {

$this->dbname = $dbname;

if (!@mysqli_select_db($dbname, $this->link)){

$this->halt('Cannot use database '.$dbname);

}

function server_info() {

return mysqli_get_server_info($this->link);

}

function version() {

return mysqli_get_server_info($this->link);

}

function insert($tableName, $column = array())/

/ {

$columnName = "";

$columnValue = "";

foreach ($column as $key => $value) {

$columnName .= $key . ",";

$columnValue .= "'" . $value . "',";

}

$columnName = substr($columnName, 0, strlen($columnName) - 1);

$columnValue = substr($columnValue, 0, strlen($columnValue) - 1);

$sql = "INSERT INTO $tableName($columnName) VALUES($columnValue)";

$this->query($sql);

}

function update($tableName, $column = array(), $where = "") {

$updateValue = "";

foreach ($column as $key => $value) {

$updateValue .= $key . "='" . $value . "',";

}

$updateValue = substr($updateValue, 0, strlen($updateValue) - 1);

$sql = "UPDATE $tableName SET $updateValue";

$sql .= $where ? " WHERE $where" : null;

$this->query($sql);

}

function delete($tableName, $where = ""){

$sql = "DELETE FROM $tableName";

$sql .= $where ? " WHERE $where" : null;

$this->query($sql);

}

function select($tableName, $columnName = "*", $where = "") {

$sql = "SELECT " . $columnName . " FROM " . $tableName;

$sql .= $where ? " WHERE " . $where : null;

$this->query($sql);

}

function get_all($sql,$result_type = MYSQLI_ASSOC) {

$query = $this->query($sql);

$i = 0;

$rt = array();

while($row =& mysqli_fetch_array($query,$result_type)) {

$rt[$i]=$row;

$i ;

}

//$this->write_log("获取全部记录 ".$sql);

return $rt;

}

function fetchRow($query){

return mysqli_fetch_assoc($query);

}

function query($sql) {

//$this->write_log("查询 ".$sql);

mysqli_query($this->link,"set names utf8");

$query = mysqli_query($this->link,$sql);

//if(!$query) $this->halt('Query Error: ' . $sql);

return $query;

}

//获取第一个字段值

function getOne($sql, $limited = false){

if ($limited == true){

$sql = trim($sql . ' LIMIT 1');

}

$res = $this->query($sql);

if ($res !== false){

$row = mysqli_fetch_row($res);

if ($row !== false){

return $row[0];

}else{

return '';

}

}else{

return false;

}

function fetch_array($query, $result_type = MYSQLI_ASSOC) {

return mysqli_fetch_array($query, $result_type);

}

//输出记录

function fetch_first($sql) {

$res=$this->query($sql);

return $this->fetch_array($res,MYSQLI_ASSOC);

}

// 取得一条数据记录

function get_one($sql, $result_type = MYSQLI_ASSOC){

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

$record = $this->fetch_array($result, $result_type);

return $record;

}

function getRow($sql, $limited = false){

if ($limited == true){

$sql = trim($sql . 'LIMIT 1');

}

$res = $this->query($sql);

if ($res !== false){

return mysqli_fetch_assoc($res);

}else{

return false;

}

//取影响条数

function affected_rows() {

return mysqli_affected_rows($this->link);

}

//从结果集中取得一行作为枚举数组

function fetch_row($query) {

return mysqli_fetch_row($query);

}

// 结果条数

function num_rows($query) {

return mysqli_num_rows($query);

}

// 取字段总数

function num_fields($query) {

return mysqli_num_fields($query);

}

// 返回查询结果

function result($query, $row) {

$query = mysqli_result($query, $row);

return $query;

}

//释放结果集

function free_result($query) {

return mysqli_free_result($query);

}

//返回自增ID

function insert_id() {

return ($id = mysqli_insert_id($this->link)) >= 0 ? $id : $this->result($this->query("SELECT last_insert_id()"), 0);

}

function close() {

return mysqli_close($this->link);

}

function error() {

return (($this->link) ? mysqli_error($this->link) : '');

}

//返回错误信息

function errno() {

return intval(($this->link) ? mysqli_errno($this->link) : '');

}

function halt($msg = '') {

global $charset;

$msg = "<html>n<head>n";

$msg .= "<meta content="text/html; charset=$charset" http-equiv="Content-Type">n";

$msg .= "<style type="text/css">n";

$msg .= "body,p,pre {n";

$msg .= "font:12px Verdana;n";

$msg .= "}n";

$msg .= "</style>n";

$msg .= "</head>n";

$msg .= "<body bgcolor="#FFFFFF" text="#000000" link="#006699" vlink="#5493B4">n";

$msg .= "error: ".htmlspecialchars($this->error())."n ";

$msg .= "error number: ".$this->errno()."n ";

$msg .= "Date: ".date("Y-m-d @ H:i")."n ";

$msg .= "Script File: http://".$_SERVER['HTTP_HOST'].getenv("REQUEST_URI")."n ";

$msg .= "</body>n</html>";

echo $msg;

exit;

}

数据库 sqlserver 云数据库SQLServer sql

0 人点赞