实现mysqli连接的最佳实践/最实用的方法

2021-12-25 00:00:00 database-connection php mysql mysqli

我正在努力简化我们的数据库助手和实用程序，我看到我们的每个函数，例如 findAllUsers(){....} 或 findCustomerById($id) {...} 有自己的连接细节，例如:

I'm working on streamlining a bit our db helpers and utilities and I see that each of our functions such as for example findAllUsers(){....} or findCustomerById($id) {...} have their own connection details for example :

function findAllUsers() {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        die("Connection to DB failed: " . $con->connect_error);
    } else {
        sql = "SELECT * FROM customers..."
        .....
        .....
    }

}

等每个助手/功能.所以我考虑使用一个返回连接对象的函数，例如:

and so on for each helper/function. SO I thought about using a function that returns the connection object such as :

function dbConnection ($env = null) {
    $srv = 'xx.xx.xx.xx';
    $usr = 'username';
    $pwd = 'password';
    $db = 'database';
    $port = 3306;
    $con = new mysqli($srv, $usr, $pwd, $db, $port);

    if ($con->connect_error) {
        return false;
    } else {
        return $con;
    }
}

那我就可以了

function findAllUsers() {
    $con = dbConnection();
    if ($con === false) {
        echo "db connection error";
    } else {
        $sql = "SELECT ....
        ...
    }

与诸如 $con = new dbConnection() 之类的类系统相比，使用这样的函数有什么优势吗?

Is there any advantages at using a function like this compared to a Class system such as $con = new dbConnection() ?

推荐答案

您应该只打开一次连接.一旦你意识到你只需要打开一次连接，你的函数 dbConnection 就变得毫无用处了.您可以在脚本开始时实例化 mysqli 类，然后将其作为参数传递给所有函数/类.

You should open the connection only once. Once you realize that you only need to open the connection once, your function dbConnection becomes useless. You can instantiate the mysqli class at the start of your script and then pass it as an argument to all your functions/classes.

连接总是相同的三行:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli($srv, $usr, $pwd, $db, $port);
$con->set_charset('utf8mb4');

然后只需将其作为参数传递，不再使用 if 语句执行任何检查.

Then simply pass it as an argument and do not perform any more checks with if statements.

function findAllUsers(mysqli $con) {
    $sql = "SELECT ....";
    $stmt = $con->prepare($sql);
    /* ... */
}

看起来您的代码是某种意大利面条式代码.因此，我强烈建议重写它并在 PSR-4 中使用 OOP.

It looks like your code was some sort of spaghetti code. I would therefore strongly recommend to rewrite it and use OOP with PSR-4.

相关文章