Use One Bind_Param() With Variable Number of Input Vars

mysqli bind_param Number of variables doesn't match number of parameters in prepared statement

When using LIKE in a prepared statement, it's a little bit different. You should add the % to the parameter before binding it to the statement.

Try something like below:

$param = "%{$_GET['search']}%";
$stmt = $sql->prepare("SELECT name, site, message, `when` FROM messages WHERE message LIKE ?");
$stmt->bind_param('s', $param);
$stmt->execute();
$result = $stmt->get_result();

bind_param(): variables != parameters

Define the columns fetched explicitly. This should work (line 112):

// get the record from the database
if($stmt = $mysqli->prepare("SELECT id, vEmri, vCmimi, vNgjyra, vLenda, vTransmisioni, vKilometra, vProdhimi, vVellimi FROM veturat WHERE id=?"))
{
   $stmt->bind_param("i", $id);
   $stmt->execute();

   $stmt->bind_result($id, $vEmri, $vCmimi, $vNgjyra, $vLenda, $vTransmisioni, $vKilometra, $vProdhimi, $vVellimi);
   $stmt->fetch();

   // show the form
   renderForm($vEmri, $vCmimi, NULL, $id);

   $stmt->close();
}

You must have the same number of arguments in $stmt->bind_result() as the number of columns your SELECT query is fetching.

Dealing with a variable number of input fields

Determine how many $form['input']: count($form['input']);.

mysqli error - bind_param: number of variables doesn't match

You need to take a look at the manual:

1. You should not escape your values when you use a prepared statement as you will be adding literal backslashes in your data.
2. You should not inject your variables in the query but use placeholders (question marks in mysqli) instead. These are bound to your values.

So your query would be:

$query = "INSERT INTO store_customers (
                name,
                email,
                // etc.
            ) VALUES (
                ?,
                ?,
                // etc.
            );
        ";

And you bind your values:

$stmt->bind_value(
    'sssssssissssss',
    $_POST['customer_name'],
    $_POST['customer_email'],
    // etc.
);

Note that I am using bind_value() instead of bind_param() as this seems to be used once only so there is no need to bind parameters, you can bind the values directly. It should not make a difference though.

Mysqli:mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

It is failing because mysqli is not PDO and you cannot bind in a loop. Thus you have to use tricks to bind an array in mysqli. Luckily, if your PHP version is 5.6 or 7, you can use this code:

$stmt = $db->prepare($query);
$types = str_repeat('s', count($param));
$statement->bind_param($types, ...$param);
$statement->execute();

if not, then you are bound to use call_user_func()-based solution

Build SELECT query with dynamic number of LIKE conditions as a mysqli prepared statement

The % wrapping goes around the parameters, not the placeholders.

My snippet will be using object-oriented mysqli syntax instead of the procedural syntax that your code demonstrates.

First you need to set up the necessary ingredients:

1. the WHERE clause expressions -- to be separated by ORs
2. the data types of your values -- your values are strings, so use "s"
3. the parameters to be bound to the prepared statement

I am going to combine #2 and #3 into one variable for simpler "unpacking" with the splat operator (...). The data type string must be the first element, then one or more elements will represent the bound values.

As a logical inclusion, if you have no conditions in your WHERE clause, there is no benefit to using a prepared statement; just directly query the table.

Code: (100% Tested / Successful Code)

$string = "my name";

$conditions = [];
$parameters = [''];
foreach (array_unique(explode(' ', $string)) as $value) {
    $conditions[] = "name LIKE ?";
    $parameters[0] .= 's';
    $parameters[] = "%{$value}%";
}
// $parameters now holds ['ss', '%my%', '%name%']

$query = "SELECT * FROM info";
if ($conditions) {
    $stmt = $conn->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
    $stmt->bind_param(...$parameters);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($query);
}
foreach ($result as $row) {
    echo "<div>{$row['name']} and whatever other columns you want</div>"; 
}

---

For anyone looking for similar dynamic querying techniques:

• SELECT with dynamic number of values in IN()
• INSERT dynamic number of rows with one execute() call

---

Related Topics