$stmt->bind_param() dynamically if condition query

问题:

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

回答1:

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.

回答2:

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

回答3:

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 
    }