如何在mysqli准备好的语句中动态绑定参数? [英] How to dynamically bind params in mysqli prepared statement?

问题描述

我正在尝试为我的项目创建一个函数.我希望它能够处理所有检查功能.我的意思是在您开始在数据库中插入一行之前，请检查该电子邮件地址是否存在一行.

I'm trying to create a function for my project. I would like it to handle all the check functions. What I mean with that is before you start inserting a row in your database you check if a row exists with that email address.

要动态使用此功能，它必须灵活.所以我确实将变量放入数组中，但是mysqli_stmt_bind_param无法处理数组.作为解决方案，我尝试制作一个foreach循环.

To use this function dynamically it needs to be flexible. So I did put my variables in an array, but mysqli_stmt_bind_param can't handle arrays. As a solution, I tried making a foreach loop.

查询:

$sql = "SELECT users_id, users_email FROM users WHERE users_id = ? AND users_email = ?;";

调用函数:

check_database($sql, array($id, $mail), array("s", "s"), $location);

我的原始功能:

function check_database($sql, $variables, $types, $location)
{
    require $_SERVER['DOCUMENT_ROOT'] . '/includes/db/db.inc.php';
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        header("Location: " . $location . "?error=sqlerror");
        exit();
    } else {
        mysqli_stmt_bind_param($stmt, $types, $variables);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        if (!$row = mysqli_fetch_assoc($result)) {
            return true;
        }
    }
}

我这样在mysqli_stmt_bind_param上添加了foreach:

foreach ($types as $index => $type) {
    mysqli_stmt_bind_param($stmt, $type, $variables[$index]);
}

这给了我一个错误，我不知道如何解决:(

This gives me an error and I don't know how to solve it :(

警告:mysqli_stmt_bind_param():变量数与准备好的语句中的参数数不匹配

Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement

推荐答案

您步入正轨！对于每个使用mysqli的PHP程序员来说，这样的函数应该是日常的伴侣，但是奇怪的是，很少有人想到创建一个.

You are on a very right track! Such a function should be an everyday companion for the every PHP programmer using mysqli, but strangely, only few ever have an idea of creating one.

我曾经有一个完全相同的想法，并实现了我自己的 mysqli辅助函数 :

I've had an exactly the same idea once and implemented a mysqli helper function of my own:

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

与您的方法的主要区别

• 仅一次建立连接.您的代码每次执行查询时都会连接，这绝对不是它应该做的
• 它可用于任何查询，而不仅限于SELECT.您可以在文章
中的示例部分中查看该功能的多功能性
• 将类型设为可选，因为大多数时候您都不在乎该类型
• 没有奇怪的$ location变量.老实说，无论HTTP内容是什么，都绝不能成为数据库操作的一部分！如果您想知道如何正确处理错误，请参阅我的其他文章 PHP中的错误报告

• the connection is made only once. Your code connects every time it executes a query and this is absolutely NOT what it should do
• it can be used for any query, not only SELECT. You can check the versatility of the function in the examples section down in the article
• types made optional as most of time you don't care for the type
• no bizarre $location variable. Honestly, whatever HTTP stuff should never be a part of a database operation! If you're curious how to properly deal with errors, here is my other article Error reporting in PHP

在您的示例查询中，可以这样使用

With your example query it could be used like this

$check = prepared_query($sql, [$id, $mail])->get_result()->fetch_row();

或者，如果您想要一个独特的功能，也可以将其制成

or, if you want a distinct function, you can make it as well

function check_database($mysqli, $sql, $params, $types = "")
{
    return prepared_query($mysqli, $sql, $params, $types)->get_result()->fetch_row();
}

现在可以将其称为

$check = check_database($sql, [$id, $mail]);

这篇关于如何在mysqli准备好的语句中动态绑定参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！