php - 如何在缺少输入的情况下进行搜索

Question

我正在研究一个搜索栏，旁边有一个连接到数据库的按钮。单击按钮，它将获取搜索栏中的数据，搜索数据库并显示结果。到目前为止，这是有效的。

我想不通的是即使搜索栏中的数据丢失了，如何让它进行搜索。例如，用户在搜索栏中输入“ma”。它仍然会显示诸如“mark”、“marge”、“may”和任何带有“ma”的结果。如何做到这一点？

index.php(搜索栏)

<SCRIPT>
        function loadSearch()
        {
            var xmlhttp;
            if (window.XMLHttpRequest)
                  {// code for IE7+, Firefox, Chrome, Opera, Safari
                  xmlhttp=new XMLHttpRequest();
                  }
            else
                  {// code for IE6, IE5
                  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
                  }
            xmlhttp.onreadystatechange=function()
              {
                  if (xmlhttp.readyState==4 && xmlhttp.status==200)
                    {
                    document.getElementById("myDivs").innerHTML=xmlhttp.responseText;
                    }
              }
            var bar = document.getElementById('sbar').value;

            xmlhttp.open("GET","search.php?search="+bar,true);  
            xmlhttp.send(); 
        }
</SCRIPT>

<span class="searchbox">    
            <input type="text" class="searchbar" id = "sbar" autocomplete="off" placeholder="Search for site..." autocorrect="off" autocapitalize="off">

            </input>
            </span>

搜索.php

<?php
        $getsearch = $_GET['search'];
            $conn = mysqli_connect("localhost", "root", "", "test1");

            // Check connection
        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
                }

            $today = date("Y-m-d");
            $sql = "SELECT name, COUNT(*) as Referrals,
            SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
            SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
            FROM table1
            WHERE name > '' AND name = '".$getsearch."' AND month(date) = MONTH(NOW()) AND year(date) = YEAR(NOW())
            GROUP BY name 
            ORDER BY Referrals DESC";
            $result = mysqli_query($conn, $sql);
        ?>

Answer 1

在您的 sql 中，将 = 运算符替换为带有部分匹配符号 % 的 LIKE 此处我将 % 放在双方，因此它将搜索字符串中的任何位置。

如果您只使用左或右取决于您的结果也会因搜索条件而异。

$sql = "SELECT name, COUNT(*) as Referrals,
            SUM(CASE WHEN leadstatus = 'Hired' THEN 1 ELSE 0 END) as Hired,
            SUM(CASE WHEN leadstatus = 'Failed' THEN 1 ELSE 0 END) as Failed
            FROM table1
            WHERE name > '' AND name LIKE '%".$getsearch."%' AND month(date) = MONTH(NOW()) AND year(date) = YEAR(NOW())
            GROUP BY name 
            ORDER BY Referrals DESC";