Build Select Query With Dynamic Number of Like Conditions as a MySQLi Prepared Statement

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:

the WHERE clause expressions -- to be separated by ORs
the data types of your values -- your values are strings, so use "s"
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

Dynamically build a prepared statement with call_user_func_array()

I don't understand what ways you've tried, but I will try to answer:

according to bind_param manual:

first argument of bind_param is a string, like 'ssss'.

second and other arguments - are values to be inserted into a query.

So, your $a_params array should be not

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"

But:

0:"ssss"
1:"New Zealand"
2:"Grey Lynn"
3:"Auckland"
4:"Auckland"

See? All values are strings. And placeholders' types are the first one.

Also take into consideration that order of arguments in $a_params must be the same as order of parameters in bind_param. This means that, i.e., $a_params like

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:"ssss"

is wrong. Because first element of $a_params will be the first argument of bind_param and in this case it's not a "ssss" string.

So, this means that after you filled $a_params with values, placeholders' string should be added to the beginning of $a_params, with array_unshift for example:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

// try to call
call_user_func_array(array($stmt, 'bind_param'), $a_params);

In case this didn't work, you can refer to a part of answer you provided, where values of $a_params are passed by reference to another array $tmp, in your case you can try something like:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

$tmp = array();
foreach ($a_params as $key => $value) {
    // each value of tmp is a reference to `$a_params` values
    $tmp[$key] = &$a_params[$key];  
}

// try to call, note - with $tmp, not with $a_params
call_user_func_array(array($stmt, 'bind_param'), $tmp);

Dynamic select mysqli query with dynamic parameters returns error doesn't match number of bind variables

Because:

You are using user-supplied data, you must assume that your query is vulnerable to a malicious injection attack and
the amount of data that is to be built into the query is variable/indefinite and
you are only writing conditional checks on a single table column

You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.

Building this dynamic prepared statement is more convoluted (in terms of syntax) than using pdo, but it doesn't mean that you need to abandon mysqli simply because of this task.

$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia = 'House';

$media = array_unique(explode(',', $mediaArray . $otherMedia));
$count = count($media);

$conn = new mysqli("localhost", "root", "", "myDB");
$sql = "SELECT * FROM mediaservices";
if ($count) {
    $stmt = $conn->prepare("$sql WHERE socialmedianame IN (" . implode(',', array_fill(0, $count, '?')) . ")");
    $stmt->bind_param(str_repeat('s', $count), ...$media);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($sql);
}
foreach ($result as $row) {
    // access values like $row['socialmedianame']
}

For anyone looking for similar dynamic querying techniques:

SELECT with dynamic number of LIKE conditions
INSERT dynamic number of rows with one execute() call

How to make a fully dynamic prepared statement using mysqli API?

An excellent question. And thank you for moving to prepared statements. It seems that after all those years of struggle, the idea finally is starting to take over.

Disclaimer: there will be links to my own site because I am helping people with PHP for 20+ years and got an obsession with writing articles about most common issues.

Yes, it's perfectly possible. Check out my article, How to create a search filter for mysqli for the fully functional example.

For the WHERE part, all you need is to create two separate arrays - one containing query conditions with placeholders and one containing actual values for these placeholders, i.e:

`WHERE` clause

$conditions = [];
$parameters = [];

if (!empty($_POST["content"])) {
    $conditions[] = 'content LIKE ?';
    $parameters[] = '%'.$_POST['content ']."%";
}

and so on, for all search conditions.

Then you could implode all the conditions using AND string as a glue, and get a first-class WHERE clause:

if ($conditions)
{
    $where .= " WHERE ".implode(" AND ", $conditions);
}

The routine is the same for all search conditions, but it will be a bit different for the IN() clause.

`IN()` clause

is a bit different as you will need more placeholders and more values to be added:

if (!empty($_POST["opID"])) {
    $in  = str_repeat('?,', count($array) - 1) . '?';
    $conditions[] = "opID IN ($in)";
    $parameters = array_merge($parameters, $_POST["opID"]);
}

this code will add as many ? placeholders to the IN() clause as many elements in the $_POST["opID"] and will add all those values to the $parameters array. The explanation can be found in the adjacent article in the same section on my site.

After you are done with WHERE clause, you can move to the rest of your query

`ORDER BY` clause

You cannot parameterize the order by clause, because field names and SQL keywords cannot be represented by a placeholder. And to tackle with this problem I beg you to use a whitelisting function I wrote for this exact purpose. With it you can make your ORDER BY clause 100% safe but perfectly flexible. All you need is to predefine an array with field names allowed in the order by clause:

$sortColumns = ["title","content","priority"]; // add your own

and then get safe values using this handy function:

$orderField = white_list($_POST["column"], $sortColumns, "Invalid column name");
$order = white_list($_POST["order"], ["ASC","DESC"], "Invalid ORDER BY direction");

this is a smart function, that covers three different scenarios

in case no values were provided (i.e. $_POST["column"] is empty) the first value from the white list will be used, so it serves as a default value
in case a correct value provided, it will be used in the query
in case an incorrect value is provided, then an error will be thrown.

`LIMIT` clause

LIMIT values are perfectly parameterized so you can just add them to the $parameters array:

$limit = "LIMIT ?, ?";
$parameters[] = $offset;
$parameters[] = $recordsPerPage;

The final assembly

In the end, your query will be something like this

$sql = "SELECT id, title, content, priority, date, delivery 
        FROM tasks INNER JOIN ... $where ORDER BY `$orderField` $order $limit";

And it can be executed using the following code

$stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

where $data is a conventional array contains all the rows returned by the query.

Building a Dynamic Prepared statement for searching

You were on the right track as far as you went. What you missed is how to get the right number of bind parameters. $a_bind_params has enough parameters for the title, but when you add username to it, it has to be doubled. i.e., if $a_bind_params = ['bottle','soda'], your new array needs to be ['bottle','soda','bottle','soda'] or ['bottle','bottle','soda','soda']

How to prepare a statement with a dynamic number of parameters?

I'll address this question on three fronts: actual correctness of the code, a solution, and better practices.

The Code

This code actually does not work, as mentioned there are very basic syntax error that even prevents it from actually being run at all. I'll assume this is a simplification error, however even the concatenation is wrong: the statement is duplicated each time (.= and the string itself. either of these will work, both will destroy the query)

$where_clause .= $where_clause . ' AND '

Dynamic Number Of Parameters

The problem of having a dynamic number of parameters is interesting, and depending on the needs can be fairly convoluted, however in this case, a fairly simple param concatenation will allow you to achieve a dynamic number of parameters, as suggested by AbraCadaver .

More exactly, when a condition is added to the statement, separately add the sql to the statement, and the values to an array:

$sql .= 'cat = :cat';
$values[':cat'] = $_GET['c'];

You can then prepare the statement and execute it with the correct parameters.

$stmt = $pdo->prepare($sql);
$stmt->execute($values);

Better Practices

As mentioned, the code presented in this question possibly is not functional at all, so let me highlight a few basic OOP principles that would dramatically enhance this snippet.

Dependency Injection

The db connection should be injected through the constructor, not recreated every time you execute a query (as it will, if you connect in the index method). Notice that $pdo is a private property. It should not be public, accessible by other objects. If these objects need a database connection, inject the same pdo instance in their constructor as well.

class myclass
{
    private $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }
}

The flow

One of these methods should be private, called by the other (public one) that would receive in arguments everything that is needed to run the functions. In this case, there does not seem to be any arguments involved, everything comes from $_GET.

We can adapt index so that it accepts both the sql and the values for the query, but these three lines could easily be transferred to the other method.

private function index($sql, $values)
{
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($values);
    return $stmt->fetchAll();
}

Then the public gen_where_clause (I believe that is wrongly named... it really generates the values, not the clauses) that can be safely used, that will generate a dynamic number of parameters, protecting you from sql injection.

public function gen_where_clause()
{
    $sql = "SELECT COUNT(amount) AS paid_qs FROM qanda ";
    $values = [];
    if (isset($_GET['c']) || isset($_GET['t']))
    {
        $sql .= ' WHERE ';
        if (isset($_GET['c']))
        {
            $sql .= ' cat = :cat ';
            $values[':cat'] = $_GET['c'];
        }
        // etc.
    }
    return $this->index($sql, $values);
}

Filtering inputs

Escaping values is not needed, for sql injection protection that is, when using parameterized queries. However, sanitizing your inputs is always a correct idea. Sanitize it outside of the function, then pass it as an argument to the "search" function, decoupling the function from the superglobal $_GET. Defining the arguments for filtering is out of the rather large scope of this post, consult the documentation.

// global code
// create $pdo normally
$instance = new myclass($pdo);
$inputs = filter_input_array(INPUT_GET, $args);
$results = $instance->gen_search_clause($inputs);

Are Dynamic Prepared Statements Bad? (with php + mysqli)

I think it is dangerous to use eval() here.

Try this:

iterate the params array to build the SQL string with question marks "SELECT * FROM t1 WHERE p1 = ? AND p2 = ?"
call prepare() on that
use call_user_func_array() to make the call to bind_param(), passing in the dynamic params array.

The code:

call_user_func_array(array($stmt, 'bind_param'), array($types)+$param);

MySQLi prepared statement with dynamic update query

The trick is to construct an array that contains the parameters that you want to bind, then with the help of call_user_func_array, you can pass this array to bind_param.

See http://www.php.net/manual/en/function.call-user-func-array.php for details on call_user_func_array.

Your code can be something like:

    $para_type="";
    /* $para is the array that later passed into bind_param */
    $para=array($para_type);
    $query = 'UPDATE tickets SET ';

    IF(count($data) != 0) {
        /* Looping all values */

        foreach($data as $k=>$d) {
            $query .= '`'.$d['field'].'` = ? ,';

            $para_type .=$d['type'];

            $para[] = &$data[$k]['value'];
        }

        /* removing last comma */
        $query[(strlen($query)-2)] = '';

        /* adding where */
        $query .= ' WHERE `ticket_id` = ?';
        $para_type .= 'i';
        $para[]=&$ticket_id;

        call_user_func_array(array($stmt, 'bind_param'), $para);

        return true;
    }

Notice the & in front of all parameters, it is required by bind_param.

Another way which I think is better is to use PDO. It takes named parameter and can do incremental bind.

Build Select Query With Dynamic Number of Like Conditions as a MySQLi Prepared Statement