添加搜索字段 - Add search field

<form method="post" action="oabtest.php?go" id="searchform">
    <input type="text" name="name">
    <input type="submit" name="submit" value="Search">
</form>
<p><a href="?by=A">A</a> | <a href="?by=B">B</a> | <a href="?by=C">C</a> |<a href="?by=D">D</a> |<a href="?by=E">E</a> |<a href="?by=F">F</a> |<a href="?by=G">G</a> |<a href="?by=H">H</a> |<a href="?by=I">I</a> |<a href="?by=J">J</a> |<a href="?by=K">K</a> |<a href="?by=L">L</a> |<a href="?by=M">M</a> |<a href="?by=N">N</a> |<a href="?by=O">O</a> |<a href="?by=P">P</a> |<a href="?by=Q">Q</a> |<a href="?by=R">R</a> |<a href="?by=S">S</a> |<a href="?by=T">T</a> |<a href="?by=U">U</a> |<a href="?by=V">V</a> |<a href="?by=W">W</a> |<a href="?by=X">X</a> |<a href="?by=Y">Y</a> |<a href="?by=Z">Z</a> </p>
<p>You may also search by Patrol.</p>
<form method="post" action="oabtest.php?go" id="searchform">
    <input type="text" name="patrol">
    <input type="submit" name="submit" value="Search">
</form>
<?php
//Include database connection details
require_once('config.php');
//Array to store validation errors
$errmsg_arr = array();
//Validation error flag
$errflag = false;
//Connect to mysql server
$link = mysql_connect("localhost", "*****", "*****");
if (!$link) {
    die('Failed to connect to server: ' . mysql_error());
}
//Select database
$db = mysql_select_db("troop97_***");
if (!$db) {
    die("Unable to select database");
}
if (isset($_POST['submit'])) {
    if (isset($_GET['go'])) {
        if (preg_match("/[A-Z | a-z]+/", $_POST['name'])) {
            $name = $_POST['name'];

//-query the database table
            $sql = "SELECT ID, First_Name, Last_Name FROM contact WHERE First_Name LIKE '" . mysql_real_escape_string($name) . "%' OR Last_Name LIKE '" . mysql_real_escape_string($name) . "%'";
//-run the query against the mysql query function
            $result = mysql_query($sql);
//-count results
            $numrows = mysql_num_rows($result);
            echo "<p>" . $numrows . " results found for " . stripslashes($name) . "</p>";
//-create while loop and loop through result set
            while ($row = mysql_fetch_array($result)) {
                $First_Name = $row['First_Name'];
                $Last_Name = $row['Last_Name'];
                $ID = $row['ID'];
//-display the result of the array
                echo "<ul>'n";
                echo "<li>" . "<a href='"oabtest.php?id=$ID'">" . $First_Name . " " . $Last_Name . "</a></li>'n";
                echo "</ul>";
            }
        } else {
            echo "<p>Please enter a search query</p>";
        }
    }
}
if (isset($_GET['by'])) {
    $letter = $_GET['by'];
//-query the database table
    $letter = mysql_real_escape_string($letter);
    $sql = "SELECT ID, First_Name, Last_Name FROM contact WHERE First_Name LIKE '" . $letter . "%' 
OR Last_Name LIKE '" . $letter . "%'";
//-run the query against the mysql query function
    $result = mysql_query($sql);
//-count results
    $numrows = mysql_num_rows($result);
    echo "<p>" . $numrows . " results found for " . $letter . "</p>";
//-create while loop and loop through result set
    while ($row = mysql_fetch_array($result)) {
        $First_Name = $row['First_Name'];
        $Last_Name = $row['Last_Name'];
        $ID = $row['ID'];
//-display the result of the array
        echo "<ul>'n";
        echo "<li>" . "<a href='"oabtest.php?id=$ID'">" . $First_Name . " " . $Last_Name . "</a></li>'n";
        echo "</ul>";
    }
}
if (isset($_POST['submit'])) {
    if (isset($_GET['go'])) {
        if (preg_match("/[A-Z | a-z]+/", $_POST['patrol'])) {
            $patrol = $_POST['patrol'];

//-query the database table
            $patrol = mysql_real_escape_string($patrol);
            $sql = "SELECT ID, First_Name, Last_Name FROM contact WHERE Patrol LIKE '" . mysql_real_escape_string($patrol) . "%'";
//-run the query against the mysql query function
            $result = mysql_query($sql);
//-count results
            $numrows = mysql_num_rows($result);
            echo "<p>" . $numrows . " results found for " . $patrol . "</p>";
//-create while loop and loop through result set
            while ($row = mysql_fetch_array($result)) {
                $First_Name = $row['First_Name'];
                $Last_Name = $row['Last_Name'];
                $ID = $row['ID'];
//-display the result of the array
                echo "<ul>'n";
                echo "<li>" . "<a href='"oabtest.php?id=$ID'">" . $First_Name . " " . $Last_Name . "</a></li>'n";
                echo "</ul>";
            }
        }
        if (isset($_GET['id'])) {
            $contactid = $_GET['id'];
//-query the database table
            $sql = "SELECT * FROM contact WHERE ID=" . $contactid;

//-run the query against the mysql query function
            $result = mysql_query($sql);
//-create while loop and loop through result set
            while ($row = mysql_fetch_array($result)) {
                $First_Name = $row['First_Name'];
                $Last_Name = $row['Last_Name'];
                $Home_Phone = $row['Home_Phone'];
                $Cell_Phone = $row['Cell_Phone'];
                $Work_Phone = $row['Work_Phone'];
                $Email = $row['Email'];
                $Home_Street = $row['Home_Street'];
                $Home_City = $row['Home_City'];
                $Home_State = $row['Home_State'];
                $Home_Zip = $row['Home_Zip'];
                $Troop_Role = $row['Troop_Role'];
                $Patrol = $row['Patrol'];
//-display the result of the array
                echo "<ul>'n";
                echo "<li>" . $First_Name . " " . $Last_Name . "</li>'n";
                echo (empty($Home_Phone)) ? '' : "<li>" . $Home_Phone . " Home</li>'n";
                echo (empty($Cell_Phone)) ? '' : "<li>" . $Cell_Phone . " Cell</li>'n";
                echo (empty($Work_Phone)) ? '' : "<li>" . $Work_Phone . " Work</li>'n";
                echo "<li>" . "<a href=mailto:" . $Email . ">" . $Email . "</a></li>'n";
                echo "<li>" . $Home_Street . "</li>'n";
                echo "<li>" . $Home_City . ", " . $Home_State . " " . $Home_Zip . "</li>'n";
                echo "<li>" . $Troop_Role . "</li>'n";
                echo "<li>" . $Patrol . "</li>'n";
                echo "</ul>";
            }
        }
    }
}

SQL注入风险

如果您在与数据库交互时使用了来自提交表单的值，则应该在这样做之前转义该内容。在MySQL中，最好的函数是mysql_real_escape_string() PHP Manual

$sql="SELECT ID, First_Name, Last_Name FROM contact WHERE First_Name LIKE '" . mysql_real_escape_string( $name ) . "%' OR Last_Name LIKE '" . mysql_real_escape_string( $name ) ."%'";

添加字段到搜索

如果您想要添加一个额外的字段，比如"Department"到搜索查询中，您只需在搜索表单中添加一个与之对应的字段，然后调整您的SQL search，使其包含在WHERE子句中:

$sql="SELECT ID, First_Name, Last_Name
      FROM contact
      WHERE ( First_Name LIKE '" . mysql_real_escape_string( $name ) . "%'
              OR Last_Name LIKE '" . mysql_real_escape_string( $name ) ."%' )
            AND Department='" . mysql_real_escape_string( $department ) ."'";

使用一个字段进行两个搜索

如果希望使用单个文本字段执行上述搜索，则需要为用户确定某种前缀，以便在第二个字段的值前面加上。

例如，如果我们指定"in:"作为指定部门的前缀，那么搜索"John in:Radiology"将查找任何名字以"John"开头的人，但只查找"Radiology"部门的人。

list( $name , $department ) = explode( ' in:' , $_POST['name'] , 2 );

不是

$name = $_POST['name'];

类搜索限制

目前，您的代码将只搜索以输入值开头的First Names和/或Last Names。通过在搜索字符串的开头加上另一个"%"，可以使搜索返回仅包含(而不仅仅是以输入值开始)的字段:

$sql="SELECT ID, First_Name, Last_Name FROM contact WHERE First_Name LIKE '%" . $name . "%' OR Last_Name LIKE '%" . $name ."%'";

全文搜索

你可能想看看这个教程-使用MySQL全文搜索。它涵盖了全文搜索的概念，将允许您在多个数据库字段中找到通过单个字段提交的一个或多个单词。

限制返回的行数

限制为搜索返回的行数总是一个好主意，无论您是分页还是仅仅显示X行。如果不这样做，恶意用户就可以通过简单地搜索字母表中的每个字母来抓取整个数据库。

添加您的假设字段说search_field，然后用"SELECT * FROM contact WHERE search_field='search value' order by First_Name"搜索它，不要忘记索引search_field，如果它将是一个唯一的字段，如电子邮件。我希望您上面粘贴的代码不会进入生产环境。不要相信用户输入并在SQL查询中使用它们之前对它们进行适当过滤，更不用说将db凭据和连接字符串存储在单独的文件中并包含它。