在包含多条记录的大表中进行搜索/查询

Question

我创建了一个html+php页面，在其中它在mysql数据库中进行搜索。当我的表包含一些记录时，我可以得到输出，但是当我的表有超过100+记录时，当我搜索时，它将不再显示记录，并显示我的else语句，即"0记录“。

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

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

if(!empty($sfname) || !empty($sgen) || !empty($sdoc) || !empty($smisc) || !empty($ssick) || !empty($sothers) ){

        $genQueryPart = !empty($sgen) ? "Gender LIKE '%$sgen%'" : "";
        $fnameQueryPart = !empty($sfname) ? "FullName LIKE '%$sfname%'" : "";
        $docQueryPart = !empty($sdoc) ? "Doctor LIKE '%$sdoc%'" : "";
        $miscQueryPart = !empty($smisc) ? "Misc LIKE '%$smisc%'" : "";
        $sickQueryPart = !empty($ssick) ? "Sickness LIKE '%$ssick%'" : "";
        $othersQueryPart = !empty($sothers) ? "Others LIKE '%$sothers%'" : "";

        $arr = array($genQueryPart, $fnameQueryPart,$docQueryPart,$miscQueryPart,$sickQueryPart,$othersQueryPart);

        $sql = "select * from index where ";

        $needsAnd = false;
    for ($i = 0; $i < count($arr); $i++) {
        if ($arr[$i] != "") {
            if ($needsAnd) {
                $sql .= " AND ";
            }

            $needsAnd = true;
            $sql .= " " . $arr[$i];
        }

    }

//Get query on the database
    $result = mysqli_query($conn, $sql);
    mysqli_store_result();
    //Check results
    if (mysqli_num_rows($result) > 0)
    {
    //Headers
      echo "<table border='1' style='width:100%'>";
    echo "<tr>";
         echo "<th>File ID</th>"; 
    echo "<th>Full Name</th>";
    echo "<th>Gender</th>";
    echo "<th>Doctor</th>";
    echo "<th>Misc</th>";
    echo "<th>Sickness</th>";
    echo "<th>Others</th>";
    echo "</tr>";

  //output data of each row
      while($row = mysqli_fetch_assoc($result))
        {
        echo "<tr>";
                  echo "<td>".$row['FileID']."</td>";
                  echo "<td>".$row['FullName']."</td>";
                  echo "<td>".$row['Gender']."</td>";
                  echo "<td>".$row['Doctor']."</td>";
                  echo "<td>".$row['Misc']."</td>";
            echo "<td>".$row['Sickness']."</td>";
            echo "<td>".$row['Others']."</td>";
                echo "</tr>";

        }
              echo "</table>";
    }
 else {
        echo "0 results";
    }

} else {
    echo "You must enter at least one value";
}

mysqli_close($conn);

?>

Answer 1

使用if条件和分页在搜索后显示100条以上的记录。计算结果的数量之后，使用分页查询来显示每页的记录.This将是显示大量记录的好主意。不要将Php和Html合并在一起。在一个函数中获取记录，并使用它来集成到HTML中