I am trying to refactor my code to be PDO ready, but something is wrong and cannot understand what: The following code using PDO prepared INSERT query runs apparently Ok, I cannot get any error message (other than some Warning on "undefined indexes", but no data are inserted in the demographics table. I have also checked MySQL and I have the correct privileges to write or delete everything. Any help would be greatly appreciated. I have a print_r($_POST) that is working fine, but cannot get any value from the stmt->execute(); command (I have tried print_r($value = stmt->execute()); with no errors but no messages. Here is the code:
// First --> Let us then include info regarding the connection to the database, PHP functions, and header section and page title
require('../../includes/config.inc.php');
require('../../includes/db_connection.php');
require('../../includes/functions.php');
require('../elements/layouts/header.php');
// Second --> Let's 'Check whether user has the rights to see current page or not
if(!isLoggedIn()) //"isLoggedIn" is a pre-specified function in functions.php file
{
header('Location: ../index.php');
die();
}
/*
Setup some variables/arrays: First we are creating a blank array called action and then setting an array value of result.
Result is going to hold a value of either success or error. Next we create another blank array called text.
This is going to hold any text we want to show the user during the signup.
*/
$action = array();
$action['result'] = null;
$text = array();
// Check if the form has been submitted:
if (isset($_POST['enroll'])) {
// On the other hand, if there are no errors, we can go ahead and enroll the patient:
if($action['result'] != 'error'){
// let's start a try/catch loop and submit the query via mysqli prepared statement
try {
//let's define the variables involved, starting with the fields coming from the Demographics form
$pid = null; // this can be anything (MySQL will overwrite this value in any case)
$addmod_ts = date('Y-m-d H:i:s');
$address = $_POST['address'];
$age = $_POST['age'];
$censor_d = $_POST['censor_d'];
$city = $_POST['city'];
$clinic = $_POST['clinic'];
$death = $_POST['death'];
$dis_cat_main = $_POST['dis_cat_main'];
$dis_cat_spec = $_POST['dis_cat_spec'];
$disease_1 = $_POST['disease_1'];
$disease_2 = $_POST['disease_2'];
$disease_3 = $_POST['disease_3'];
$disease_4 = $_POST['disease_4'];
$dob = $_POST['dob'];
$email_1 = $_POST['email_1'];
$email_2 = $_POST['email_2'];
$firstname = $_POST['firstname'];
$fup_months = $_POST['fup_months'];
$fup_years = $_POST['fup_years'];
$institution = $_POST['institution'];
$lastname = $_POST['lastname'];
$locked = $_POST['locked'];
$notes = $_POST['notes'];
$phone_1 = $_POST['phone_1'];
$phone_2 = $_POST['phone_2'];
$phone_3 = $_POST['phone_3'];
$physician = $_POST['physician'];
$province = $_POST['province'];
$pt_department = $_POST['pt_department'];
$pt_location = $_POST['pt_location'];
$recruit_ts = date('Y-m-d H:i:s');
$region = $_POST['region'];
$research = $_POST['research'];
$saved = $_POST['saved'];
$sex = $_POST['sex'];
$specdis_1a = $_POST['specdis_1a'];
$specdis_1b = $_POST['specdis_1b'];
$ssn = $_POST['ssn'];
$study = $_POST['study'];
$zip = $_POST['zip'];
$month = $_POST['month'];
$day = $_POST['day'];
$year = $_POST['year'];
//Let us start basic validation: make sure everything required has been inserted
if (empty($lastname)){
$action['result'] = 'error'; array_push($text,'Please insert patient last name');
}
if (empty($firstname)){
$action['result'] = 'error'; array_push($text,'Please insert patient first name ');
}
if (!is_numeric ($sex)) {
$action['result'] = 'error'; array_push($text,'Please insert patient gender'); // SEX is a Number so must be treated accordingly (if empty does not work here)
}
if (empty($disease_1)){
$action['result'] = 'error'; array_push($text,'Please insert at least the first medical issue'); // Disease_1 is a Number so must be treated accordingly (if empty does not work here)
}
if (empty($address)){
$action['result'] = 'error'; array_push($text,'Please insert patient Address');
}
if (empty($city)){
$action['result'] = 'error'; array_push($text,'Please insert city name');
}
if (empty ($phone_1)){
$action['result'] = 'error'; array_push($text,'Please insert at least one valid phone number ');
}
if (empty($email_1)){
$action['result'] = 'error'; array_push($text,'Please insert at least one valid e-mail address');
}
// then let us define and validate DOB and put the date in SQL format
// Validate the month.
if (is_numeric ($month)) {
$dob = $month . '-';
} else {
$action['result'] = 'error'; array_push($text,'Please insert a valid Month for patient birth date');
}
// Validate the day.
if (is_numeric ($day)) {
$dob .= $day . '-';
} else {
$action['result'] = 'error'; array_push($text,'Please insert a valid Day for patient birth date');
}
// Validate the year.
if (is_numeric ($year)) {
$dob = $year . '-' . $month . '-' . $day; // Set Birthdate in SQL format
} else {
$action['result'] = 'error'; array_push($text,'Please insert a valid Year for patient birth date');
}
// Finally, we can go ahead with the SQL INSERT query
$sql = 'INSERT INTO `demographics` ( PID,
ADDMOD_TS,
ADDRESS,
AGE,
CENSOR_D,
CITY,
CLINIC,
DEATH,
DIS_CAT_MAIN,
DIS_CAT_SPEC,
DISEASE_1,
DISEASE_2,
DISEASE_3,
DISEASE_4,
DOB,
EMAIL_1,
EMAIL_2,
FIRSTNAME,
FUP_MONTHS,
FUP_YEARS,
INSTITUTION,
LASTNAME,
LOCKED,
NOTES,
PHONE_1,
PHONE_2,
PHONE_3,
PHYSICIAN,
PROVINCE,
PT_DEPARTMENT,
PT_LOCATION,
RECRUIT_TS,
REGION,
RESEARCH,
SAVED,
SEX,
SPECDIS_1A,
SPECDIS_1B,
SSN,
STUDY,
ZIP
)
VALUES ( :pid,
NOW(),
:address,
:age,
:censor_d,
:city,
:clinic,
:death,
:dis_cat_main,
:dis_cat_spec,
:$disease_1,
:disease_2,
:disease_3,
:disease_4,
:dob,
:email_1,
:email_2,
:firstname,
:fup_months,
:fup_years,
:institution,
:lastname,
:locked,
:notes,
:phone_1,
:phone_2,
:phone_3,
:physician,
:province,
:pt_department,
:pt_location,
NOW(),
:region,
:research,
:saved,
:sex,
:specdis_1a,
:specdis_1b,
:ssn,
:study,
:zip
)';
$stmt = $db->prepare($sql);
$stmt->bindParam(':pid' , $pid, PDO::PARAM_INT);
$stmt->bindParam(':addmod_ts' , $addmod_ts, PDO::PARAM_STR);
$stmt->bindParam(':address' , $address, PDO::PARAM_STR);
$stmt->bindParam(':age' , $age, PDO::PARAM_INT);
$stmt->bindParam(':censor_d' , $censor_d, PDO::PARAM_STR);
$stmt->bindParam(':city' , $city, PDO::PARAM_STR);
$stmt->bindParam(':clinic' , $clinic, PDO::PARAM_STR);
$stmt->bindParam(':death' , $death, PDO::PARAM_INT);
$stmt->bindParam(':dis_cat_main' , $dis_cat_main, PDO::PARAM_STR);
$stmt->bindParam(':dis_cat_spec' , $dis_cat_spec, PDO::PARAM_STR);
$stmt->bindParam(':disease_1' , $disease_1, PDO::PARAM_STR);
$stmt->bindParam(':disease_2' , $disease_2, PDO::PARAM_STR);
$stmt->bindParam(':disease_3' , $disease_3, PDO::PARAM_STR);
$stmt->bindParam(':disease_4' , $disease_4, PDO::PARAM_STR);
$stmt->bindParam(':dob' , $dob, PDO::PARAM_STR);
$stmt->bindParam(':email_1' , $email_1, PDO::PARAM_STR);
$stmt->bindParam(':email_2' , $email_2, PDO::PARAM_STR);
$stmt->bindParam(':firstname' , $firstname, PDO::PARAM_STR);
$stmt->bindParam(':fup_months' , $fup_months, PDO::PARAM_INT);
$stmt->bindParam(':fup_years' , $fup_years, PDO::PARAM_INT);
$stmt->bindParam(':institution' , $institution, PDO::PARAM_STR);
$stmt->bindParam(':lastname' , $lastname, PDO::PARAM_STR);
$stmt->bindParam(':locked' , $locked, PDO::PARAM_INT);
$stmt->bindParam(':notes' , $notes, PDO::PARAM_STR);
$stmt->bindParam(':phone_1' , $phone_1, PDO::PARAM_STR);
$stmt->bindParam(':phone_2' , $phone_2, PDO::PARAM_STR);
$stmt->bindParam(':phone_3' , $phone_3, PDO::PARAM_STR);
$stmt->bindParam(':physician' , $physician, PDO::PARAM_STR);
$stmt->bindParam(':province' , $province, PDO::PARAM_STR);
$stmt->bindParam(':pt_department' , $pt_department, PDO::PARAM_STR);
$stmt->bindParam(':pt_location' , $pt_location, PDO::PARAM_STR);
$stmt->bindParam(':recruit_ts' , $recruit_ts, PDO::PARAM_STR);
$stmt->bindParam(':region' , $region, PDO::PARAM_STR);
$stmt->bindParam(':research' , $research, PDO::PARAM_INT);
$stmt->bindParam(':saved' , $saved, PDO::PARAM_INT);
$stmt->bindParam(':sex' , $sex, PDO::PARAM_INT);
$stmt->bindParam(':specdis_1a' , $specdis_1a, PDO::PARAM_STR);
$stmt->bindParam(':specdis_1b' , $specdis_1b, PDO::PARAM_STR);
$stmt->bindParam(':ssn' , $ssn, PDO::PARAM_STR);
$stmt->bindParam(':study' , $study, PDO::PARAM_STR);
$stmt->bindParam(':zip' , $zip, PDO::PARAM_STR);
$stmt->execute();
$errorInfo = $stmt->errorInfo();
if (isset($errorInfo[2])) {
print_r($error = $errorInfo[2]);
}
} catch (Exception $e) {
$error = $e->getMessage();
}
// Tell the user we have done successfully
$action['result'] = 'success';
array_push($text,'Patient is on Kardia now');
}
//A quick check of our action result value and we can continue on with the signup. If our result is error we will
//skip over all the above code and output the errors to our user so they can make the necessary changes.
// The last piece of this code we are putting the values of your text array into our action array.
$action['text'] = $text;
}
?>
<?= show_errors($action); //This calls the function show_errors, to format validation appropriately ?>
