Hi,
I have a query which retrieves the most recent end_date of a contract. It works when i enter it into a cmd window but not inside my actual script as when i do a var_dump it shows as NULL. I don't understand why it works in one but not the other.
Recently i have received an error message that said the end_date field was undefined (i have highlighted this line in bold along with the query that is returning null).
My code is as follows:
<?php
include'db_config.inc';
session_start();
unset($_SESSION['OVR_ERR_MSG']);
$_SESSION['OVR_ERR_MSG'] = '';
$dbh = new PDO("mysql:host=127.0.0.1;dbname=$db", $user, $password);
if(isset($_SESSION['SESS_USER'])) {
$username = $_SESSION['SESS_USER'];
}
else {
header("location: login.php");
}
$stmt = $dbh->prepare("SELECT MAX(end_date)
FROM Contract
WHERE cust_id = '$username'");
$stmt->execute();
$latestcontract = $stmt->fetch();
$contractenddate = $latestcontract['end_date'];
$_SESSION['end_date'] = $latestcontract['end_date'];
$todaysdate = date("Y-m-d");
$_SESSION['today'] = $todaysdate;
if($contractenddate > $todaysdate){
$_SESSION['OVR_ERR_MSG'] = 'You already have a contract. You can sign up to a new one when this one ends.';
header("location: member_contract.php");
}
else {
try{
$duration = filter_var($_POST['duration'], FILTER_SANITIZE_NUMBER_INT);
$currDay = date("d");
$currMonth = date("m");
$curYear = date("Y");
$startdate1 = new DateTime($curYear . $currMonth . $currDay);
$startdate = $startdate1->format('Y-m-d');
$enddate1 = $startdate1->add(new DateInterval("P".$duration."M"));
$enddate = $enddate1->format('Y-m-d');
$total_cost = 0.00;
$stmt = $dbh->prepare("INSERT INTO `Contract`
(`cust_id`, `start_date`, `duration`, `end_date`, `total_cost`)
VALUES ('$username', NOW(), '$duration', '$enddate', '$total_cost'
)");
$stmt->execute();
header("location: member_profile.php");
}
catch (Exception $e) {
$_SESSION['OVR_ERR_MSG'] = $e;
session_write_close();
header("location: member_contract.php");
}
}
?>
And my database code is below to show there is an end_date field:
CREATE TABLE IF NOT EXISTS Contract (
contract_no INT(6) ZEROFILL NOT NULL AUTO_INCREMENT,
cust_id VARCHAR(30) NOT NULL,
start_date DATE NOT NULL,
duration INT(2) NOT NULL,
end_date DATE NOT NULL,
total_cost DOUBLE(5,2),
CONSTRAINT CONTRACT_PK PRIMARY KEY(contract_no),
CONSTRAINT CONTRACT_FK FOREIGN KEY (cust_id) REFERENCES Member (username)
);
I'm not having problems with the rest of the code; just the parts i've stated.
Thanks.