使用 PHP OOP 概念连接到 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8474876/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-26 04:48:59 来源:igfitidea点击:
Connect to MySQL database using PHP OOP concept
提问by 125369
I'm writing a class and handful of functions to connect to the database and retrieve the information from the tables. I went through previous posts having similar titles, but most of them have written using mysql functions and I am using mysqli functions.
我正在编写一个类和一些函数来连接到数据库并从表中检索信息。我浏览了以前标题相似的帖子，但其中大多数是使用 mysql 函数编写的，而我正在使用 mysqli 函数。
I want somebody who can go through this simple script and let me know where I am making my mistake.
我想要一个可以完成这个简单脚本的人，让我知道我在哪里犯了错误。
This is my class.connect.php:
这是我的class.connect.php：
<?php
class mySQL{
var $host;
var $username;
var $password;
var $database;
public $dbc;
public function connect($set_host, $set_username, $set_password, $set_database)
{
$this->host = $set_host;
$this->username = $set_username;
$this->password = $set_password;
$this->database = $set_database;
$this->dbc = mysqli_connect($this->host, $this->username, $this->password, $this->database) or die('Error connecting to DB');
}
public function query($sql)
{
return mysqli_query($this->dbc, $sql) or die('Error querying the Database');
}
public function fetch($sql)
{
$array = mysqli_fetch_array($this->query($sql));
return $array;
}
public function close()
{
return mysqli_close($this->dbc);
}
}
?>
This is my index.php:
这是我的index.php：
<?php
require_once ("class.connect.php");
$connection = new mySQL();
$connection->connect('localhost', 'myDB', 'joker', 'names_list');
$myquery = "SELECT * FROM list";
$query = $connection->query($myquery);
while($array = $connection->fetch($query))
{
echo $array['first_name'] . '<br />';
echo $array['last_name'] . '<br />';
}
$connection->close();
?>
I am getting the error saying that Error querying the Database.
我收到错误说Error querying the Database。
采纳答案by craniumonempty
The problem is either this:
问题是这样的：
public function fetch($sql)
{
$array = mysqli_fetch_array($this->query($sql));
return $array;
}
or this:
或这个：
while($array = $connection->fetch($query))
Because you are using the result from the query to query again. Basically, you are doing:
因为您正在使用查询的结果再次查询。基本上，你正在做：
$r = mysqli_query($this->dbc, $sql);
$array = mysqli_fetch_array(mysqli_query($this->dbc, $r));
And you are getting an error, because $r is not a query string. When it's converted to a string, it's a "1" (from your other comment).
并且您收到错误消息，因为 $r 不是查询字符串。当它转换为字符串时，它是一个“1”（来自您的其他评论）。
Try changing the function to (changed name of variable so you can see the difference):
尝试将函数更改为（更改变量名称，以便您可以看到差异）：
public function fetch($result)
{
return mysqli_fetch_array($result);
}
or just call the function directly.
或者直接调用函数。
回答by ajreal
Few problems :-
几个问题：-
you don't die without provide a proper mysql error(and is good practice to exit gracefully)
fetch methodis only FETCH the first row
mysqlihave OO method, why you still using procedural function?
如果不提供正确的 mysql 错误，您就不会死（这是优雅退出的好习惯）
fetch 方法只是 FETCH 第一行
mysqli有 OO 方法，为什么你还在使用过程函数？
回答by Andre
If you don't do your own db abstraction for learning php and mysql, you can use Medoo (http://medoo.in/).
如果你没有为学习 php 和 mysql 做你自己的 db 抽象，你可以使用 Medoo ( http://medoo.in/)。
It's a free and tiny db framework, that could save a huge work and time.
这是一个免费的小型数据库框架，可以节省大量的工作和时间。
回答by Pramod Jain
Try to check this
尝试检查这个
https://pramodjn2.wordpress.com/
https://pramodjn2.wordpress.com/
$database = new db();
$query = $database->select(‘user');
$st = $database->result($query);
print_r($st);
class db {
public $server = ‘localhost';
public $user = ‘root';
public $passwd = ‘*****';
public $db_name = ‘DATABASE NAME';
public $dbCon;
public function __construct(){
$this->dbCon = mysqli_connect($this->server, $this->user, $this->passwd, $this->db_name);
}
public function __destruct(){
mysqli_close($this->dbCon);
}
/* insert function table name, array value
$values = array(‘first_name' => ‘pramod','last_name'=> ‘jain');
*/
public function insert($table,$values)
{
$sql = “INSERT INTO $table SET “;
$c=0;
if(!empty($values)){
foreach($values as $key=>$val){
if($c==0){
$sql .= “$key='”.htmlentities($val, ENT_QUOTES).”‘”;
}else{
$sql .= “, $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}
$c++;
}
}else{
return false;
}
$this->dbCon->query($sql) or die(mysqli_error());
return mysqli_insert_id($this->dbCon);
}
/* update function table name, array value
$values = array(‘first_name' => ‘pramod','last_name'=> ‘jain');
$condition = array(‘id' =>5,'first_name' => ‘pramod!');
*/
public function update($table,$values,$condition)
{
$sql=”update $table SET “;
$c=0;
if(!empty($values)){
foreach($values as $key=>$val){
if($c==0){
$sql .= “$key='”.htmlentities($val, ENT_QUOTES).”‘”;
}else{
$sql .= “, $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}
$c++;
}
}
$k=0;
if(!empty($condition)){
foreach($condition as $key=>$val){
if($k==0){
$sql .= ” WHERE $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}else{
$sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}
$k++;
}
}else{
return false;
}
$result = $this->dbCon->query($sql) or die(mysqli_error());
return $result;
}
/* delete function table name, array value
$where = array(‘id' =>5,'first_name' => ‘pramod');
*/
public function delete($table,$where)
{
$sql = “DELETE FROM $table “;
$k=0;
if(!empty($where)){
foreach($where as $key=>$val){
if($k==0){
$sql .= ” where $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}else{
$sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}
$k++;
}
}else{
return false;
}
$del = $result = $this->dbCon->query($sql) or die(mysqli_error());
if($del){
return true;
}else{
return false;
}
}
/* select function
$rows = array(‘id','first_name','last_name');
$where = array(‘id' =>5,'first_name' => ‘pramod!');
$order = array(‘id' => ‘DESC');
$limit = array(20,10);
*/
public function select($table, $rows = ‘*', $where = null, $order = null, $limit = null)
{
if($rows != ‘*'){
$rows = implode(“,”,$rows);
}
$sql = ‘SELECT ‘.$rows.' FROM ‘.$table;
if($where != null){
$k=0;
foreach($where as $key=>$val){
if($k==0){
$sql .= ” where $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}else{
$sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
}
$k++;
}
}
if($order != null){
foreach($order as $key=>$val){
$sql .= ” ORDER BY $key “.htmlentities($val, ENT_QUOTES).””;
}
}
if($limit != null){
$limit = implode(“,”,$limit);
$sql .= ” LIMIT $limit”;
}
$result = $this->dbCon->query($sql);
return $result;
}
public function query($sql){
$result = $this->dbCon->query($sql);
return $result;
}
public function result($result){
$row = $result->fetch_array();
$result->close();
return $row;
}
public function row($result){
$row = $result->fetch_row();
$result->close();
return $row;
}
public function numrow($result){
$row = $result->num_rows;
$result->close();
return $row;
}
}
回答by Affo
The mysqli_fetch_array function in your fetch method requires two parameters which are the SQL result and the kind of array you intend to return. In my case i use MYSQLI_ASSOC.
fetch 方法中的 mysqli_fetch_array 函数需要两个参数，它们是 SQL 结果和您打算返回的数组类型。就我而言，我使用 MYSQLI_ASSOC。
That is it should appear like this:
也就是说它应该是这样的：
public function fetch($sql)
{
$array = mysqli_fetch_array($this->query($sql), MYSQLI_ASSOC);
return $array;
}
公共函数 fetch($sql) {
$array = mysqli_fetch_array($this->query($sql), MYSQLI_ASSOC);
返回 $array; }
回答by Wesley van Opdorp
Obviously an error occurs on SELECT * FROM listyou can use mysqli_errorto find the error:
很明显，出现了错误，SELECT * FROM list您可以使用它mysqli_error来查找错误：
return mysqli_query($this->dbc, $sql) or die('Error:'.mysqli_error($this->dbc));
This will display the exact error message and will help you solve your problem.
这将显示确切的错误消息并帮助您解决问题。
回答by Gaurang P
**classmysql.inc.php**
<?php
class dbclass {
var $CONN;
function dbclass() { //constructor
$conn = mysql_connect(SERVER_NAME,USER_NAME,PASSWORD);
//$conn = mysql_connect(localhost,root,"","");
if(!$conn)
{ $this->error("Connection attempt failed"); }
if(!mysql_select_db(DB_NAME,$conn))
{ $this->error("Database Selection failed"); }
$this->CONN = $conn;
return true;
}
//_____________close connection____________//
function close(){
$conn = $this->CONN ;
$close = mysql_close($conn);
if(!$close){
$this->error("Close Connection Failed"); }
return true;
}
function error($text) {
$no = mysql_errno();
$msg = mysql_error();
echo "<hr><font face=verdana size=2>";
echo "<b>Custom Message :</b> $text<br><br>";
echo "<b>Error Number :</b> $no<br><br>";
echo "<b>Error Message :</b> $msg<br><br>";
echo "<hr></font>";
exit;
}
//_____________select records___________________//
function select ($sql=""){
if(empty($sql)) { return false; }
if(!eregi("^select",$sql)){
echo "Wrong Query<hr>$sql<p>";
return false; }
if(empty($this->CONN)) { return false; }
$conn = $this->CONN;
$results = @mysql_query($sql,$conn);
if((!$results) or empty($results)) { return false; }
$count = 0;
$data = array();
while ( $row = mysql_fetch_array($results)) {
$data[$count] = $row;
$count++; }
mysql_free_result($results);
return $data;
}
//________insert record__________________//
function insert ($sql=""){
if(empty($sql)) { return false; }
if(!eregi("^insert",$sql)){ return false; }
if(empty($this->CONN)){ return false; }
$conn = $this->CONN;
$results = @mysql_query($sql,$conn);
if(!$results){
$this->error("Insert Operation Failed..<hr>$sql<hr>");
return false; }
$id = mysql_insert_id();
return $id;
}
//___________edit and modify record___________________//
function edit($sql="") {
if(empty($sql)) { return false; }
if(!eregi("^update",$sql)){ return false; }
if(empty($this->CONN)){ return false; }
$conn = $this->CONN;
$results = @mysql_query($sql,$conn);
$rows = 0;
$rows = @mysql_affected_rows();
return $rows;
}
//____________generalize for all queries___________//
function sql_query($sql="") {
if(empty($sql)) { return false; }
if(empty($this->CONN)) { return false; }
$conn = $this->CONN;
$results = mysql_query($sql,$conn) or $this->error("Something wrong in query<hr>$sql<hr>");
if(!$results){
$this->error("Query went bad ! <hr>$sql<hr>");
return false; }
if(!eregi("^select",$sql)){return true; }
else {
$count = 0;
$data = array();
while ( $row = mysql_fetch_array($results))
{ $data[$count] = $row;
$count++; }
mysql_free_result($results);
return $data;
}
}
function extraqueries($sql="") {
if(empty($sql)) { return false; }
if(empty($this->CONN)) { return false; }
$conn = $this->CONN;
$results = mysql_query($sql,$conn) or $this->error("Something wrong in query<hr>$sql<hr>");
if(!$results){
$this->error("Query went bad ! <hr>$sql<hr>");
return false; }
else {
$count = 0;
$data = array();
while ( $row = mysql_fetch_array($results))
{ $data[$count] = $row;
$count++; }
mysql_free_result($results);
return $data;
}
}
}
?>
**config.inc.php**
<?php
ini_set("memory_limit","70000M");
ini_set('max_execution_time', 900);
ob_start();
session_start();
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
############################################
# Database Server
############################################
if($_SERVER['HTTP_HOST']=="localhost")
{
define("DB_NAME","DB_NAME");
define("SERVER_NAME","SERVER_NAME");
define("USER_NAME","USER_NAME");
define("PASSWORD","PASSWORD");
}
else
{
define("DB_NAME","DB_NAME");
define("SERVER_NAME","SERVER_NAME");
define("USER_NAME","USER_NAME");
define("PASSWORD","PASSWORD");
}
#############################################
# File paths
#############################################
// For the Database file path
include("system/classmysql.inc.php");
//For the inc folders
define("INC","inc/");
//For the Function File of the pages folders
define("FUNC","func/");
//For the path of the system folder
define("SYSTEM","system/");
$table_prefix = 'dep_';
################################################################
# Database Class
################################################################
$obj_db = new dbclass();
?>
**Function Page**
<?php
// IF admin is not logged in
if(!isset($_SESSION['session_id']))
{
header("location:index.php");
}
$backpage = 'page.php?type=staff&';
if(isset($_REQUEST['endbtn']) && trim($_REQUEST['endbtn']) == "Back")
{
header("location:".$backpage);
die();
}
// INSERT into database.
if(isset($_REQUEST['submit']) && trim($_REQUEST['submit']) == "Submit")
{
$pass = addslashes(trim($_REQUEST['password']));
$password = encrypt($pass, "deppro");
$username = addslashes(trim($_REQUEST['username']));
$sql = "select * from ".$table_prefix."users where `UserName` ='".$username."'";
$result = $obj_db->select($sql);
if(count($result) == 0)
{
$insert="INSERT INTO ".$table_prefix."users (`UserName`)VALUES ('".$username."')";
$sql=$obj_db->insert($insert);
$newuserid = mysql_insert_id($obj_db->CONN);
}
header("location:".$backpage."msg=send&alert=2");
die();
}
// DELETE record from database
if(isset($_REQUEST['action']) && trim($_REQUEST['action'])==3)
{
if(isset($_REQUEST['id']) && trim($_REQUEST['id']!=""))
{
$id = site_Decryption($_REQUEST['id']);
$sql_del = "Delete from ".$table_prefix."users where StaffID ='$id'";
$del = $obj_db->sql_query($sql_del);
header("location:".$backpage."msg=delete&alert=2");
die();
}
}
// UPDATE the record
$action=1;
if((isset($_REQUEST['action']) && trim($_REQUEST['action'])==2) && (!(isset($_REQUEST['submit']) && trim($_REQUEST['submit']) == "Submit")))
{
if(isset($_REQUEST['id']) && trim($_REQUEST['id']!=""))
{
$id = site_Decryption($_REQUEST['id']);
//$id = $_SESSION['depadmin_id'];
$sql = "select * from ".$table_prefix."users where StaffID ='$id'";
$result = $obj_db->select($sql);
if($result)
{
foreach($result as $row)
{
$title = stripslashes($row['StaffTitle']);
$action=2;
}
}
if(isset($_REQUEST['submit']) && trim($_REQUEST['submit']) == "Update")
{
$title = addslashes(trim($_REQUEST['title']));
$sql_upd ="UPDATE ".$table_prefix."users SET `StaffTitle` = '$title' WHERE StaffID ='$id'";
$result = $obj_db->sql_query($sql_upd);
$action=1;
header("location:".$backpage."msg=edited&alert=2");
die();
}
}
}
if(isset($_REQUEST['vid']) && trim($_REQUEST['vid']!=""))
{
$id = site_Decryption($_REQUEST['vid']);
$sql = "select * from ".$table_prefix."users where StaffID ='$id'";
$result = $obj_db->select($sql);
if($result)
{
foreach($result as $row)
{
$username = stripslashes($row['UserName']);
}
}
}
?>
<td class="center"><a href="cproperty.php?script=edit&action=2&id=<?php echo site_Encryption($sql[$j]['PropertyID']); ?>"><span class="editbutton"> </span></a> <a href="cproperty.php?action=3&id=<?php echo site_Encryption($sql[$j]['PropertyID']); ?>" onClick="return confirm('Are you sure to delete?');"><span class="deletebutton"> </span></a> <a class="lightbox" title="View" href="cpropertyview.php?script=view&vid=<?php echo site_Encryption($sql[$j]['PropertyID']); ?>&lightbox[width]=55p&lightbox[height]=60p"><span class="viewbutton"> </span></a></td>
