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...
HTMLPHPAJAX

Avatar of undefined
Last Comment
Scott Fell

8/22/2022 - Mon
David H.H.Lee

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

Open in new window

David Schure

ASKER
Hi David. Thank you. Where would I put this? On what page?
David H.H.Lee

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,
 .....
   
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Scott Fell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.