PHP Parameterized mysql statement with LIKE clause returns command out of sync

Question

I am trying to use a LIKE clause with a parameterized query with php and mysql. Every time I try though, I'm getting different errors.

I've tried to implement solutions from here, here, and here. Each of these is throwing different errors, so I'm afraid the problem is in something that I'm missing. If I try with an array in the execute function, I get Command out of sync error. When I try binding values or parameters, I'm getting a can't bind on string error.

I'm at a loss for what I'm missing.

Thanks for any help!

 <? 
    $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 
        }
    }
?>

ScaisEdge · Accepted Answer · 2017-11-26T20:47:33.370

0

You pass the string in param with wildchar in wrong way you can use a simplest way managing the wildchar with concat and assign the pure var as param

  $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));

edited Nov 26 '17 at 20:47

answered Nov 26 '17 at 20:35

ScaisEdge

131,976
10
91
107

Thanks for the quick reply. I tried this and am now getting: Call to a member function bind_param() on boolean – edumacator Nov 26 '17 at 20:37
you missed lastname column name .. before where .. add the missing code .. and why downvote ??? – ScaisEdge Nov 26 '17 at 20:41
Ok...so I had a mistake in the code. Just fixed that, and now I'm getting results, but I'm only getting results for the first character. If the $namePart is one character, I get the top twenty results with that character. When I type a second character, I'm not getting an error, but I'm getting no results. – edumacator Nov 26 '17 at 20:41
for this you should check you code before the query and this seems another problem respect tom your original question – ScaisEdge Nov 26 '17 at 20:43
Yes. True. I'll post separately. It is a new problem. I just hate fixing one problem only to be introduced to the next...*sigh. Thanks again for the help. – edumacator Nov 26 '17 at 20:45

PHP Parameterized mysql statement with LIKE clause returns command out of sync

1 Answers1