个人中心
gpt4 book ai didi

php - 在日期的 2 个日期之间搜索以及文本搜索查询

转载 作者：行者123 更新时间：2023-11-29 06:44:30 25 4
gpt4 key购买 nike

我尝试寻找解决方案，我对 PHP 非常陌生，尽管我已经看过，但似乎没有解决方案可以帮助数据格式化。

到目前为止，我还不太担心 SQL 注入(inject)和安全性，因为这是要提交的作业，只是一些关于如何获得我需要的结果的建议。

我有一个页面，我想在数据库中搜索关键字，但我也可以选择添加时间范围搜索。

例如，我可以使用文本搜索来搜索姓名、医生、病情和药物，但我也可以选择(因为我希望能够在不使用日期限制的情况下进行搜索)喜欢使用搜索 appdatefrom 和appdateto 来缩小预约日期。

我遇到错误

SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE visitdate BETWEEN '2001-01-01' and '2015-01-01' ORDER BY visitdate ASC' at line 5

目前在数据库中我的访问日期格式为 2014-04-20 01:23:43

由于时间 TIMESTAMP 格式，我发现很难做到。

数据库如下所示。由于某种原因我无法使用 XAMP 获得良好的 ERD？所以我创建了一个。

Visit Table Schema

Database Schema

页面代码如下。 

    <?php // Include config file
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/functions.php");
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");

$sql  = "SELECT patient.fName AS fname, patient.sName AS surname, doctor.sName AS doc, conditions.con_name AS con, drugs.medication AS meds, visit.visitdate, visit.visit_id AS visitid  FROM visit 
            JOIN patient ON visit.patient_id = patient.patient_id
            JOIN doctor ON visit.doctor_id = doctor.doctor_id
            LEFT JOIN conditions ON visit.con_id = conditions.con_id
            LEFT JOIN drugs ON visit.drugs_id = drugs.med_id";

if (isset($_POST['search'])) {

$search_term = ($_POST['searchapp']);
$appdatefrom = ($_POST['appdatefrom']);
$appdateto = ($_POST['appdateto']);

$sql .= " WHERE patient.fName LIKE '%".$search_term."%'";
$sql .= " OR patient.sName LIKE '%".$search_term."%'";
$sql .= " OR doctor.sName LIKE '%".$search_term."%'";
$sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
$sql .= " OR drugs.medication LIKE '%".$search_term."%'";
$sql .= " WHERE visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
$sql .= " ORDER BY visitdate ASC";
}

$query = mysqli_query($db, $sql);

if (!$query) {
  die ('SQL Error: ' . mysqli_error($db));
  }

?>

<body>
<div class="container"><br><br>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>

  <h2>APPOINTMENTS</h2>

  <p>Search Recent Appointments:</p>      

    <form name="searchform" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
                    <div class="form-group row">
                    <div class="col-xs-4">
                            <label>Search for Patient, Doctor, Medication or Condition</label> 
                            <input type="text"  class="form-control" name="searchapp" placeholder="Example. Dr Mears, Tonsillitis, Vimovo, Andrew" required><br>
                    </div>   
                    <div class="col-xs-4">                         
                            <label>Date From:</label> 
                            <input type="date"  class="form-control" name="appdatefrom"><br>
                    </div>   
                    <div class="col-xs-4"> 
                            <label>Date To:</label> 
                            <input type="date"  class="form-control" name="appdateto"><br> 
                    </div>   
                    <div class="col-xs-4">                            
                            <input type="submit" class="btn btn-primary" name="search" value="Submit">
                            <span class="help-block"></span>
                            </div>
                        </div>      



  <table class="table table-striped">
  <thead>
      <tr>
        <th>Patient Name</th>
        <th>Doctor</th>
        <th>Condition</th>
        <th>Medication Prescribed</th>
        <th>Visit ID</th>
        <th>Date</th>
        <th></th>
      </tr>
    </thead>
    <tbody>
    <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query)) 
        {
            echo '<tr>
                    <td>'.$row['fname']." ".$row['surname'].'</td> 
                    <td>'."Dr ".$row['doc'].'</td> 
                    <td>'.$row['con'].'</td> 
                    <td>'.$row['meds'].'</td> 
                    <td>'.$row['visitid'].'</td>
                    <td>'.$row['visitdate'].'</td>
                    <td><a href="viewapp.php?id='.($row['visitid']).'" class="btn btn-warning pull-right btn-xs">View</a></td> 
                    <td><a href="delapp.php?id='.($row['visitid']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
                    </tr>';    
            $no++;
        }?>
    </tbody>
  </table>
  <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  <a href="" class="btn btn-info pull-left">Refesh</a>
  <a href="../" class="btn btn-info pull-right">Admin Area</a>
</div>
<div class="bottompadding"></div>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/footer.php"); ?>
</body>
</html>

查看给出的答案后，在代码中添加括号使其无法工作，它目前的工作方式如下，但没有日期搜索？

这就是网站当前的样子。当输入我的姓氏时，它会显示我的结果，但如果我想在两个日期之间进行搜索，它不会执行任何操作。 Screen Capture 

<?php // Include config file
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/functions.php");
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");

$sql  = "SELECT patient.fName AS fname, patient.sName AS surname, doctor.sName AS doc, conditions.con_name AS con, drugs.medication AS meds, visit.visitdate, visit.visit_id AS visitid  FROM visit 
            JOIN patient ON visit.patient_id = patient.patient_id
            JOIN doctor ON visit.doctor_id = doctor.doctor_id
            LEFT JOIN conditions ON visit.con_id = conditions.con_id
            LEFT JOIN drugs ON visit.drugs_id = drugs.med_id";

if (isset($_POST['search'])) {

$search_term = ($_POST['searchapp']);
$appdatefrom = ($_POST['appdatefrom']);
$appdateto = ($_POST['appdateto']);

$sql .= " WHERE patient.fName LIKE '%".$search_term."%'";
$sql .= " OR patient.sName LIKE '%".$search_term."%'";
$sql .= " OR doctor.sName LIKE '%".$search_term."%'";
$sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
$sql .= " OR drugs.medication LIKE '%".$search_term."%'";
$sql .= " AND visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
$sql .= " ORDER BY visitdate ASC";
}

$query = mysqli_query($db, $sql);

if (!$query) {
  die ('SQL Error: ' . mysqli_error($db));
  }

?>

<body>
<div class="container"><br><br>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>

  <h2>APPOINTMENTS</h2>

  <p>Search Recent Appointments:</p>      

    <form name="searchform" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
                    <div class="form-group row">
                    <div class="col-xs-4">
                            <label>Search for Patient, Doctor, Medication or Condition</label> 
                            <input type="text"  class="form-control" name="searchapp" placeholder="Example. Dr Mears, Tonsillitis, Vimovo, Andrew" required><br>
                    </div>   
                    <div class="col-xs-4">                         
                            <label>Date From:</label> 
                            <input type="date"  class="form-control" name="appdatefrom"><br>
                    </div>   
                    <div class="col-xs-4"> 
                            <label>Date To:</label> 
                            <input type="date"  class="form-control" name="appdateto"><br> 
                    </div>   
                    <div class="col-xs-4">                            
                            <input type="submit" class="btn btn-primary" name="search" value="Submit">
                            <span class="help-block"></span>
                            </div>
                        </div>      



  <table class="table table-striped">
  <thead>
      <tr>
        <th>Patient Name</th>
        <th>Doctor</th>
        <th>Condition</th>
        <th>Medication Prescribed</th>
        <th>Visit ID</th>
        <th>Date</th>
        <th></th>
      </tr>
    </thead>
    <tbody>
    <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query)) 
        {
            echo '<tr>
                    <td>'.$row['fname']." ".$row['surname'].'</td> 
                    <td>'."Dr ".$row['doc'].'</td> 
                    <td>'.$row['con'].'</td> 
                    <td>'.$row['meds'].'</td> 
                    <td>'.$row['visitid'].'</td>
                    <td>'.$row['visitdate'].'</td>
                    <td><a href="viewapp.php?id='.($row['visitid']).'" class="btn btn-warning pull-right btn-xs">View</a></td> 
                    <td><a href="delapp.php?id='.($row['visitid']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
                    </tr>';    
            $no++;
        }?>
    </tbody>
  </table>
  <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  <a href="" class="btn btn-info pull-left">Refesh</a>
  <a href="../" class="btn btn-info pull-right">Admin Area</a>
</div>
<div class="bottompadding"></div>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/footer.php"); ?>
</body>
</html>

最佳答案

每个查询只能有一个 WHERE 子句。如果要将日期范围与搜索结合起来，请使用 AND 和一组带括号的 OR 条件。

$sql .= " WHERE visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
$sql .= " AND (patient.fName LIKE '%".$search_term."%'";
$sql .= " OR patient.sName LIKE '%".$search_term."%'";
$sql .= " OR doctor.sName LIKE '%".$search_term."%'";
$sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
$sql .= " OR drugs.medication LIKE '%".$search_term."%')";
$sql .= " ORDER BY visitdate ASC";

使用括号将指定 a AND b OR c OR d 将被计算为 a AND (b OR c OR d) 而不是 (a AND b) 或 c 或 d。

关于php - 在日期的 2 个日期之间搜索以及文本搜索查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50103722/

25 4 0
文章推荐： php - 为什么数组进入我准备好的 sql 语句之一而不是另一个
文章推荐： mysql - 如何在 rails 应用程序中将大型 csv 文件导入 mysql？
文章推荐： mysql - 从数据库中获取每天和每月的总销售额
文章推荐： mysql 表更改 phpmyadmin 中每个页面刷新的行数
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com