I have tried looking for a solution, i am very new to PHP and although i've looked, there does not seem to be a solution to help with the data formatting.
I am not too concerned about SQL injections and security as of yet with this as it is for an assignment to hand in, just some advice on how get the results i need.
I have a page where i am wanting to search for a keyword in a database, but also optional i would like to add a time frame search.
for example i can search for name, doctor, condition and medication by using the text search, but i would also optionally (as i would like to be able to search without using the date restriction) like to use the search appdatefrom and appdateto to narrow down appointment dates.
I am getting there error
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
Currently in the database my visitdate are formatted as 2014-04-20 01:23:43
i am finding it hard to do, due to the time TIMESTAMP format.
Database looks as follows. i cannot get a good ERD with XAMP for some reason? so i have created one.
code for the page is as follows.
<?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> After looking at answers given, adding parenthasis in the code stopped it from working, it is currently working like this but without the date search?
this is how the site looks currently. when typing in my surname it brings up my results, but if i want to search between two dates it just does not do anything. 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> 
visitdatein your schema