搜索两个日期之间从日期以及文本搜索查询

zbdgwd5y  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(382)

我试过寻找一个解决方案，我对php非常陌生，虽然我已经看过了，但似乎没有一个解决方案可以帮助格式化数据。
我不太关心sql注入和安全性，因为这是一个任务交，只是一些关于如何得到我需要的结果的建议。
我有一个网页，我想在数据库中搜索关键字，但也可选我想添加一个时间框架搜索。
例如，我可以使用文本搜索来搜索姓名、医生、病情和药物，但我也可以选择（因为我希望能够在不使用日期限制的情况下进行搜索）使用搜索appdatefrom和appdateto来缩小预约日期。
我到那儿去了
sql错误：您的sql语法有错误；请查看与您的mariadb服务器版本相对应的手册，以获取在第5行“where visitdate between'2001-01-01'and'2015-01-01'order by visitdate asc”附近使用的正确语法
目前在数据库中，我的访问日期格式为2014-04-20 01:23:43
我发现这很难做到，由于时间戳格式。
数据库如下所示。不知为什么我不能和xamp一起得到一个好的erd？所以我创造了一个。
访问表架构
数据库架构
页面代码如下。

<?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>

在查看给出的答案后，在代码中添加parenthasis会阻止它工作，它当前是这样工作的，但是没有日期搜索？
这是网站目前的样子。当输入我的姓氏时，它会显示我的结果，但如果我想在两个日期之间搜索，它就什么都不做。屏幕截图

<?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>
mysqlphp

1条答案

按热度按时间
cuxqih21

cuxqih211#

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

$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) OR c OR d .

赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com