参数为空时如何获取所有值

Question

我有一个查询表单，需要从MYSQL的自定义表中获取详细信息。如果该参数留为空白，则应提取所有记录。如果在参数中输入了一个值，则应获取该值的记录。

到目前为止，这是我的代码：

<?php

$host = 'localhost';

$dbname = 'test';

$username = 'test';

$password = 'xxx';

session_start();    

global $wpdb, $current_user; 

$conn = mysqli_connect($host, $username, $password, $dbname);

if (!$conn) {

    die ('Failed to connect to MySQL: ' . mysqli_connect_error());

}

$param_1=mysqli_real_escape_string($conn,$_GET['param_1']);

 if (!empty($param_1)){

  $sql = "SELECT column1 ,column2,column3,column4,column5

            FROM xxx

          WHERE column1='$param_1'";

   } else {

   $sql = 'SELECT column1 ,column2,column3,column4,column5

             FROM xxx';

}

$query = mysqli_query($conn, $sql);

if (!$query) {

    die ('SQL Error: ' . mysqli_error($conn));

}

?>

一个参数可以很好地工作。我将需要添加更多参数，并且这些参数也可以为null。

例如

$sql = "SELECT column1 ,column2,column3,column4,column5

            FROM xxx

          WHERE column1='$param_1' AND column2='$param_2";

这些中的任何一个都可以为null。如何在MYSQL中处理此问题？

我的问题是解决这种情况的最佳方法是什么？

Answer 1

您可以按照以下步骤<?php$host = 'localhost';$dbname = 'test';$username = 'test';$password = 'xxx';session_start();&nbsp; &nbsp;&nbsp;global $wpdb, $current_user;&nbsp;$conn = mysqli_connect($host, $username, $password, $dbname);if (!$conn) {&nbsp; &nbsp; die ('Failed to connect to MySQL: ' . mysqli_connect_error());}$whereArr=[];if(isset($_GET['param_1'])){&nbsp; &nbsp;$whereArr[]="column1=" . mysqli_real_escape_string($conn,$_GET['param_1']);}if(isset($_GET['param_2'])){&nbsp; &nbsp;$whereArr[]="column2=" . mysqli_real_escape_string($conn,$_GET['param_2']);}if(isset($_GET['param_3'])){&nbsp; &nbsp;$whereArr[]="column3=" . mysqli_real_escape_string($conn,$_GET['param_3']);}$whereStr='';if(count($whereArr)>0){&nbsp; &nbsp; &nbsp; $whereStr="WHERE " . implode(" AND ",$whereArr);}&nbsp; $sql = "SELECT column1 ,column2,column3,column4,column5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM xxx " . $whereStr;$query = mysqli_query($conn, $sql);if (!$query) {&nbsp; &nbsp; die ('SQL Error: ' . mysqli_error($conn));}?>检查上面演示的每个参数，将它们放在数组中。然后检查数组是否被isset设置，如果isset创建一个where字符串并将其附加到查询中。即使未设置任何参数，您的查询也将在没有where子句的情况下运行。

Answer 2

您可以执行以下操作来优化代码，$getArr = array_filter($_GET);// checking sql injection$getArr = array_map(function ($v) use ($conn) {&nbsp; &nbsp; return mysqli_real_escape_string($conn, $v);}, $getArr);$temp = [];// fetching numbers for that keyforeach ($getArr as $key => $value) {&nbsp; &nbsp; $temp[$key] = preg_replace('/[^\d]/', '', $key);}$str = '';// creating condition for data fetched in getarray_walk($temp, function ($item, $key) use (&$str, $getArr) {&nbsp; &nbsp; $str .= " column$item = '" . $getArr[$key] . "' AND ";});// raw query$sql = 'SELECT column1 ,column2,column3,column4,column5 FROM xxx';// if not empty stringif (!empty($str)) {&nbsp; &nbsp; $sql .= rtrim($str,'AND ');}echo $sql;die;