如何使用准备好的语句动态绑定参数? [英] how to dynamically bind parameters using prepared statement?

问题描述

我正在尝试编写准备好的语句以供用户输入.参数号是可变的，取决于用户输入.我正在尝试此代码

I am trying to write prepared statement for user input. parameter numbers are variable depends on user input. i am trying this code

php代码:

$string          = "my name";
$search_exploded = explode( " ", $string );
$num             = count( $search_exploded );
$cart            = array();
for ( $i = 1; $i <= $num; $i ++ ) {
    $cart[] = 's';
}
$str          = implode( '', $cart );
$inputArray[] = &$str;
$j            = count( $search_exploded );
for ( $i = 0; $i < $j; $i ++ ) {
    $inputArray[] = &$search_exploded[ $i ];
}
print_r( $inputArray );
foreach ( $search_exploded as $search_each ) {
    $x ++;
    if ( $x == 1 ) {
        $construct .= "name LIKE %?%";
    } else {
        $construct .= " or name LIKE %?%";
    }
}
$query = "SELECT * FROM info WHERE $construct";
$stmt  = mysqli_prepare( $conn, $query );
call_user_func_array( array( $stmt, 'bind_param' ), $inputArray );
if ( mysqli_stmt_execute( $stmt ) ) {

    $result = mysqli_stmt_get_result( $stmt );
    if ( mysqli_num_rows( $result ) > 0 ) {
        echo $foundnum = mysqli_num_rows( $result );
        while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {

            echo $id = $row['id'];
            echo $name = $row['name'];
        }
    }
}

当我print_r $ inputArray输出是这样的时候:

when i print_r $inputArray output is this:

   Array ( [0] => ss [1] => my [2] => name ) 

在错误日志中没有错误显示. 我在这里做什么错了，请告诉我.

there is no error showing in error log. what is wrong i am doing here please tell me.

推荐答案

100％经过测试/成功的代码:

%包装包含参数，而不是占位符. (有关其他说明，请参见嵌入式注释)

The % wrapping goes around the parameters, not the placeholders. (see inline comments for additional explanations)

$string = " b c ";

$strings = array_unique(preg_split('~\s+~', $string, -1, PREG_SPLIT_NO_EMPTY));  // isolate and remove duplicates
$where = '';
$types = '';
foreach ($strings as $s) {
    $params[] = "%{$s}%";  // wrap values in percent signs for LIKE
    $where .= (!$where ? " WHERE" : " OR") . " name LIKE ?";  // build clause
    $types .= 's';
}
// echo "<div>{$where}</div>";  // uncomment if you wish to see what is generated

if (!$conn = new mysqli("host", "user", "pass", "db")) {
    echo "Database Connection Error: " , $conn->connect_error;
} else {
    $query = "SELECT id, name FROM info{$where}";
    if(!$stmt = $conn->prepare($query)) {
        echo "Syntax Error @ prepare: " , $conn->error;  // don't show to public
    }else{
        if ($where) {
            array_unshift($params, $types);  // prepend the type values string
            $ref = [];  // add references
            foreach ($params as $i => $v) {
                $ref[$i] = &$params[$i];  // pass by reference as required/advised by the manual
            }
            call_user_func_array([$stmt, 'bind_param'], $ref);
        }    

        if (!$stmt->execute()) {
            echo "Error @ bind_param/execute: " , $stmt->error;  // don't show to public
        } elseif (!$stmt->bind_result($id, $name)) {
            echo "Error @ bind_result: " , $stmt->error;  // don't show to public
        } else {
            while ($stmt->fetch()) {
                echo "<div>$id : $name</div>"; 
            }
            $stmt->close();
        }
    }
}

p.s.如果您使用的是php5.6或更高版本，则可以享受splat/spread运算符(...)提供的更简短的语法.

p.s. If you are using php5.6 or higher, then you can enjoy the more brief syntax afforded by the splat/spread operator (...).

if ($where) {
    if (!$stmt->bind_param($types, ...$params)) {
        echo "MySQL Query Syntax Error: <b>Failed to bind placeholders and data</b>";  // $stmt->error;
    }
} 

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