PHP- MySQL搜索标准

发布时间：2020-12-13 17:10:43 所属栏目：PHP教程来源：网络整理

导读：我开发了一个包含多个字段的数据库 – 大约65个字段.我希望为我的用户提供多个搜索条件,即.通过在下拉菜单中选择各种字段名称.我无法通过使用简单的IF-ELSEIF-ELSE语句来执行它.它只给出了第一个IF语句的结果,而忽略了其余的结果. 以下是html页面： form nam

我开发了一个包含多个字段的数据库 – 大约65个字段.我希望为我的用户提供多个搜索条件,即.通过在下拉菜单中选择各种字段名称.我无法通过使用简单的IF-ELSEIF-ELSE语句来执行它.它只给出了第一个IF语句的结果,而忽略了其余的结果.

以下是html页面：

<form name="frm" action="search_plants.php" method="get">
            <table>
                <tr>
                    <td width="155" height="24">Select Scientific Name:</td>
      <td width="275">
                        <select name="spnm" size="1">       
                        <option value="0">  All</option>    
                        <option value="Abrus precatorius">  Abrus precatorius</option>  
                        <option value="Abutilon indicum">  Abutilon indicum</option>                    
                        <option value="Abutilon theophrasti"> Abutilon theophrasti</option>                 



                        </select>
                        </td>
              </tr>
                <tr>
                    <td>Select Family Name:</td>
                    <td>
                        <select name="fmnm" size="1">   
                        <option value="0">  All</option>
                        <option value="Fabaceae">Fabaceae</option>
                        <option value="Malvaceae">Malvaceae</option>
                        <option value="Mimosaceae">Mimosaceae</option>                      

                        </select>
                    </td>
                </tr>
                <tr>
                    <td>Select Geographic Location:</td>
                    <td>
                        <select name="znm" size="1">                    
                        <option value="0">  All</option>
                        <option value="North Gujarat">North Gujarat</option>
                        <option value="South Gujarat">South Gujarat</option>
                        <option value="South East Gujarat">South East Gujarat</option>      
                        <option value="Central Gujarat">Central Gujarat</option>
                        <option value="Kachchh">Kachchh</option>
                        <option value="Saurashtra">Saurashtra</option>

                        </select>
                    </td>
                </tr>
                <tr>
                <tr>
                    <td>Select Marker:</td>
                    <td>
                        <select name="pnm" size="1">                    
                        <option value="0">  All</option>
                        <option value="rbcL">rbcL</option>      
                        <option value="psbA-trnH">psbA-trnH</option>    
                        <option value="matK">matK</option>  
                        <option value="rpoC1">rpoC1</option>    
                        <option value="ITS2">ITS2</option>  
                        <option value="ycf5">ycf5</option>      

                        </select>
                    </td>
                </tr>
                <tr>
                    <td>
                        <input type="submit" value="Go" name="action">
                        <label>
                        <input type="reset" name="Reset" id="button" value="Reset">
                        </label>                        </td>
                </tr>
            </table>
        </form>

表单操作页面如下,仅描绘整个编码的一部分.我想给用户一个选择任何/所有字段的选项,并能够通过从数据库中选择性搜索来显示它.请帮帮我.

if (($species == 0) || ($family == 0) || ($zones ==0 ) || ($marker == 0))   
    {
        if(($species==0) && ($family==0) && ($zones==0))
        {
            $sql="SELECT * FROM `ncbi_bold_ plants` WHERE Marker LIKE '$marker%'" ;
        }

        elseif($species==0 &&  $family==0 &&  $marker==0)
        {
        $sql="SELECT * FROM `ncbi_bold_ plants` WHERE Zones LIKE '%$zones%'";
        }

        elseif($family==0 &&  $zones==0 &&  $marker==0)
        {
        $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%'";

        }

        elseif($species==0 &&  $zones==0 &&  $marker==0)
        {
            $sql="select * from `ncbi_bold_ plants` where Family like '$family%'";

        }
    /*
    elseif($species==0 && $marker==0)
    {
        $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND Zones like '%$zones%'";
        $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");
    }       
    elseif($family==0 && $zones==0 && $marker==0)
    {
        $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%'";
        $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");
    }
    elseif($species==0 && $family==0)
    {
        $sql="select * from `ncbi_bold_ plants` where Zones like '%$zones%' AND marker like '$marker%'";
        $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");
    }
    elseif($species==0 && $zones==0)
    {
        $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND marker like '$marker%'";
        $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");
    }
    elseif($species==0 && $marker==0)
    {
        $sql="select * from `ncbi_bold_ plants` where Family like '$family%' AND Zones like '%$zones%'";
        $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");
    }
    */          
        else
        {
        $sql="SELECT * FROM `ncbi_bold_ plants` " ;
        }

    }

    else 
    {
        $sql="select * from `ncbi_bold_ plants` where Speciesname like '$species%' and Family like '$family%' and Zones like '%$zones%' and Marker like '$marker%'";


    }

    $search_sql=mysql_query($sql) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$sql}");

请帮我一样.我无法在if语句中找到缺陷.

解决方法

试试这个简单的功能：

function create_query($p_species,$p_family,$p_zones,$p_marker) {
  $options = array(
    'Speciesname' => $p_species,'Family' => $p_family,'Zones' => $p_zones,'Marker' => $p_marker
    );
  $cond = '';
  $noopt = true;
  foreach ($options as $column => $value) {
    if ($value !== 0) {
      $noopt = false;
      if ($cond != '') $cond .= ' AND ';
      $cond .= "$column LIKE '%$value%'";
      }
    }
  return $noopt ? false : "SELECT * FROM ncbi_bold_ plants WHERE $cond;";
  }

如果所有参数都为零,则此函数返回false,否则返回查询字符串.

示例#1：

$species = 'spectest';
$family = 0;
$zones = 'zonestest';
$marker = 0;

echo create_query($species,$family,$zones,$marker);

输出：

SELECT * FROM ncbi_bold_ plants WHERE Speciesname LIKE '%spectest%' AND Zones LIKE '%zonestest%';

示例#2：

$species = 0;
$family = 0;
$zones = 0;
$marker = 0;

var_dump(create_query($species,$marker));

输出：

bool(false)

或者,如果要在没有条件而不是false的情况下返回查询字符串,只需将函数中的返回行替换为：

return $noopt ? "SELECT * FROM ncbi_bold_ plants;" : "SELECT * FROM ncbi_bold_ plants WHERE $cond;";

现在,如果你想用大量的IF,ELSEIF和ELSE语句来做这个,那就有解决方案：

解决方案无功能

(它的工作方式与上面的功能相同)：

if ($species == 0 && $family == 0 && $zones == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants";
    }
  elseif ($species == 0 && $family == 0 && $zones == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Marker LIKE '$marker%'";
    }
  elseif ($species == 0 && $family == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%'";
    }
  elseif ($species == 0 && $zones == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%'";
    }
  elseif ($family == 0 && $zones == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%'";
    }
  elseif ($species == 0 && $family == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
    }
  elseif ($species == 0 && $zones == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Marker LIKE '$marker%'";
    }
  elseif ($species == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%'";
    }
  elseif ($family == 0 && $zones == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Marker LIKE '$marker%'";
    }
  elseif ($family == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%'";
    }
  elseif ($zones == 0 && $marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%'";
    }
  elseif ($species == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
    }
  elseif ($family == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
    }
  elseif ($zones == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Marker LIKE '$marker%'";
    }
  elseif ($marker == 0) {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%'";
    }
  else {
    $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'";
    }

  // here you have your $sql ... now do whatever you want with your query
  echo $sql;

此代码已针对所有可能的情况进行了测试,因此如果它仍然不起作用,则问题不在上面发布的PHP示例中.我不推荐这种方式,因为这是完全错误的方法,即使对于初学者也是如此.

如果您仍想使用IF / ELSE / ELSEIF,请考虑检查IF变量IS NOT EQUAL为零的条件.您将获得更少的代码和更容易的实现.像这样的东西：

不同的方法

$species = 0;
$family = 0;
$zones = 0;
$marker = 0;

// default query w/out conditions
$sql = "SELECT * FROM ncbi_bold_plants";

// conditions array
$conditions = array();

// adding condition to array for every parameter <> 0
if ($species != 0) $conditions[] = "Speciesname LIKE '$species%'";
if ($family != 0) $conditions[] = "Family LIKE '$family%'";
if ($zones != 0) $conditions[] = "Zones LIKE '$zones%'";
if ($marker != 0) $conditions[] = "Marker LIKE '$marker%'";

// all we need now is to concatenate array elements with " AND " glue
$sql_cond = join(" AND ",$conditions);

// last thing,adding condition(s) to the main query (if there's any)
if ($sql_cond != '') $sql .= " WHERE $sql_cond";

// let see what we have now
echo $sql;

前4行仅用于测试.现在,我们有与之前相同的解决方案,但代码较少(仅限8行).

希望这可以帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!