error when using bind_param() in php

Question

I keep getting the follow error when using bind_param() and have tried everything to fix it but nothing seems to work.

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

here is my code

$output = '';
$output2 = '';
$output3 = '';

  if(isset($_POST['search'])) {
    $search = $_POST['search'];
    $search = preg_replace("#[^0-9a-z]i#","", $search);



    if ($stmt = $db->prepare("SELECT * FROM Users WHERE name LIKE '%$search%'")){
    $stmt->bind_param("s", $search);
    $count = $stmt->num_rows();
    $stmt->execute();


    if($count == 0){
      $output = "There was no search results!";

    }else{

      while ($rows = $stmt->num_rows) {

        $name = $row ['name'];
        $location = $row ['location'];
        $gender = $row ['gender'];
        $date_of_birth = $row ['date_of_birth'];
        $picture = $row['url'];



        $output .='<form action="header.php" method="post"><div class="row"><div class="col-sm-3">'.$name.'<br>'.$location.'<br>'.$gender.'<br>'.$date_of_birth.'</div>';

        $output2 = '<div class="col-sm-3"><img src="upload/'.$picture.'"width="180" height="144" /></div></div>';

        $output3 = '<input id="add_friend" name= "addfriend" type="submit" value="Add As Friend" /></form>';

      }

    }
  }

Answer 1

You need to bind the value to a placeholder ? in the querystring. Then you need to look at the parameters you pass to bind_param() - that first argument should be the types of variables - in this case, $search is just one string, so the first argument should be s.

Further, you should note that $stmt->num_rows is a property, not a method. This property may be inaccurate (that is, it might show zero rows before the results are fetched) unless you store the results first, by using $stmt->store_result() first. Both of these needs to come after executing.

Then you need to bind the results using bind_param(). This means binding every column selected by the query. Therefor, it's better to select the specific columns you are looking for, instead of doing SELECT *. When you now fetch, it should be the single argument supplied to while, without assigning it to a variable.

$search = "%$search%";
if ($stmt = $db->prepare("SELECT name, location, gender, date_of_birth, url FROM Users WHERE name LIKE ?")){
    $stmt->bind_param("s", $search);
    $stmt->execute();
    $stmt->bind_result($name, $location, $gender, $date_of_birth, $picture);
    $stmt->store_result();
    $count = $stmt->num_rows;

   if ($count == 0) {
       $output = "There was no search results!";
   } else {
         while ($stmt->fetch()) {
             // You can use $name, $location, $gender, $date_of_birth, $picture here
            $output .='<form action="header.php" method="post"><div class="row"><div class="col-sm-3">'.$name.'<br>'.$location.'<br>'.$gender.'<br>'.$date_of_birth.'</div>';

            $output2 = '<div class="col-sm-3"><img src="upload/'.$picture.'"width="180" height="144" /></div></div>';

            $output3 = '<input id="add_friend" name= "addfriend" type="submit" value="Add As Friend" /></form>';
         }
    }
}

• PHP.net on mysqli_stmt::bind_param()
• PHP.net on mysqli_stmt::bind_result()
• PHP.net on mysqli_stmt::num_rows
• PHP.net on mysqli_stmt::store_result()