代码之家 › 专栏 › 技术社区 › Steven Guerrero

具有不同参数的准备语句

prepared-statement mysqli php

-2

Steven Guerrero · 技术社区 · 8 年前

我只需要知道如何拥有一个可以接受1-4个字符串的mysqli\u stmt\u bind\u参数。带占位符(“”),在mysqli\u stmt\u bind\u param中,当搜索1个兴趣时,只有“$stmt”,s“,$Interest”起作用;当搜索“Any Interest”时,只有“$stmt”,ssss“,$Interest”起作用。

谢谢

    $Interest = $_GET['interestId'];

$sql = "SELECT * from User WHERE (Interest1 = '$Interest' OR Interest2 = '$Interest' OR Interest3 = '$Interest') OR '$Interest' = 'Any Interest';";


        echo "<h1 class='contact-intro'>";
        echo " Welcome to the business card library for $Interest! </h1>";

$stmt = mysqli_stmt_init($link);
//Prepare the prepare statements
if (!mysqli_stmt_prepare($stmt, $sql)) {
    echo "SQL statement failed";

} else {
    //Bind parameters to the placeholder(s)
    mysqli_stmt_bind_param($stmt, $Interest);
    //Run parameters inside database
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    //   $result = mysqli_query($link, $sql);
      $resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
(lots of echos.......)

更新了问题的完整代码和描述。

2 回复 | 直到 8 年前

Karlo Kokkak 8 年前

您可以将占位符的值放入数组变量中。在mysqli\u stmt\u bind\u param()中,可以使用splat运算符 ... -与下面的代码类似,因此您可以为单个表字段处理不同数量的占位符及其值。

您可以这样做:

$interest_array = array('basketball', 'basketball', 'basketball', 'basketball');
$s_marks = str_repeat("s", count($interest_array)); 
mysqli_stmt_bind_param($stmt, $s_marks, ...$interest_array);

这是:

mysqli_stmt_bind_param($stmt, $s_marks, ...$interest_array);

将被视为:

mysqli_stmt_bind_param($stmt, "ssss", $interest1,$interest2,$interest13, $interest4);

您的更新代码(尚未测试):

$Interest = $_GET['interestId'];
$sql = "SELECT * from User WHERE (Interest1 = ? OR Interest2 = ? OR Interest3 = ?) OR ? = 'Any Interest';";
$placeholder_count  = substr_count($sql, '?');
$s_marks = str_repeat("s", $placeholder_count); // creates sss string
$interest_array = array_fill(0, $placeholder_count, $Interest); // creates an array of the same values.

echo "<h1 class='contact-intro'>";
echo " Welcome to the business card library for $Interest! </h1>";

 $stmt = mysqli_stmt_init($link);

 if (!mysqli_stmt_prepare($stmt, $sql)) {
    echo "SQL statement failed";
} else {
    mysqli_stmt_bind_param($stmt, $s_marks, ...$interest_array);
    mysqli_stmt_execute($stmt);
}

其余代码将仅根据sql字符串中占位符的数量来采用。

d3t0x 8 年前

为什么不这样做呢?

// prepare and bind
$stmt = $conn->prepare("SELECT * from User WHERE (Interest1 = ? OR Interest2 = ? OR Interest3 = ?) OR ? = 'Any Interest';");
$stmt->bind_param("sss", $Interest, $Interest, $Interest);

if ($stmt->execute()){
    echo "<h1 class='contact-intro'>";
    echo " Welcome to the business card library for $Interest! </h1>";
}else{
    echo "SQL statement failed";
}
$stmt->close();