$stmt->bind_param() dynamically if condition query

Question

I have a php problem when want to bind_param dynamically when I using SELECT

this is my php code

$sql = 'SELECT ProductName, ProductId  FROM Product WHERE 1=1'
if($produkname != ''){
    $sql .= ' AND ProdukName Like ?';
}
if($produkcode != ''){
    $sql .= ' AND Produkcode Like ?';
}

if($stmt = $mysqli->prepare($sql)){
    $stmt->bind_param ('ss', $produkname, $produkcode)
    $stmt->execute();

    //else code 
}

I cannot bind_param if one of $produkname or $produkcode value is empty

For argument's sake, you're missing a semi-colon for `$sql = 'SELECT ProductName, ProductId FROM Product WHERE 1=1'` and for `$stmt->bind_param ('ss', $produkname, $produkcode)` — Funk Forty Niner, Jan 13 '15 at 00:57
@PHPglue That's a common idiom to return all rows. WHERE expressions don't necessarily have to involve actual columns, they just need to be expressions that evaluate to true/false. 1=1 is always true. — Michael Berkowski, Jan 13 '15 at 01:00
@PHPglue In this case, he's using this so he can dynamically put an `AND` statement at the end. It's a common technique because SQL will basically ignore it because it's always true — Machavity, Jan 13 '15 at 01:02
possible duplicate of [How to bind mysqli bind\_param arguments dynamically in PHP?](http://stackoverflow.com/questions/5100046/how-to-bind-mysqli-bind-param-arguments-dynamically-in-php) — FredTheWebGuy, Jan 13 '15 at 01:07
@PHPglue : i use 1=1 that because in some condition i don't use like statement to run the query.. — Hansen, Jan 13 '15 at 01:12

Michael Berkowski · Answer 1 · 2015-01-13T01:11:35.970

0

In your case, you can construct the WHERE clause such that it does not need dynamic parameters. Use boolean AND conditions to verify that the variable values are <> '' in the SQL rather than in the PHP, and you can therefore accomplish it using four ? (each variable bound twice) and no dynamic binding.

$sql = "
 SELECT ProductName, ProductId  
 FROM Product
 WHERE 1=1
  AND ((? <> '' AND ProdukName LIKE ?) OR ProdukName LIKE '%')
  AND ((? <> '' AND Produkcode LIKE ?) OR Produkcode LIKE '%')
";
// Then bind each variable *twice*
if($stmt = $mysqli->prepare($sql)){
    $stmt->bind_param ('ssss', $produkname, $produkname, $produkcode, $produkcode)
    $stmt->execute();

    //else code 
}

How this works:

AND ((? <> '' AND ProdukName LIKE ?) OR ProdukName LIKE '%')

This creates a condition where the first ? (supplied by a bound variable) if <> '' will cause the AND ProdukName Like ? to be also evaluated. If the variable was empty, on the othre hand, the other side of this expression OR ProdukName LIKE '%' will be evaluated instead. That matches any ProdukName value so essentially the entire expression is canceled out.

edited Jan 13 '15 at 01:11

answered Jan 13 '15 at 01:06

Michael Berkowski

267,341
46
444
390

thank you for the answered i will tried it when i at home... now i just go outside.. thank you very much have respond fastly – Hansen Jan 13 '15 at 01:14
You're welcome. Please @ me if you have any questions. This should work just fine though. – Michael Berkowski Jan 13 '15 at 01:15
@Hans Did you ever come back to this? – Michael Berkowski Jan 14 '15 at 19:49
@Hans In what way does it fail? Does it return no rows, or extra rows? Do you mean to use `%` wildcards in the `LIKE`? – Michael Berkowski Jan 15 '15 at 02:11
that not get return data, i use % inside php variable name like this $produkname = '%' . $produkname . '%'; – Hansen Jan 15 '15 at 02:16
@Hans Interesting. Please edit your code above to show when you add the `%`, because if it is always present, then the `? <> ''` condition will not be met unless the variable is bound before. In its current state, it may work to use an expression like `AND ((? <> '%%' AND ProdukName LIKE ?) OR ProdukName LIKE '%')` to match the otherwise empty variable with `%` added. – Michael Berkowski Jan 15 '15 at 02:22
MySQLi does not have a `bind_value()` alternative to `bind_param()`, so you cannot just `trim($produkname ,'%')` inside the `bind_param()`. – Michael Berkowski Jan 15 '15 at 02:23
I Have try another and work, i pun inside script directly like below : "SELECT ProductName, ProductId FROM Product WHERE 1=1 AND ProdukName LIKE ? AND Produkcode LIKE ?" , note : when i bind param $ProdukName = '%' . ($ProdukName == null ? '' : $ProdukName) . '%', and that work.. btw thank you for your help any way... – Hansen Jan 16 '15 at 01:01
@Hans You're welcome. Next time, please include all the relevant code. We could have avoided much confusion if it was clear that the variables were already `%` enclosed. – Michael Berkowski Jan 16 '15 at 01:04
@Hans You should add your own answer with your solution or accept an existing one. – Michael Berkowski Jan 16 '15 at 04:26

score 0 · Answer 2 · answered Jan 13 '15 at 01:12

You need more conditions:

$sql = 'SELECT ProductName, ProductId  FROM Product WHERE ';
if($produkname !== ''){
  $sql .= 'ProdukName Like ?'; $pdn = 1;
}
if($produkcode !== ''){
  if(isset($pdn))$sql .= ' && ';
  $sql .= 'Produkcode Like ?'; $pdc = 1;
}
if($stmt = $mysqli->prepare($sql)){
  if(isset($pdn, $pdc)){
    $stmt->bind_param('ss', $produkname, $produkcode);
  }
  elseif(isset($pdn)){
    $stmt->bind_param('s', $produkname);
  }
  elseif(isset($pdc)){
    $stmt->bind_param('s', $produkcode);
  }
  $stmt->execute(); 
}

score 0 · Answer 3 · answered Jan 13 '15 at 03:05

You could also utilize call_user_func_array with $stmt->bind_param to do something like this:

<?php
$sql = 'SELECT ProductName, ProductId  FROM Product WHERE 1=1'

$types = '';
$params = array(&$types);

if($produkname != ''){
    $sql .= ' AND ProdukName Like ?';

    $types .= 's';
    $params[] = $produkname;
}

if($produkcode != ''){
    $sql .= ' AND Produkcode Like ?';

    $types .= 's';
    $params[] = $produkcode;
}

if($stmt = $mysqli->prepare($sql)){
    if (!empty($types)) {
        call_user_func_array(array($stmt, 'bind_param'), $params);
    }

    $stmt->execute();

    //else code 
    }

$stmt->bind_param() dynamically if condition query

3 Answers3

Linked