CODEWITHSUNDEEP
phpmysql
Commandrea
Commandrea

Reputation: 561

Track/Verify/Update a MySQL result by user

I have a database of a schedule where volunteers can check their shifts. I'm going to email them a link to the page where they enter their email addresses into an HTML form to access this information.

Is there a way I can track which emails are queried so I can resend the schedule link to those who haven't accessed the database?

If necessary, I could add an additional 'confirmed' check box to the results and have that update the database. I like that idea, but I'm not sure how to implement (or the terminology for what that action would be).

Edit: Here's the code I'm using to implement. However I'm not getting results in the confirmed column. 

                $db = new mysqli("host", "user", "pass", "db");

    $stmt = $db->prepare('UPDATE volConfirm SET confirmed = TRUE WHERE email = ?');
    $stmt->bind_param('s', $_POST['email']);
    $stmt->execute();

    $stmt = $db->prepare('SELECT * from volConfirm WHERE email = ?');
    $stmt->bind_param('s', $_POST['email']);
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
    // construct your output here using $row to access database record
    echo "<h2>" . $row['agreeName'] . "</h2>";
    echo "<p> You have been assigned as a volunteer for:" . $row['position'] . "</p>";
    echo "<p>Your shift times are scheduled for:" . $row['shift_times'] . "</p>";
    echo "<p>Your shift has been confirmed:" . $row['confirmed'] . "</p>";
    }

Upvotes: 1

Views: 147

Answers (1)

eggyal
eggyal

Reputation: 126025

You need to do something along the lines of:

  1. Add a new column to your volunteers table

    ALTER TABLE Volunteers ADD COLUMN Confirmed BOOLEAN NOT NULL DEFAULT FALSE;

  2. Have the PHP in the submission page update that column:

    UPDATE Volunteers SET Confirmed = TRUE WHERE Email = '[email protected]';

    In your code snippet:

    $db = new mysqli("dbhostname", "username", "password", "dbschema");

$stmt = $db->prepare('UPDATE volConfirm SET confirmed = TRUE WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();

$stmt = $db->prepare('SELECT * from volConfirm WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$result = $stmt->get_result();

while ($row = $result->fetch_array(MYSQLI_NUM)) {
  // construct your output here using $row to access database record
}

  3. At some point in the future, get a list of all users who have not yet accessed the page:

    SELECT Email FROM Volunteers WHERE Confirmed = FALSE;

Upvotes: 1

Related Questions