Mysql - PHP - Creating a trigger or a transaction? - Need clarification

Question

Please read edit 2 first thanks!

I have been working on a page called "inoffice.php" this page shows students waiting to be seen and currently being seen page view bellow :

Now pretty much I have just used the same table from above to get the second table however I added a left join so that the counselor working with the student (in this case Dawne) is shown and also what time the student was accepted by the counselor.

That part of the project was so so in difficulty but none the less I was able to get by. Now the hard part for me is this (this is also my question) :

1) How can I make it so that the Being Seen table (the second table under waiting) is empty UNLESS a counselor (or a staff member) clicks on the button "Start Session" (not a php session but a actual real life meeting between the student and the counselor)

2) How can I make it so that the first table (waiting) loses an entry once the counselor (staff member) clicks on the "Start Session" button.

My data base schema goes as follows :

1. Users - Holds staff credentials
2. Waiting - contains ID(PK), anum (which is a student Id Number), first, last, why, comments, signintime
3. counselors - This holds ID (PK and FK from waiting to use in a join), counselorname, counselor start time
4. comments - ID (PK and FK from waiting), Comments (around varchar 50 for counselor comments)
5. finish - ID(PK and FK from waiting), and timestamp for when the counselor is finished with the student

So hopefully with the info I have provided, some one can elaborate on which how / if I should use a trigger or a transaction to do accomplish this. Or if it is a PHP "thing" - for lack of better words.

Thank you, Rage

Edit 2 : Deleted the old not working code, updating the post with new code and removing outdated screen shot! Including updated one! :

<php
require('core/init.php');

    if(!isset($_SESSION['LoggedIn'])){
        die("You must <a href='index.php'><bold>login</bold></a> before you can see that page!");
    }

//Begin the select statements
try 
    {
$query = $dbh->prepare("SELECT * FROM waiting WHERE counselorname IS NULL ");
$query->bindParam(':null', $null);
$query->execute();
$result = $query->fetchall();
    }
        catch (PDOException $e) {
        error_log($e->getMessage());
        die($e->getMessage());
        }


    echo "Return to <a href='login_success.php'><bold>Home Page</bold></a>";

echo "<br />";
echo "Waiting";

echo 
    "<table border='2'>
    <tr>
    <th>ID</th>
    <th>A Number</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Why</th>
    <th>Comments</th>
    <th>Signintime</th>
    <th>Staff Member</th>
    <th>Click if ready!</th>
    </tr>"
    ;

    foreach($result as $row)
    {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td><a href=Student.php?student=" . $row['anum'] . ">" .$row['anum'] . " </a></td>";
  echo "<td>" . $row['first'] . "</td>";
  echo "<td>" . $row['last'] . "</td>";
  echo "<td>" . $row['why'] . "</td>";  
  echo "<td>" . $row['comments'] . "</td>";
  echo "<td>" . $row['signintime'] . "</td>";
  echo "
        <td>    <form action='counselor.php?id=" . $row['id'] . "' method='post' target='_new'>
                    <select name='namedrop'>
                        <option value=''>Counselor Name</option> 
                        <option value='Admin-John'>Admin - John</option>
                        <option value='Admin-Christine'>Admin - Christine</option>
                        <option value='Admin-Dawne'>Admin - Dawne</option>
                        <option value='Counselor-Cherie'>Counselor - Cherie</option>
                        <option value='Counselor-Tootie'>Counselor - Tootie</option>
                        <option value='Counselor-Debbie'>Counselor - Debbi</option>
                        <option value='FrontDesk-Delores'>Front Desk - Delores</option>
                        <option value='FrontDesk-Kiana'>Front Desk - Kiana</option>
                    </select>
            </td>

            <td> <input type='submit' name='submit' value='Start Session'></td>
            </form> </td>";
}

  echo "</tr>";
  echo "</table>";

echo "<br />";
echo "<br />";


try 
    {
    $query2 = $dbh->prepare("SELECT * FROM waiting WHERE counselorname is NOT NULL");
    $query2->execute();
    $result2 = $query2->fetchall();
    }
        catch (PDOException $e) {
        error_log($e->getMessage());
        die($e->getMessage());
        }


echo "Return to <a href='login_success.php'><bold>Home Page</bold></a>";

echo "<br />";
echo "Being seen";

echo 
    "<table border='2'>
    <tr>
    <th>ID</th>
    <th>A Number</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Why</th>
    <th>Comments</th>
    <th>Signintime</th>
    <th>Staff Member</th>
    <th>Counselor Start Time</th>
    </tr>"
    ;

    foreach($result2 as $row2)
    {
  echo "<tr>";
  echo "<td>" . $row2['id'] . "</td>";
  echo "<td><a href=Student.php?student=" . $row2['anum'] . ">" .$row2['anum'] . " </a></td>";
  echo "<td>" . $row2['first'] . "</td>";
  echo "<td>" . $row2['last'] . "</td>";
  echo "<td>" . $row2['why'] . "</td>"; 
  echo "<td>" . $row2['comments'] . "</td>";
  echo "<td>" . $row2['signintime'] . "</td>";
  echo "<td>" . $row2['counselorname'] . "</td>";
  echo "<td>" . $row2['counselor_time'] . "</td>";
}


?> 

Answer 1

There should likely be one SQL table (with any normalized dependencies) for both the rendered HTML tables.

That is, make the displayed HTML tables represent different views of the same information.

Now, the posted schema mostly allows this, but there is a problem that keeps it from being realized:

The Counselors table contains information (start time) that is not a functional dependency - that is, it is not related to "being a counselor"! This causes two issues:

1. A counselor may only have one start time and;
2. It's not possible to represent the start time for all events.

The fix is to move the start time column from the Counselors table and put it in the Schedules table as a start time (like a finish time) is dependent upon a particular scheduling.

---

1) How can I make it so that the Being Seen table (the second table under waiting) is empty UNLESS a counselor (or a staff member) clicks on the button "Start Session" (not a php session but a actual real life meeting between the student and the counselor)

Query (view of information):

select * from Schedules
where startTime not null   -- only show started events

2) How can I make it so that the first table (waiting) loses an entry once the counselor (staff member) clicks on the "Start Session" button.

Query (view of information):

select * from Schedules
where startTime is null    -- it does not 'lose' item, but it is not shown

Otherwise, if it is insisted to use separate tables you'll have to do book-keeping: either insert/delete as appropriate manually each time or set up a trigger to do the same. This adds additional work and is likely not needed.