PHP - How to Parametrize SQL Query with Multiple Where Conditions (Prepared Statement)

php - How to parametrize SQL query with multiple WHERE conditions (prepared statement)?

I have solved my problem using PDO which has named parameters. So here is my solution, hope it helps somebody.

$by_nickname = $_GET['nickname'];
$by_steamid = $_GET['steamid'];
// integer
$by_serverid = $_GET['serverid'];

$pdo = new PDO("mysql:host=host;port=port;dbname=db;charset=utf8", "user", "password");

$conditions = array();
$parameters = array();
$where = "";

if($by_nickname !="")
{
    $conditions[] = " players.nickname LIKE :nickname";
    $parameters[":nickname"] = "%$by_nickname%";
}

if($by_steamid !="")
{
    $conditions[] = " ids.steamid = :steamid";
    $parameters[":steamid"] = $by_steamid;
}

if($by_serverid !="")
{
    $conditions[] = " servers.serverid = :serverid";
    // Beware of correct parameter type!
    $parameters[":serverid"] = intval($by_serverid);
}

if (count($conditions) > 0)
{
    $where = implode(' AND ', $conditions);
}

// check if $where is empty string or not
$query = "SELECT ... " . ($where != "" ? " WHERE $where" : "");

try
{
    if (empty($parameters))
    {
        $result = $pdo->query($query);
    }
    else
    {
        $statement = $pdo->prepare($query);
        $statement->execute($parameters);
        if (!$statement) throw new Exception("Query execution error.");
        $result = $statement->fetchAll();
    }
}
catch(Exception $ex)
{
    echo $ex->getMessage();
}

foreach($result as $row)
{
  // for example
  echo row["<Column Name>"];
}

Building Multiple LIKE Operator Prepared Statement at run-time

Consider building your LIKE expression portion of prepared SQL statement using implode. Then build a param arguments to be run with call_user_func_array().

$terms = explode(",", str_replace(",", " ,", $_POST['txtD']));

// PREPARED STATEMENT BUILD
$likes = [];
foreach($terms as $t) {
    $likes[] = "col3 LIKE CONCAT('%', ?, '%')";
}

$expr = implode(" or ", $likes);
$sql = "select col1, col2, col3 from tbl ".
       "where col1=? and col2 between ? and ? and (". $expr .")";

// PARAM ARG BUILD
$type = 'iii' . str_repeat("s", count($terms));
$sql_params = array_merge(array($stmt, $type, $param1, $param2, $param3), $terms);

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con, $sql);
call_user_func_array('mysqli_stmt_bind_param', sql_params);    
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

SQL and Param Build Demo

---

Alternatively, consider MySQL's REGEXP for a regex expression using pipes to denote OR logic:

// REPACE COMMAS BY PIPE
$terms = str_replace(",", "|", str_replace(",", " ,", $_POST['txtD']));

$sql = "select col1, col2, col3 from tbl " .
       "where col1=? and col2 between ? and ? and col3 regexp ?";

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con);    
mysqli_stmt_bind_param($stmt, "iii", $param1, $param2, $param3, $terms);
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

Do note as mentioned here REGEXP is slower in execution than the equivalent LIKE expression.

Bind multiple parameters for prepared statement

You can use parameters only once in a query

$sql1="SELECT @rownum := @rownum + 1 Rank, q.* FROM (SELECT @rownum:=0) r,(SELECT  * ,sum(`number of cases`) as tot, sum(`number of cases`) * 100 / t.s AS `% of total` FROM `myTable` CROSS JOIN (SELECT SUM(`number of cases`) AS s FROM `myTable` where `type`=:criteria and `condition`=:diagnosis) t where `type`=:criteria2 and `condition`=:diagnosis2 group by `name` order by `% of total` desc) q";
$stmt = $dbh->prepare($sql1);       
$stmt->execute(array(':criteria' => $search_crit, ':diagnosis' => $diagnosis, ':criteria2' => $search_crit, ':diagnosis2' => $diagnosis));

Parameterised IN Clause in prepared statement using MySql,PHP and ADODB

I would do it this way (as I was googling for a while and google came up with nothing useful):

$count = count($first_names);
$in_params = trim(str_repeat('?, ', $count), ', ');

$query = "
SELECT    
    mytable_id_pk
FROM 
    mytable
WHERE
    mytable_fname IN ({$in_params});";

$stmt = $DB->Prepare($query);
$result = $DB->Execute($stmt, $first_names);

This should do it...

How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement

As user3783243 states in the comments above, my placeholders and parameters where not matching. So in order to solve that, I did the following (this will be sloppy as I'm new to PHP but if someone can clean it up for me I'll award the answer to that).

First you have to create a string for the type parameter (mine are all strings so this was easy, you could run a conditional statement if you have different types). Since I use two placeholders for each entry in my SQL, each iteration will include two s's.

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

Then create a new array to put the types and the values all together (again, since there are two placeholders for each parameter, I just add the $word twice to the array):

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

Finally, bind the parameters using call_user_func_array:

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

So the code in my question now looks like this:

$word=preg_split('/[\s]+/',$terms);
$totalwords=count($word);

$sql="SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";

for(i=1;$i<$totalwords;$i++){
  $sql.=" AND (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";
}

$stmt=$conn->prepare($sql);

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

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

$stmt->execute;
$stmt->store_result;

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

Use bound parameter multiple times

I have ran over the same problem a couple of times now and I think i have found a pretty simple and good solution. In case i want to use parameters multiple times, I just store them to a MySQL User-Defined Variable.

This makes the code much more readable and you don't need any additional functions in PHP:

$sql = "SET @term = :term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(":term", "%$term%", PDO::PARAM_STR);
    $stmt->execute();
}
catch(PDOException $e)
{
    // error handling
}

$sql = "SELECT ... FROM table WHERE name LIKE @term OR number LIKE @term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $stmt->fetchAll();
}
catch(PDOException $e)
{
    //error handling
}

The only downside might be that you need to do an additional MySQL query - but imho it's totally worth it.

Since User-Defined Variables are session-bound in MySQL there is also no need to worry about the variable @term causing side-effects in multi-user environments.

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);

---

Related Topics