How Can I Dynamically Bind Parameters to MySQL Queries in PHP?

Dynamically Binding MySQL Parameters in PHP

In your application, you encounter scenarios where you need to bind parameters dynamically to prepared SQL queries. While your current approach works, it lacks versatility, especially when dealing with varying numbers of parameters.

To achieve the desired flexibility, you can leverage the following technique using call_user_func_array() and the unpacking operator (...$var):

public function get_custom_result($sql, $types = null, $params = null) {
    $stmt = $this->mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);

    if (!$stmt->execute()) return false;
    return $stmt->get_result();
}

This updated get_custom_result() method allows you to specify the parameter types and values dynamically through the $types and $params arguments, respectively. The ...$params syntax unpacks the array, passing each value individually to the bind_param() method.

For example, consider the following query and parameter values:

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC";

$params = ['1', 'Tk'];

You can now execute the query with dynamic parameter binding:

$res = $output->get_custom_result($sql, 'ss', $params);

while ($row = $res->fetch_assoc()) {
    echo $row['fieldName'] .'<br>';
}

The above is the detailed content of How Can I Dynamically Bind Parameters to MySQL Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!