Link to home
Start Free TrialLog in
Avatar of David Schure
David Schure

asked on

Getting a Selectbox to Filter a Report

I am trying to get this selectbox to filter a report. I have this so far.
Selectbox.
<form action="">
  <label for="cars">Select a Month</label>
 
  <select name="monthly" id="monthly">
     <option value="All">Show All</option>
    <option value="Jan">January</option>
    <option value="Feb">February</option>
    <option value="Mar">March</option>
    <option value="Apr">April</option>
    <option value="May">May</option>
    <option value="Jun">June</option>
    <option value="Jul">July</option>
    <option value="Aug">August</option>
    <option value="Sept">September</option>
    <option value="Oct">October</option>
    <option value="Nov">November</option>
    <option value="Dec">December</option>
  </select>
   <input type="submit" value="Submit"> 
</form>

Open in new window

The Script
<script>
      $('select[name=monthly]').on('change', function() {

    let selectedValue = $(this).val()
    $.ajax({
        url : 'processmonth.php',
        data : { client : selectedValue },
    }).done(function(response) {
        console.log(response);
      $('#results').html(response);
    })

})
      </script>

Open in new window

The processmonth.php page
<?php
require_once('includes/common_pdo.php');
require_once('includes/secure.php');

$therapistWhere = null;
$params = [':client_id' => $client->id];

if ($therapistId = (int)($_GET['therapist'] ?? 0)) {
    $params[':therapist_id'] = $therapistId;
    $therapistWhere = "AND tbl_therapist.therapist_id = :therapist_id";
}

$sql = <<<EOT
    SELECT DATE_FORMAT(tbl_session.session_date,"%W, %M, %d, %Y") AS DATE,
   TIME_FORMAT(CONCAT(tbl_session.time_slot_id, ":00:00"), "%h:%i %p") AS TIME,
   tbl_session.session_type AS TYPE, tbl_therapist.therapist_name as THERAPIST, tbl_session.session_status AS STATUS
    FROM tbl_session
    INNER JOIN tbl_therapist
    ON tbl_therapist.therapist_id=tbl_session.therapist_id
   WHERE tbl_session.client_id=3
AND DATE_FORMAT(tbl_session.session_date, "%b") = '$yourValue'
    ORDER BY session_date DESC;
EOT;
$result = $db->query($sql);

$stmt = $db->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
foreach($rows as $therapist) {
    echo <<< HTML
        <tr>
            <td><?= $row->DATE; ?></td>
         <td><?= $row->TIME; ?></td>
         <td><?= $row->TYPE; ?></td>
         <td><?= $row->THERAPIST; ?></td>
         <td><?= $row->STATUS; ?></td>
        </tr>
HTML;
}

Open in new window

And the HTML
 <div id="results">
      <form>
   <table class="topics" style="width:100%">
    <colgroup>
          <!--col style="width: 10%;"-->
      <!--col style="width: 0%;"-->
      <col style="width: 35%;">
      <col style="width: 15%;">
      <col style="width: 15%;">
      <col style="width: 20%;">
      <col style="width: 15%;">
    </colgroup>
    <thead>
        <tr>
            <!--th>SELECT</th-->
         <!--th></th-->
         <th>DATE</th>
            <th>TIME</th>
         <th>TYPE</th>
         <th>THERAPIST</th>
         <th>STATUS</th>
        </tr>
    </thead>
    <tbody>
    <?php while ($row = $result->fetch(PDO::FETCH_OBJ)): ?>
        <tr>
            <!--td><input type="checkbox"></td-->
         <!--td><!?= $row->ID; ?></td-->
            <td><?= $row->DATE; ?></td>
         <td><?= $row->TIME; ?></td>
         <td><?= $row->TYPE; ?></td>
         <td><?= $row->THERAPIST; ?></td>
         <td><?= $row->STATUS; ?></td>
        </tr>
    <?php endwhile; ?>
    </tbody>
</table>
</form>
</div>

Open in new window

Missing something. As it is not filtering...
Avatar of David H.H.Lee
David H.H.Lee
Flag of Malaysia image

I didn't see the declared variable for
$yourValue.
Eg:
$yourValue=$_GET['client'];

Open in new window

Avatar of David Schure
David Schure

ASKER

Hi David. Thank you. Where would I put this? On what page?
In processmonth.php page before you use the declared $yourValue variable in $sql statement.

$yourValue=$_GET['client'];

$sql = <<<EOT
    SELECT DATE_FORMAT(tbl_session.session_date,"%W, %M, %d, %Y") AS DATE,
 .....
   
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial