PHP MySQL搜索使用多个文本框来搜索多个列 - PHP MySQL search using multiple text boxes to search multiple columns

PHP MySQL search using multiple text boxes to search multiple columns

本文关键字：搜索文本 MySQL PHP | 更新日期: 2023-09-27

我正在学习PHP，并致力于在MySQL数据库中搜索书籍的项目。用户应该能够按书名、图书作者和类别进行搜索，使用全部、一个或三者的任意组合。

目前这是我的代码：

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Welcome to Library Management System</title>
<link href="main.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php
require_once "db.php";
include "header.html";

if(isset($_POST["bookTitle"]))
{
$bookTitle = mysqli_real_escape_string($con, $_POST["bookTitle"]);
}
else
{
$bookTitle = NULL;
}
if(isset($_POST["bookAuthor"]))
{
$bookAuthor = mysqli_real_escape_string($con, $_POST["bookAuthor"]);
}
else
{
$bookAuthor = NULL;
}
if(isset($_POST["category"]))
{
$category = mysqli_real_escape_string($con, $_POST["category"]);
}
else
{
$category= NULL;
}

echo "Results by Book Title Search";
$bookTitle = mysqli_real_escape_string($con, $_POST["bookTitle"]);
$query = "Select * From book NATURAL JOIN category where category.CategoryDesc LIKE '%" .$category ."%' OR book.BookTitle LIKE '%" .$bookTitle ."%' OR book.Author LIKE '%" .$bookAuthor."%'";
$result=mysqli_query($con, $query) or die(mysqli_error());
echo '<table border="1" width="95%">'."'n";
echo "<tr><th>ISBN</th><th>Title</th><th>Author</th><th>Edition</th><th>Year</th><th>Category ID</th><th>Reserved</th><th>Reserve?</th><tr>";
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)){
echo "<tr><td>";
echo(htmlentities($row[0]));
echo("</td><td>");
echo(htmlentities($row[1]));
echo("</td><td>");
echo(htmlentities($row[2]));
echo("</td><td>'n");
echo(htmlentities($row[3]));
echo("</td><td>'n");
echo(htmlentities($row[4]));
echo("</td><td>'n");
echo(htmlentities($row[5]));
echo("</td><td>'n");
echo(htmlentities($row[6]));
echo("</td><td>'n");
echo('<a href="edit.php?id='.htmlentities($row[1]).'">Edit</a> 
/ ');
echo('<a 
href="delete.php?id='.htmlentities($row[1]).'">Delete</a>');
echo("</td></tr>'n");
}
echo "</br>";

如果我使用所有三个字段进行搜索，那么查询将返回相关结果。如果一个或多个字段为空，则返回整个数据库，这不是我想要的。

有更好的方法吗？

您可以使用此

$condition="sasaaa";
$bookTitle=trim($_POST['bookTitle']);
$bookAuthor=trim($_POST['bookAuthor']);
$category=trim($_POST['category']);
if(isset($bookTitle))
   $condition="booktitle=$bookTitle";
if(isset($bookAuthor))
   $condition="bookAuthor=$bookAuthor";
if(isset($category))
   $condition="category=$category";

并在您的SQl中使用这个$condition变量。使用mysqli_real_escape_string（）。希望它能帮助你：）

最好一开始就跳过所有测试，简单地动态构建查询，只在设置后变量时设置where条件。但是，如果您希望保留这种逻辑（这不是很好），只需将NULL值替换为空字符串，就可以了。。。