Query Sort

Hey David,

You need your JOINS before your WHEREs

SELECT c.client_name, c.client_id
FROM tbl_client AS c
JOIN tbl_therapist_client AS t ON t.client_id = c.client_id
JOIN tbl_session AS s ON ???? = s.????
WHERE t.therapist_id = 1
AND c.client_status = "Active"
ORDER BY c.client_name ASC;

Select allOpen in new window

Hi Chris..getting this back.  Doing something wrong!

This seems to work..

SELECT tbl_client.client_name, tbl_client.client_id
        FROM tbl_client
        JOIN tbl_therapist_client
        ON tbl_therapist_client.client_id=tbl_client.client_id
         JOIN tbl_session
        ON tbl_session.client_id=tbl_client.client_id
         WHERE tbl_therapist_client.therapist_id = $therapist_id
        AND tbl_client.client_status = "Active"
         AND tbl_session.session_status = "Invoiced"
        ORDER BY tbl_client.client_name ASC;

Select allOpen in new window

We've aliased the session table as s, so you need to use that:

JOIN tbl_session AS s ON t.therapist_id = s.therapist_id
WHERE t.therapist_id = 1
AND c.client_status = "Active"
AND s.session_status = "Invoiced"

Only one problem if a client has more than one invoice it shows the client twice...or more. Definitely not filtering properly.  Show All, shows all invoices regardless of status.  Also shows all invoices per client regardless of status.



https://arise.plus/THERAPIST/view-invoice.php

Okay much better.  However; It's showing all of Kevin's invoices.  It should show only the Invoiced ones.

$sql = <<< EOT
          SELECT tbl_client.client_name, tbl_client.client_id
        FROM tbl_client
        JOIN tbl_therapist_client
        ON tbl_therapist_client.client_id=tbl_client.client_id
         JOIN tbl_session
        ON tbl_session.client_id=tbl_client.client_id
         WHERE tbl_therapist_client.therapist_id = $therapist_id
           AND tbl_session.session_status = "Invoiced"
         GROUP BY tbl_client.client_id
        ORDER BY tbl_client.client_name ASC;
EOT;

Select allOpen in new window

Hmmm. That query wouldn't produce those results.  The query is only selecting 2 columns but the results are showing much more than that.  Are you sure you're running the correct query

I believe so...Page attached.view-unpaid.php  could it becoming from this?  invoice_unpaid.php?

<?php
require_once('includes/common.php');
require_once('includes/secure.php');
require_once('includes/pdo_connection.php');

// Extract POST and bug out if not present
$data = isset($_POST['cancel']) ? $_POST['cancel'] : false;
if (!$data) die();

$qmarks = str_pad("?", (count($data) * 2)- 1, ',?');
$query = <<< QUERY
UPDATE tbl_session SET `session_status` = 'Paid' WHERE session_id IN ({$qmarks})
QUERY;
$stmt = $db->prepare($query);
$msg = '';
if ($stmt->execute(array_values($data))) {
    $total = $stmt->rowCount();
    if ($total) {
        $msg = "{$total} Paid";
    } else {
        $msg = "No Sessions Paid";
    }
} else {
    $msg = "There was an error processing this request.";
}

die(json_encode(['msg' => $msg]));

Select allOpen in new window

OK David,

In the file you've sent over, there are 2 queries. The one we've been discussing here is only ever used to populate the <select> dropdown:

$sql = <<< EOT
    SELECT tbl_client.client_name, tbl_client.client_id
    FROM tbl_client
    JOIN tbl_therapist_client ON tbl_therapist_client.client_id=tbl_client.client_id
    JOIN tbl_session ON tbl_session.client_id=tbl_client.client_id
    WHERE tbl_therapist_client.therapist_id = $therapist_id
    AND tbl_session.session_status = "Invoiced"
    GROUP BY tbl_client.client_id
    ORDER BY tbl_client.client_name ASC;
EOT;

$results = $db->query($sql);
echo "<select name='myClients'>";
echo "<option value='0'>Show All</option>";
while ($row = $results->fetch(PDO::FETCH_OBJ)) {
    printf("<option value='%s'>%s</option>", $row->client_id, $row->client_name);
}
echo "</select>";

Select allOpen in new window

Right at the very top of your script, you have a different query that is used to populate the table:

$sql = <<<EOT
    SELECT tbl_session.session_id AS ID, 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_client.client_name as CLIENT, tbl_session.session_status AS STATUS
    FROM tbl_session
    INNER JOIN tbl_client ON tbl_client.client_id=tbl_session.client_id
    WHERE tbl_session.therapist_id = :therapist_id
    AND tbl_session.session_status = "Invoiced"
    ORDER BY session_date DESC
EOT;

$stmt = $db->prepare($sql);
$stmt->execute([':therapist_id' => $therapist_id]);
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);

Select allOpen in new window

That loads the results into the $rows variable and then you output it with the following:

foreach($rows as $row): ?>
    <tr>
        <td><?= $row->ID; ?></td>
        <td><input type="checkbox" name="cancel[]" value="<?= $row->ID?>"></td>
        <td><?= $row->DATE; ?></td>
        <td><?= $row->TIME; ?></td>
        <td><?= $row->TYPE; ?></td>
        <td><?= $row->CLIENT; ?></td>
        <td><?= $row->STATUS; ?></td>
    </tr>
<?php endforeach; ?>

Select allOpen in new window

Ah - now it makes more sense - when I was looking at the code from your previous post, it didn't seem right - you were never passing in the Client ID to the query, so I knew something was off.

Hi Chris.  This is the problem.  processCreate.php  It should only show "Booked" for the Client.  I tried to "Fix It" but I have made a mess of it! LOL.

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once('includes/common.php');
require_once('includes/secure.php');
require_once('includes/practice.php');
require_once('includes/pdo_connection.php');

$clientWhere = null;
$params = ['therapist' => $therapist->id];

if ($clientId = (int)($_GET['client'] ?? 0)) {
    $params['client'] = $clientId;
    $clientWhere = "AND tbl_client.client_id = :client";
}

$sql = <<<EOT
    SELECT tbl_session.session_id AS ID, 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_client.client_name as CLIENT, tbl_session.session_status AS STATUS
    FROM tbl_session
    INNER JOIN tbl_client
    ON tbl_client.client_id=tbl_session.client_id
    WHERE tbl_session.therapist_id = :therapist $clientWhere
   AND tbl_session.session_status ='Booked'
    ORDER BY session_date ASC
EOT;
$stmt = $db->prepare($sql);
$stmt->execute([':therapist_id' => $therapist_id]);
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);

while ($row = $results->fetch(PDO::FETCH_OBJ)) {
    printf("<option value='%s'>%s</option>", $row->client_id, $row->client_name);
        <table id="myTable" class="topics" style="width:100%">
    <colgroup>
          <col style="width: 10%;">
      <col style="width: 25%;">
      <col style="width: 10%;">
      <col style="width: 15%;">
      <col style="width: 25%;">
      <col style="width: 15%;">
    </colgroup>
    <thead>
        <tr>
            <th>&nbsp;</th>
         <th>SELECT</th>
         <th>DATE</th>
            <th>TIME</th>
         <th>TYPE</th>
         <th>CLIENT</th>
         <th>STATUS</th>
        </tr>
    </thead>
    <tbody id="results">
   <?php 

   foreach($rows as $row): 
   ?>
        <tr>
            <td><?= $row->ID; ?></td>
            <td><input type="checkbox" name="cancel[]" value="<?= $row->ID?>"></td>
         <td><?= $row->DATE; ?></td>
         <td><?= $row->TIME; ?></td>
         <td><?= $row->TYPE; ?></td>
         <td><?= $row->CLIENT; ?></td>
         <td><?= $row->STATUS; ?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>
        );
}

Select allOpen in new window

Hi Chris. VICTORY!  It was a number of file that needed adjusted.  I had two js scripts. the seond was overwriting the first.  Then I adjusted the processCreat.php file. This is what I ended up with.

<?php

require_once('includes/common.php');
require_once('includes/secure.php');
require_once('includes/practice.php');
require_once('includes/pdo_connection.php');

$clientWhere = null;
$params = ['therapist' => $therapist->id];

if ($clientId = (int)($_GET['client'] ?? 0)) {
    $params['client'] = $clientId;
    $clientWhere = "AND tbl_client.client_id = :client";
}

$sql = <<<EOT
    SELECT tbl_session.session_id AS ID, 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_client.client_name as CLIENT, tbl_session.session_status AS STATUS
    FROM tbl_session
    INNER JOIN tbl_client
    ON tbl_client.client_id=tbl_session.client_id
    WHERE tbl_session.therapist_id = :therapist $clientWhere
   AND tbl_session.session_status = "Booked"
    ORDER BY session_date ASC;
EOT;

$clients = $db->prepare($sql);
$clients->execute($params);

while ($client = $clients->fetch(PDO::FETCH_OBJ)) {
    printf('<tr><td>%s</td><td><input type="checkbox"></td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
        $client->ID,
        $client->DATE,
        $client->TIME,
        $client->TYPE,
        $client->CLIENT,
        $client->STATUS
        );
}

Select allOpen in new window

Thank you for your help!