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-26 20:47:33Z

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, 2017 at 20:47

answered Nov 26, 2017 at 20:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

edumacator Over a year ago

Thanks for the quick reply. I tried this and am now getting: Call to a member function bind_param() on boolean

ScaisEdge Over a year ago

you missed lastname column name .. before where .. add the missing code .. and why downvote ???

edumacator Over a year ago

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.

ScaisEdge Over a year ago

for this you should check you code before the query and this seems another problem respect tom your original question

edumacator Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related