带有 LIKE 子句的 PHP 参数化 mysql 语句返回不同步的命令

Question

我正在尝试将 LIKE 子句与带有参数化查询的 php 和 mysql 一起使用。不过，每次尝试时，我都会遇到不同的错误。

我尝试实现来自 here 的解决方案, here , 和 here .每一个都抛出不同的错误，所以恐怕问题出在我遗漏的地方。如果我在 execute 函数中尝试使用数组，我会得到 Command out of sync 错误。当我尝试绑定(bind)值或参数时，出现无法绑定(bind)字符串错误。

我不知所措。

感谢您的帮助!

 <? 
    $access = 3;
    $dbConnect = true;
    require "../scripts/php/scriptSecurity.php";

    // Partial name given by user.
    $namePart = $_GET["namePart"];

    // Deal with name parts. last, first middle or first middle last, or first last
    if (strpos($namePart, ',') !== false){
        $arr_name = explode(",", $namePart);
        $lName = $arr_name[0];
        if (strpos($arr_name[1], " ") !== false){
            $firstName = substr($arr_name[1], 0, strpos($arr_name[1], " ", 1));
            $middleName = substr($arr_name[1], strpos($arr_name[1], " ", 1));
        }
    }
    elseif (strpos($namePart, " ") !== false){
        $arr_name = explode(" ", $namePart);
        if (sizeOf($arr_name) == 3) {       
            $fName = $arr_name[0];
            $lName = $arr_name[3];
            $mName = $arr_name[2];
        }
        elseif (sizeOf(arr_name) == 2) {
            $fName = $arr_name[0];
            $lName = $arr_name[1];
            $mName = $arr_name[1];
        }
        else {
            $fName = $namePart;
            $mName = $namePart;
            $lName = $namePart;
        }
    }
    else {
        $fName = $namePart;
        $lName = $namePart;
        $mName = $namePart;
    }
    // Get rid of extra spaces.
    $fName = str_replace(" ", "", $fName);
    $lName = str_replace(" ", "", $lName);
    $mName = str_replace(" ", "", $mName);
    // build query
    $query = "SELECT LastName, FirstName, MiddleName, StudentId, Gender, Grade, GradYear FROM students WHERE LastName LIKE ? OR FirstName LIKE ? OR MiddleName LIKE ? ORDER BY LastName, FirstName LIMIT 20";
    $stmt = $connect->prepare($query);
    // execute
    $stmt->execute(array('%'.$lName.'%', '%'.$fName.'%', '%'.$mName.'%'));
    $result = $stmt->get_result();
    // post results
    if (!$result) {
        echo $connect->error;
        echo "No Results";
    }
    else {
        echo "Results";
        while ($row = $result->fetch_assoc()){
            ?>
                <div><? echo $row["LastName"] . ", " . $row["FirstName"] . "(" . $row["StudentId"] . ")"?> </div>
            <?php 
        }
    }
?>

Answer 1

您以错误的方式将参数中的字符串传递给 wildchar 您可以使用最简单的方法使用 concat 管理 wildchar 并将纯 var 分配为参数

  $query = "SELECT LastName, FirstName, MiddleName, StudentId, Gender, Grade, GradYear 
  FROM students 
  WHERE LastName LIKE concat('%',?, '%') 
  OR FirstName LIKE concat('%',?, '%')
  OR MiddleName LIKE concat('%',?, '%')
  ORDER BY LastName, FirstName 
  LIMIT 20";
  $stmt = $connect->prepare($query);
  // execute
  $stmt->execute(array($lName, $fName, $mName));