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

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

我试过寻找一个解决方案，我对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？所以我创造了一个。
访问表架构
数据库架构
页面代码如下。

  1. <?php // Include config file
  2. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
  3. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/functions.php");
  4. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");
  6. $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 
  7.             JOIN patient ON visit.patient_id = patient.patient_id
  8.             JOIN doctor ON visit.doctor_id = doctor.doctor_id
  9.             LEFT JOIN conditions ON visit.con_id = conditions.con_id
  10.             LEFT JOIN drugs ON visit.drugs_id = drugs.med_id";
  12. if (isset($_POST['search'])) {
  14. $search_term = ($_POST['searchapp']);
  15. $appdatefrom = ($_POST['appdatefrom']);
  16. $appdateto = ($_POST['appdateto']);
  18. $sql .= " WHERE patient.fName LIKE '%".$search_term."%'";
  19. $sql .= " OR patient.sName LIKE '%".$search_term."%'";
  20. $sql .= " OR doctor.sName LIKE '%".$search_term."%'";
  21. $sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
  22. $sql .= " OR drugs.medication LIKE '%".$search_term."%'";
  23. $sql .= " WHERE visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
  24. $sql .= " ORDER BY visitdate ASC";
  25. }
  27. $query = mysqli_query($db, $sql);
  29. if (!$query) {
  30.   die ('SQL Error: ' . mysqli_error($db));
  31.   }
  33. ?>
  35. <body>
  36. <div class="container"><br><br>
  37. <?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>
  39.   <h2>APPOINTMENTS</h2>
  41.   <p>Search Recent Appointments:</p>      
  43.     <form name="searchform" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
  44.                     <div class="form-group row">
  45.                     <div class="col-xs-4">
  46.                             <label>Search for Patient, Doctor, Medication or Condition</label> 
  47.                             <input type="text"  class="form-control" name="searchapp" placeholder="Example. Dr Mears, Tonsillitis, Vimovo, Andrew" required><br>
  48.                     </div>   
  49.                     <div class="col-xs-4">                         
  50.                             <label>Date From:</label> 
  51.                             <input type="date"  class="form-control" name="appdatefrom"><br>
  52.                     </div>   
  53.                     <div class="col-xs-4"> 
  54.                             <label>Date To:</label> 
  55.                             <input type="date"  class="form-control" name="appdateto"><br> 
  56.                     </div>   
  57.                     <div class="col-xs-4">                            
  58.                             <input type="submit" class="btn btn-primary" name="search" value="Submit">
  59.                             <span class="help-block"></span>
  60.                             </div>
  61.                         </div>      
  63.   <table class="table table-striped">
  64.   <thead>
  65.       <tr>
  66.         <th>Patient Name</th>
  67.         <th>Doctor</th>
  68.         <th>Condition</th>
  69.         <th>Medication Prescribed</th>
  70.         <th>Visit ID</th>
  71.         <th>Date</th>
  72.         <th></th>
  73.       </tr>
  74.     </thead>
  75.     <tbody>
  76.     <?php
  77.         $no     = 1;
  78.         $total  = 0;
  79.         while ($row = mysqli_fetch_array($query)) 
  80.         {
  81.             echo '<tr>
  82.                     <td>'.$row['fname']." ".$row['surname'].'</td> 
  83.                     <td>'."Dr ".$row['doc'].'</td> 
  84.                     <td>'.$row['con'].'</td> 
  85.                     <td>'.$row['meds'].'</td> 
  86.                     <td>'.$row['visitid'].'</td>
  87.                     <td>'.$row['visitdate'].'</td>
  88.                     <td><a href="viewapp.php?id='.($row['visitid']).'" class="btn btn-warning pull-right btn-xs">View</a></td> 
  89.                     <td><a href="delapp.php?id='.($row['visitid']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
  90.                     </tr>';    
  91.             $no++;
  92.         }?>
  93.     </tbody>
  94.   </table>
  95.   <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  96.   <a href="" class="btn btn-info pull-left">Refesh</a>
  97.   <a href="../" class="btn btn-info pull-right">Admin Area</a>
  98. </div>
  99. <div class="bottompadding"></div>
  100. <?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/footer.php"); ?>
  101. </body>
  102. </html>

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

  1. <?php // Include config file
  2. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
  3. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/functions.php");
  4. include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");
  6. $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 
  7.             JOIN patient ON visit.patient_id = patient.patient_id
  8.             JOIN doctor ON visit.doctor_id = doctor.doctor_id
  9.             LEFT JOIN conditions ON visit.con_id = conditions.con_id
  10.             LEFT JOIN drugs ON visit.drugs_id = drugs.med_id";
  12. if (isset($_POST['search'])) {
  14. $search_term = ($_POST['searchapp']);
  15. $appdatefrom = ($_POST['appdatefrom']);
  16. $appdateto = ($_POST['appdateto']);
  18. $sql .= " WHERE patient.fName LIKE '%".$search_term."%'";
  19. $sql .= " OR patient.sName LIKE '%".$search_term."%'";
  20. $sql .= " OR doctor.sName LIKE '%".$search_term."%'";
  21. $sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
  22. $sql .= " OR drugs.medication LIKE '%".$search_term."%'";
  23. $sql .= " AND visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
  24. $sql .= " ORDER BY visitdate ASC";
  25. }
  27. $query = mysqli_query($db, $sql);
  29. if (!$query) {
  30.   die ('SQL Error: ' . mysqli_error($db));
  31.   }
  33. ?>
  35. <body>
  36. <div class="container"><br><br>
  37. <?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>
  39.   <h2>APPOINTMENTS</h2>
  41.   <p>Search Recent Appointments:</p>      
  43.     <form name="searchform" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
  44.                     <div class="form-group row">
  45.                     <div class="col-xs-4">
  46.                             <label>Search for Patient, Doctor, Medication or Condition</label> 
  47.                             <input type="text"  class="form-control" name="searchapp" placeholder="Example. Dr Mears, Tonsillitis, Vimovo, Andrew" required><br>
  48.                     </div>   
  49.                     <div class="col-xs-4">                         
  50.                             <label>Date From:</label> 
  51.                             <input type="date"  class="form-control" name="appdatefrom"><br>
  52.                     </div>   
  53.                     <div class="col-xs-4"> 
  54.                             <label>Date To:</label> 
  55.                             <input type="date"  class="form-control" name="appdateto"><br> 
  56.                     </div>   
  57.                     <div class="col-xs-4">                            
  58.                             <input type="submit" class="btn btn-primary" name="search" value="Submit">
  59.                             <span class="help-block"></span>
  60.                             </div>
  61.                         </div>      
  63.   <table class="table table-striped">
  64.   <thead>
  65.       <tr>
  66.         <th>Patient Name</th>
  67.         <th>Doctor</th>
  68.         <th>Condition</th>
  69.         <th>Medication Prescribed</th>
  70.         <th>Visit ID</th>
  71.         <th>Date</th>
  72.         <th></th>
  73.       </tr>
  74.     </thead>
  75.     <tbody>
  76.     <?php
  77.         $no     = 1;
  78.         $total  = 0;
  79.         while ($row = mysqli_fetch_array($query)) 
  80.         {
  81.             echo '<tr>
  82.                     <td>'.$row['fname']." ".$row['surname'].'</td> 
  83.                     <td>'."Dr ".$row['doc'].'</td> 
  84.                     <td>'.$row['con'].'</td> 
  85.                     <td>'.$row['meds'].'</td> 
  86.                     <td>'.$row['visitid'].'</td>
  87.                     <td>'.$row['visitdate'].'</td>
  88.                     <td><a href="viewapp.php?id='.($row['visitid']).'" class="btn btn-warning pull-right btn-xs">View</a></td> 
  89.                     <td><a href="delapp.php?id='.($row['visitid']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
  90.                     </tr>';    
  91.             $no++;
  92.         }?>
  93.     </tbody>
  94.   </table>
  95.   <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  96.   <a href="" class="btn btn-info pull-left">Refesh</a>
  97.   <a href="../" class="btn btn-info pull-right">Admin Area</a>
  98. </div>
  99. <div class="bottompadding"></div>
  100. <?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/footer.php"); ?>
  101. </body>
  102. </html>
mysqlphp

1条答案

按热度按时间
cuxqih21

cuxqih211#

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

  1. $sql .= " WHERE visitdate BETWEEN '".$appdatefrom."' and '".$appdateto."'";
  2. $sql .= " AND (patient.fName LIKE '%".$search_term."%'";
  3. $sql .= " OR patient.sName LIKE '%".$search_term."%'";
  4. $sql .= " OR doctor.sName LIKE '%".$search_term."%'";
  5. $sql .= " OR conditions.con_name LIKE '%".$search_term."%'";
  6. $sql .= " OR drugs.medication LIKE '%".$search_term."%')";
  7. $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