function get_patients_for_day()
{
// Output JSON
global $db;
$date = clean($_POST['date']);
//the date to fetch
$date = sql_friendly_date($date);
// The start_time is a SQL timestamp. Convert to format ex: 07:30PM or 9:30PM (12 hr, AM/PM).
$query = "SELECT first_name, last_name, patient_id, slot_id, DATE_FORMAT(start_time, '%h:%i%p') AS start_time ";
$query .= "FROM Schedule JOIN Patients ON ";
$query .= "Schedule.scheduled_patient_id = Patients.patient_id WHERE slot_date = '{$date}'";
$result = $db->query($query);
$all = $result->fetch_all(MYSQLI_ASSOC);
// fetch as associative array, not numeric
die(json_encode($all));
}
function get_patient_id_create_if_new($first_name, $last_name, $birthday)
{
global $db;
$id = get_patient_id($first_name, $last_name, $birthday);
if (empty($id)) {
// ID was null, so the patient is new, so let's insert them
// We are preferring prepared statements, especially for INSERTion
$query = "INSERT INTO Patients";
$query .= "(first_name, last_name, date_of_birth, created, last_modified)";
$query .= "VALUES (?, ?, ?, now(), now())";
$birthday = sql_friendly_date($birthday);
if (!($stmt = $db->prepare($query))) {
echo "Prepare failed: (" . $db->errno . ") " . $db->error;
}
if (!$stmt->bind_param("sss", $first_name, $last_name, $birthday)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if ($stmt->execute()) {
//header("Location: ../../"); This will direct to the next form to be added
} else {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
$id = $stmt->insert_id;
// The new patient_id of what we INSERTed
}
return $id;
}
<thead>
<tr>
<th style="text-align:center">Time of Action</th>
<th style="text-align:center">Type of Action</th>
<th style="text-align:center">User Name</th>
</tr>
</thead>
<!-- Body of table 1st <td> is time, 2nd is action, 3rd is user name -->
<tbody>
<?php
if (isset($_POST['log-date']) && $_POST['log-date'] !== "") {
//if user has looked up a date
//get log-date and convert it to a sql-friendly date
$log_date = clean($_POST['log-date']);
$log_date = sql_friendly_date($log_date);
global $db;
//DATE() function is used in sql query to ignore the time part of 'created' field.
$result = $db->query("SELECT log_user_id, action, created FROM Log WHERE DATE(created) = '{$log_date}'");
while ($row = $result->fetch_assoc()) {
$name_result = get_user_name($row['log_user_id']);
//function we had in users.php returns result, not name itself
$name_row = $name_result->fetch_assoc();
//so we must fetch the array
echo '<tr>';
echo '<td style="text-align:center">' . $row['created'] . '</td>';
echo '<td style="text-align:center">' . $row['action'] . '</td>';
echo '<td style="text-align:center">' . $name_row['name'] . '</td>';
echo '</tr>';
}
}
$token = $_GET['token'];
$patient_id = get_patient_id_from_token($token);
$addnew = true;
//assume this is a new patient unless we find records
if ($result = $db->query("SELECT * FROM PatientDemographics WHERE patient_id = '{$patient_id}'")) {
$row = $result->fetch_assoc();
if ($result->num_rows !== 0) {
$addnew = false;
}
}
// Get info from post
$first_name = clean_up($_POST['first-name']);
$last_name = clean_up($_POST['last-name']);
$preferred_name = clean_up($_POST['preferred-name']);
$date_entered = sql_friendly_date(clean_up($_POST['today-date']));
$date_of_birth = sql_friendly_date(clean_up($_POST['birth-date']));
$gender = clean_up($_POST['gender-options']);
$daily_work_performed = clean_up($_POST['daily-work']);
$completed_education = clean_up($_POST['education-options']);
$has_history_tobacco = clean_up($_POST['tobacco-options']);
$alcohol_usage = clean_up($_POST['alcohol-options']);
$ethnicity = clean_up($_POST['ethnicity-options']);
//INSERT or UPDATE DATABASE
if ($addnew) {
//create insert query
// Note we insert patient_id on the end of this INSERT query so that
// we can use the same bind_param() method for both INSERT INTO and UPDATE queries
$sql = "INSERT INTO PatientDemographics (preferred_name, date_entered, date_of_birth, gender, daily_work_performed, completed_education, has_history_tobacco, alcohol_usage, ethnicity, patient_id, created, last_modified) VALUES (?,?,?,?,?,?,?,?,?,?,now(), now())";
} else {
//create update query
$sql = "UPDATE PatientDemographics SET preferred_name=?, date_entered=?, date_of_birth=?, gender=?, daily_work_performed=?, completed_education=?, has_history_tobacco=?, alcohol_usage=?, ethnicity=?, last_modified=now() WHERE patient_id = ? LIMIT 1";
$patient_id = get_patient_id_from_token($token);
$slot_id = get_slot_id_from_token($token);
/*------------------------------------------------------------------------------
* Step 1: Collect our POST variables into PHP variables for readability.
* We will perform data validation with helper functions.
* Pay careful attention to variables which have bounds
* As well as date parsing. SQL date convention is: YYYY-MM-DD
* As well as Yes/No or True/False responses. SQL convention dictates we
* store such information as CHAR(1): 'Y' or 'N'.
*/
// Convert Yes or No to Y or N
$has_pain_now = convertYesNo($_POST['has_pain']);
$month = clean($_POST['month']);
$day = clean($_POST['day']);
$year = clean($_POST['year']);
$date = sql_friendly_date($month . '/' . $day . '/' . $year);
// Give date like 02/28/2015 and convert to SQL friendly date
if (!isReasonableYear($year)) {
die("Please enter a reasonable year, {$year} is not reasonable.");
}
$activity_onset_pain = clean($_POST['activity']);
$pain_right_now = clean($_POST['pain_right_now']);
$pain_at_worst = clean($_POST['pain_at_worst']);
$pain_at_best = clean($_POST['pain_at_best']);
$pain_on_average = clean($_POST['pain_on_average']);
$makes_pain_worse = clean($_POST['makes_pain_worse']);
$makes_pain_better = clean($_POST['makes_pain_better']);
$coords_array = $_POST['coords'];
//array of coordinates, e.g $coords_arr[0] = 'X Y'
/*------------------------------------------------------------------------------