Mysql搜索功能

Question

我正在尝试设置一个搜索函数，可以输入我想要的内容，也可以从下拉菜单中选择它，但是当字段为空白时，我希望它显示所有内容。

现在，当我搜索某物时，它将工作，但是当字段为空白时，它根本没有显示任何内容。我使用准备和bind_params来设置mysql查询，这就是为什么很难设置它的原因，因为如果变量是空的，我不确定如何轻松地删除查询的该部分，并更改绑定到查询的变量的数量。以下是查询

$stmt = $conn->prepare("SELECT * FROM re_tblcombinationlist WHERE active != ? AND ModifierKey = ? AND (LootItem1Key = ? OR LootItem2Key = ? ) ORDER BY ReportedOn DESC LIMIT ? , ?");
$stmt->bind_param("isssii", $i = 0, $modifier, $lootsearch, $lootsearch, $limits, $max);
$recent1 = infoo($stmt);

我试图通过添加以下if语句来解决这个问题，但是它没有改变任何事情。

if(!isset($_POST["modifier"])){
$modifier = '';
}else{
$modifier = clean($_POST["modifier"]);
}

if(!isset($_POST["lootsearch"])){
$lootsearch = '';
}else{
$lootsearch = clean($_POST["lootsearch"]);
}

基本上，如果修饰符或掠夺者是空的，我不希望这个部分在mysql查询中，但是这很容易完成，但是这使得处理要绑定的变量的数量变得非常困难，所以我试图找到一种方法，使它在变量为空的情况下搜索所有内容。

谢谢

Answer 1

我选择的最后一个解决方案是将我的上一篇文章和Kostas帖子结合起来。

首先，我们添加这个类：

class BindParam { 
private $values = array();
private $types = ''; 

public function add( $type, &$value ){ 
    $this->values[] = &$value; 
    $this->types .= $type; 
} 

public function get() { 
    $array = array_merge(array($this->types), $this->values);
    foreach($array as $key => $value)
    $refs[$key] = &$array[$key];
    return $refs; 
} 
}

然后，我们在需要动态使用查询的地方添加以下代码：

//User inputs, just for testing
$active = 0;
$modifier = 5;
$lootsearch = 'item';
$limits = 3;
$max = 5;

//Conditional binding
$bindParam = new BindParam(); 
$binds = array(); 
$binds[] = 'active = ?';
$bindParam->add('i', $active);

$query = "SELECT * FROM re_tblcombinationlist WHERE ";
if (!empty($modifier)) {
$binds[] = ' AND ModifierKey = ?';
$bindParam->add('s', $modifier);
} 
if (!empty($lootsearch)) {
$binds[] = ' AND (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ';
$bindParam->add('s', $lootsearch);
$bindParam->add('s', $lootsearch);
}

$query .= implode(" ", $binds);//removed the AND to allow for AND and OR
$query .= " ORDER BY ReportedOn DESC LIMIT ? , ?";

$bindParam->add('i', $limits);
$bindParam->add('i', $max);

//Uncomment this for debugging

//echo $query . '<br/>';

//Using the above user inputs query should be:
//SELECT * FROM re_tblcombinationlist WHERE active = ? AND ModifierKey = ? AND     (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ORDER BY ReportedOn DESC LIMIT ? ,     ?

//var_dump($bindParam->get());

//Maybe you have to experiment a bit with those lines
$stmt = $conn->prepare($query);
call_user_func_array(array($stmt, 'bind_param'), $bindParam->get());//Required in order to use the array given by bindParam->get()
$recent1 = infoo($stmt);

Answer 2

我想我找到了一个解决您的问题的方法，基于此：http://php.net/manual/en/mysqli-stmt.bind-param.php#109256

首先创建这个类：

class BindParam { 
    private $values = array();
    private $types = ''; 

    public function add( $type, $value ){ 
        $this->values[] = $value; 
        $this->types .= $type; 
    } 

    public function get() { 
        return array_merge(array($this->types), $this->values); 
    } 
}

之后，请确保在代码中导入该类，然后在搜索文件中使用以下插入行：

//User inputs, just for testing
$active = 0;
$modifier = 5;
$lootsearch = 'item';
$limits = 3;
$max = 5;

//Conditional binding
$bindParam = new BindParam(); 
$binds = array(); 
$binds[] = 'active = ?';
$bindParam->add('i', $active);

$query = "SELECT * FROM re_tblcombinationlist WHERE ";
if (!empty($modifier)) {
    $binds[] = 'ModifierKey = ?';
    $bindParam->add('s', $modifier);
} 
if (!empty($lootsearch)) {
    $binds[] = ' (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ';
    $bindParam->add('s', $lootsearch);
    $bindParam->add('s', $lootsearch);
}

$query .= implode(" AND ", $binds);
$query .= " ORDER BY ReportedOn DESC LIMIT ? , ?";

$bindParam->add('i', $limits);
$bindParam->add('i', $max);

//Uncomment this for debugging

//echo $query . '<br/>';

//Using the above user inputs query should be:
//SELECT * FROM re_tblcombinationlist WHERE active = ? AND ModifierKey = ? AND (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ORDER BY ReportedOn DESC LIMIT ? , ?

//var_dump($bindParam->get());

//Maybe you have to experiment a bit with those lines
$stmt = $conn->prepare($query);
$stmt->bind_param($bindParam->get());
$recent1 = infoo($stmt);

Answer 3

好吧，我想出了办法让这件事起作用。首先，科斯塔斯所说的大部分都是解决方案，我并不是想为他的工作取得功劳，但它需要稍微改变，才能正常工作。

首先，我添加了一个函数

function reference($arr){
$refs = array();
foreach($arr as $key => $value)
    $refs[$key] = &$arr[$key];
return $refs;

}

然后我改变了这个(从Kostas的代码)

$stmt->bind_param($bindParam->get());

至

call_user_func_array(array($stmt, 'bind_param'), reference($bindParam->get()));